NYCPHP Meetup

[Keith J Richardson]

having that 3rd table does make sense, if the people are duplicated from the different movies. then you can link them all together, and not have to have duplicates.

roles are linked back to movies, and then the roles then connect to the person, and what they did, since they can change. I would have ended up having to create that 3rd table while developing it, since I would have caught it then. I am more of a develop it and modify it as im going along, rather than sitting down and developing the idea and it on paper firsthand. You never know when you are writing the form, or when testing it, if you really did need that other field, or another way to store data.

I cant say that I know everything, but one thing I do love about working with PHP, is that ever project you perfect your skills more and more, and always learn something. I love this list because I can read what other people would do in the same situation, and then it opens my mind up to see other peoples ways of doing things, and then assimilating the better ideas :P

I will go over the relational database docs, since I never really learned much on relational databases, since the only coding instruction that I have had did use flat files for data storage, which you just had to make sure your records(pascal)/classes would fit the data and point to the proper things nicely. Thanks for the tips you all!


-----Original Message-----
From: talk-bounces at lists.nyphp.org
[mailto:talk-bounces at lists.nyphp.org]On Behalf Of James Wetterau
Sent: Friday, January 09, 2004 11:17 AM
To: NYPHP Talk
Subject: Re: [nycphp-talk] Database Relation Question 




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
_______________________________________________
talk mailing list
talk at lists.nyphp.org
http://lists.nyphp.org/mailman/listinfo/talk