SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Threaded View

  1. #1
    SitePoint Addict tempyyyst's Avatar
    Join Date
    Nov 2001
    Location
    Kent, UK
    Posts
    286
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Data structure problem

    Hi all

    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
    Code:
    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]
    Which nicely gets the names of awards and winning films for a particular year.

    So to incorporate the winning people ids, I change it (unconvincingly) to
    Code:
    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]
    Ermm, but hang on, there might be more than one person. How does that work?

    Am I trying to do too much with one query?

    Any suggestions for a better data structure?
    Last edited by tempyyyst; Aug 3, 2003 at 12:17.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •