Table schema

Thread view:

Eric Kaplan - 24 Mar 2008 04:35 GMT

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?

Reply to this Message

Lew - 24 Mar 2008 13:08 GMT

Please do not multi-post. How is someone reading one group supposed to be
aware of the conversation in another, except by accident? Multi-posting is
very bad practice. If you must reach multiple groups, rarely very helpful,
you can cross-post instead.

> I have a group of TV listing data need to map into database tables.
>
[quoted text clipped - 19 lines]
>
> Should I merge all member into a big string?

I don't know what you mean by that question, but the answer is no.

> Or should I use 2 tables to store the Crew data?

If Crew data were the only things you saved, one might do, but you're also
storing member data and program data, right?

You need at least three tables.

Study up on database normalization and third-normal form. This will clear up
the matter.

> If i use 2 tables, how the fields / column will look like?

What are the attributes of the real-world things your database will model?

Signature

Lew

Reply to this Message

Roedy Green - 24 Mar 2008 15:45 GMT

On Sun, 23 Mar 2008 20:34:17 -0800, Eric Kaplan
<tobycraftse@yahoo.com> wrote, quoted or indirectly quoted someone who
said :

>crew -> programID -> member

your main table would have three fields, plus extra info about
members.

crewid : programid: memberid:

you would also have a table with info about crews indexed by crewid
and crew name

You would also have a table of info about programs indexed by program
name and program id.

If you had just a small database, you might dispense with crewids and
programids and just use the names as keys.

Signature

Roedy Green Canadian Mind Products
The Java Glossary
http://mindprod.com

Reply to this Message

Eric Kaplan - 24 Mar 2008 21:59 GMT

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

>>crew -> programID -> member
>
[quoted text clipped - 11 lines]
>If you had just a small database, you might dispense with crewids and
>programids and just use the names as keys.

Reply to this Message

Table schema

Free Magazines