RE: When to have multiple tables

---------------------------------------------------------------------------- ----------------- Disclaimer: The information contained in this email is intended only for the use of the person(s) to whom it is addressed and may be confidential or contain legally privileged information. If you are not the intended recipient you are hereby notified that any perusal, use, distribution, copying or disclosure is strictly prohibited. If you have received this email in error please immediately advise us by return email at postmaster@normandy.com.au and delete the email document without making a copy. ---------------------------------------------------------------------------- ----------------- Personally id go for several tables. Table: Categories Cat_ID Cat_Name Cat_Long_Desc Table: Movies Mov_ID Mov_Name Mov_Long_Desc Table: Individuals (This could also maybe include groups? like Best Costume etc, should have another table if you need to name em individually) Ind_ID Ind_Name Ind_Long_Desc Then you need to have a many-to-many relationship between Catagories and Movies, and Catagories and Individuals, and also between Movies and Individuals. This really needs a few extra tables such as; Table: Movie Nominations Cat_ID Mov_ID Ind_ID (This could be left NULL for Best Movie) Date Status (N Nominee, W Winnner, R Runner up etc) but you may also wich to track who worked on what film irrespective of awards.. so you would need a Movie/People table (ie: Ind_ID and Mov_ID). A good design will allow you to easily extend your database. Like what would happen if you wish to add different types of awards that share similiar catagories? What if someone wants to enter Harrison Ford for 3 movies and seven catagories? Do you have to enter Harrisons name, age, hair colour in 7 times? Try to find a basic book on DB design, its one of those things you can and should learn from a book. mn -----Original Message----- From: Digital Hit Entertainment [mailto:ianevans@friedspamdigitalhit.com] Sent: Thursday, July 13, 2000 2:21 AM To: php-db@lists.php.net Subject: When to have multiple tables I'm still getting used to working with databases and PHP but I have been able to script a news application for my site. I'm now getting ready to work on a database that will track award winners and nominees. I'm still not entirely sure when it's best to use multiple tables and relational concepts and I'm also wondering what method would be the most efficient to code via PHP. The average awards track the following info: YEAR CATEGORY STATUS <--nominee or winner NAMES(S) <--single in the case of actors, directors, multiple for crafts like editing, song TITLE ORDER <--Best Movie comes before Best Actor, so alphabetical doesn't work, so I figure I assign each category with an order of importance from say 1 to 5 and sort alphabetically by category within that e.g. Best Movie=1 Best Director=2 Best Actor/Actress/Supporting=3, the technical awards=4, the special awards (humanitarian,lifetime)=5 Things like categories can change names, disappear, merge, etc over the years, so perhaps category should be in one table along with year and an ID# and the other table would have year, id# and the other fields. My ultimate concept of speeding the presentation would be to have: $Category <ul> <li>$nominee for $title <li>$nominee for $title <li>$nominee for $title </ul> etc. and I could generate the pages for each year just by changing the $year variable at the top. I could test for status and use an IF statement to change winners to <li><b>$nominee for $title</b> Thanks for letting me think out loud. If you have any comments or suggestions I'd appreciate them. Links to good pages on relational database design would also be appreciated. Thanks. -- Ian Evans