[thelist] plea for assist on db, pls...

Paul Cowan evolt at funkwit.com
Mon Jul 14 19:20:44 CDT 2003


jsWalter wrote:
> Does each show get it's own table to store its episodes in?
>
> Or do I create one massive EPISODES table and each item is then keyed to
> the show ID and/or abbreviation?

The latter is almost certainly what you're after. The standard
normali[zs]ation of the data you're talking about might look something
like this. I won't explain it in detail, but it should give you an
idea:

Genre
------
GenreID (PK)
Name
...


Network
-------
NetworkID (PK)
Name
...


Series
-------
SeriesID (PK)
Name
GenreID
NetworkID
...


Episode
-------
EpisodeID	(if needed)
SeriesID
Title
Number
Airdate
...


Person
-------
PersonID	PK
FirstName
Surname
Birthdate
...


SeriesPerson (maybe 'EpisodePerson' depending on how you want to track)
------------
SeriesID	\
PersonID	| PK (possibly EpisodeID, not SeriesID, see above)
RoleID		/


Role
------------
RoleID (PK)
RoleName	('Actor', 'Director', 'Guest', whatever)


Hopefully the foreign keys are obvious -- everything that's a "SomethingID"
that doesn't share the same name as the table itself, if you get what I
mean.

Have a play around with this, create the tables, etc -- it might give you
a bit of a starting point, anyway.

Anything that's unclear, or any further questions, just yell out.
Rudy will tell you what I've done wrong. Hehe.

Cheers,

Paul


More information about the thelist mailing list