Table Schema

**Erland Sommarskog** · Mar 24 '08, 09:45 AM

Re: Table Schema

Carmen Sei (fatwallet951@y ahoo.com) writes:

I have a group of TV listing data need to map into database tables.
>
Data looks like following:

http://www.oniva.com/upload/1356/crew.jpg

>
I want to create a table for productionCrew of each TV program
>
the data is like -
crew -programID -member
-member
-member ... etc
-programID -member
-member
-member ... etc
-programID -member
-member
-member ... etc
... etc
>
above are data from productionCrew of all TV program, for each
programID we have a list of members.
>
Should I merge all member into a big string?

Absolutely not.

Or should I use 2 tables to store the Crew data?
>
If i use 2 tables, how the fields / column will look like?

I would guess that you need at least three tables, since I suspect that
a person can be member of more than one crew.

That gives:

CREATE TABLE programs (programid int NOT NULL PRIMARY KEY,
programname nvarchar(200) NOT NULL,
... )

CREATE TABLE people (personid int NOT NULL PRIMARY KEY,
firstname nvarchar(50) NOT NULL,
lastname nvarchar(50) NOT NULL,
....)

CREATE TABLE productioncrew (programid int NOT NULL REFERENCES programs,
personid int NOT NULL REFERENCES people,
role datatype? NOT NULL,
PRIMARY KEY (programid, personid))

As for the role I don't have knowledge enough to know how this should
be handled. It seems reasonable that a person can participates in
different roles in different crews, so it should not be an attribute of
the person. (Then again, a person has a certain skill set, which may want
to record.) A person can also have several roles in the same crew,
which would call for even more relational tables, but depending on the
need to query that information it may shoot over the target to have
such tables.

The important thing is that in a relational database, a basic rules is:
no repeating groups. Repeating groups are difficult to query and
maintain.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

**Carmen Sei** · Mar 24 '08, 09:05 PM

Re: Table Schema

I think i only need 2 table is enough

CREATE TABLE programs
(
programid int NOT NULL PRIMARY KEY,
programname nvarchar(200) NOT NULL,
... )

CREATE TABLE productioncrew
(
programid int NOT NULL REFERENCES programs,
firstname nvarchar(200) NOT NULL,
lastname nvarchar(200) NOT NULL,
role nvarchar(200) NOT NULL,
PRIMARY KEY (programid, firstname, lastname, role)
)

since my DB is only 6 tables like Schedules, TVStations,
ChannelLineup, Programs, ProductionCrews and Genres

it's a very small DB and I dont' need all those ID things it's easy to
do query with above

>
CREATE TABLE programs (programid int NOT NULL PRIMARY KEY,
programname nvarchar(200) NOT NULL,
... )
>
CREATE TABLE people (personid int NOT NULL PRIMARY KEY,
firstname nvarchar(50) NOT NULL,
lastname nvarchar(50) NOT NULL,
....)
>
CREATE TABLE productioncrew (programid int NOT NULL REFERENCES programs,
personid int NOT NULL REFERENCES people,
role datatype? NOT NULL,
PRIMARY KEY (programid, personid))
>
>As for the role I don't have knowledge enough to know how this should
>be handled. It seems reasonable that a person can participates in
>different roles in different crews, so it should not be an attribute of
>the person. (Then again, a person has a certain skill set, which may want
>to record.) A person can also have several roles in the same crew,
>which would call for even more relational tables, but depending on the
>need to query that information it may shoot over the target to have
>such tables.
>
>The important thing is that in a relational database, a basic rules is:
>no repeating groups. Repeating groups are difficult to query and
>maintain.

Table Schema

Table Schema

Comment

Comment