NYCPHP Meetup

[James Wetterau]

Greg Wilson says:
> how about:
> 
> a table of people
>  - id, firstname, lastname, etc
> 
> a table of movies
>  - id, title, etc
> 
> a table of roletypes
>  - director, producer, actor, cinematographer, etc
> 
> and a table relating people to movies based on roles
>  - movie_id, people_id, roletypeid
....

I agree, and I would like to mention that this can be deduced from the
first principal of normalization, if you look at it right.  Each of
those people is in some sense a "repeating" or "duplicative" column,
in that you could have arbitrarily many people listed, depending on
how many roles you wanted to track.

One purpose of the rules of normalization is that by following them
you can expand the information you track without altering your
database schema or tables.  If you wanted to add "screenwriter" or
"best boy" or "gaffer" or "sound editor" later, under the original
schema you'd have to alter the tables.  If you simply create a role
table and a mapping or association table that relates movies, people
and roles, as Greg Wilson proposed, then you can add new data later
without altering the schema.  

The other point about using id's comes straight from second normal
bform, and it helps make changes or corrections to data simple,
improves storage efficiency, and eliminates redundancy (in the bad
sense of redundancy).

I'd recommend learning about 1st, 2nd and 3rd normal forms and
thinking about them a bit.  If you intend to design a large database,
it may make sense to learn about 4th and 5th normal forms, and for
cases where denormalization also makes sense.  In this case, the
database is simple enough that after taking care to get to 2nd
normal forms (they are cumulative, so if you're in 2nd NF, you're also
in 1st) you have no further issues of concern, as far as I can tell.

Here's a pretty simple web reference I found in a quick search:

http://www.gslis.utexas.edu/~l384k11w/normover.html


> On Fri, 2004-01-09 at 10:23, Keith J Richardson wrote:
> > I would personally have a table of people. Then have a table of say peoplet
ypes, which would be producer, director, acter, etc
> > 
> > then in the people list, they have a peopletypeid, which is the unique id o
f the peopletype that is in the other list
> > 
> > -----Original Message-----
> > From: talk-bounces at lists.nyphp.org
> > [mailto:talk-bounces at lists.nyphp.org]On Behalf Of Matthew Zimmerman
> > Sent: Friday, January 09, 2004 10:24 AM
> > To: NYPHP Talk
> > Subject: [nycphp-talk] Database Relation Question
> > 
> > 
> > Is it considered good database design to have two tables related to 
> > each other through more than one field.
> > 
> > What I means is I have two tables. One is called "movies" and one is 
> > called "people".
> > 
> > Movies has some fields like "director, producer, cinematographer".  
> > People is just a list of people "first_name, last_name, etc."
> > 
> > Is it ok for movies to relate to people via "director" and "producer" 
> > and "cinematographer" or is it better to create a "producer" table to 
> > related to producer, "director" table to relate to director, etc.
> > 
> > I have a feeling I am doing it the right way just having a "people" 
> > table but I wasn't sure if this would cause any query problems down the 
> > line.
> > 
> > Thanks for your help. I am not sure if I used all of the proper 
> > terminology here so I can rephrase the question if needed.
> > 
> > Matt
> > 
> > _______________________________________________
> > talk mailing list
> > talk at lists.nyphp.org
> > http://lists.nyphp.org/mailman/listinfo/talk
> > _______________________________________________
> > talk mailing list
> > talk at lists.nyphp.org
> > http://lists.nyphp.org/mailman/listinfo/talk
> > 
> 
> _______________________________________________
> talk mailing list
> talk at lists.nyphp.org
> http://lists.nyphp.org/mailman/listinfo/talk