By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,364 Members | 1,343 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,364 IT Pros & Developers. It's quick & easy.

Table Schema

P: n/a
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?
Or should I use 2 tables to store the Crew data?

If i use 2 tables, how the fields / column will look like?
Mar 24 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Carmen Sei (fa**********@yahoo.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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 24 '08 #2

P: n/a
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.
Mar 24 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.