Results 1 to 3 of 3
Thread: Data structure problem
Aug 1, 2003, 22:20 #1
- Join Date
- Nov 2001
- Kent, UK
- 0 Post(s)
- 0 Thread(s)
Data structure problem
I'm building a site at the moment for an award ceremony, so a fundamental part of that is who won what award. To complicate things, it's for a film award so an award can be won by a film, a person, more than one person, a person for a particular film or more than one person for a particular film.
Being a sensible developer I of course normalized my data so I have a film table and a people table. Without complicating things too much I also have a table called award_ceremony which is made up of id, award_id and ceremony_id fields, so that each award in each year has a unique id.
I then created a winner table consisting of id, award_ceremony_id, film_id.....
and this is where I get stuck
Assuming that each award can be won by more than one person, do I need to have a winner_people linking table with a winner_id and a people_id? I just seem to be ending up with too many tables!
So, assuming that I do, I then get stuck on the query to do the select. This is what I have so far
SELECT award, title FROM winner LEFT JOIN film ON film_id=film.id LEFT JOIN award_ceremony ON award_ceremony_id=award_ceremony.id LEFT JOIN award ON award_id=award.id WHERE ceremony_id=[ceremony id here]
So to incorporate the winning people ids, I change it (unconvincingly) to
SELECT award, title, people.id FROM winner LEFT JOIN film ON film_id=film.id LEFT JOIN winner_people ON winner_id=winner.id LEFT JOIN award_ceremony ON award_ceremony_id=award_ceremony.id LEFT JOIN award ON award_id=award.id WHERE ceremony_id=[ceremony id here]
Am I trying to do too much with one query?
Any suggestions for a better data structure?