Re: Table Schema

On Mar 24, 4:57 pm, Eric Kaplan <tobycraf..._at_yahoo.com> wrote:
> 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
>

Apparently you do not understand the difference between relational database design and just slapping a few tables together that seem related. Following your current plan you will basically have a file set of flat files stored in the database. The IDs are not the issue. It is the mixture of data in your design. What if a cameraman is on two programs? and is cameraman a role? What if Prince appears on the show? That link you gave certainly does not provide much information. Is that data what will be your input? Or are you sup[posed to be able to generate a report that looks like that??

Take a little time and google Database Normalization. Look at some of the college links that show up. You will find that designing a database is a lot more than creating a few tables and columns.

Ed
The key the whole key and nothing but the key, so help me Codd. Received on Tue Mar 25 2008 - 08:03:29 CDT