SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  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 13:17.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    your opening paragraph contains a splendid description, a gold mine of information to model your data properly
    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.
    you have three main entities: award, film, person

    there will be at least one three-way relationship table, due to "an award [won] by ... a person... for a particular film"
    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.
    let's leave that and come back to it later -- design for one set of awards, get that straightened out, then it will be easier to modify the design for multiple sets of awards (as this involves primary/foreign keys)

    AWARD
    id primary key
    name

    FILM
    id primary key
    name

    PERSON
    id primary key
    name

    AWARDWON
    a_id foreign key references AWARD
    f_id foreign key references FILM
    p_id foreign key references PERSON


    okay, let's see if we can cover all our requirements:

    "an award can be [won] by a film..."

    a_id f_id p_id
    27 103 --

    " ... a person, ..."

    a_id f_id p_id
    28 -- 1201

    " ... more than one person, ..."

    a_id f_id p_id
    23 -- 1193
    23 -- 1198
    23 -- 1205

    " ... a person for a particular film ..."

    a_id f_id p_id
    25 117 1212

    " ... or more than one person for a particular film."

    a_id f_id p_id
    20 098 1187
    20 098 1188
    20 098 1189


    okay, as you can see, sometimes one of the ids is null

    those are foreign keys, and it is perfectly okay for a foreign key to be null, it means that the relationship is optional, i.e. that particular row does not relate to a parent (e.g. for an award won by a person, the film is a non-existent, non-sensical attribute)

    because two of the keys can be null, the combo of the three keys cannot be declared a primary key, but that's okay because you only need a primary key if the table acts as parent table in a parent-child relationship, and this one doesn't

    however it is imperative that you have an index on the keys, for join performance, and multiple indexes may be needed in a large database

    now let's make this AWARDWON a child in another relationship, as a child to the CEREMONY, which is for a specific year

    thus each instance like "25 117 1212" has to relate to which ceremony/year it belongs to

    this means add a foreign key to AWARDWON which links to the id of the CEREMONY row for the year

    if it were me doing it (see note), i would use the year number (e.g. 1995) as the primary key of the CEREMONY table

    note: some people think that a pk should never change, as this has ripple effects on foreign keys that refer to it (the "on update cascade" so-called problem), but me, i don't think they would in this example -- change, i mean

    CEREMONY
    pk primary key
    name

    AWARDWON
    c_pk not null foreign key references CEREMONY
    a_id not null foreign key references AWARD
    f_id null foreign key references FILM
    p_id null foreign key references PERSON

    AWARDWON has no primary key but does have indexes


    have fun


    rudy
    http://r937.com/

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

    Thanks for the clear and useful reply. I had been on the right line a few days ago, but had overcomplicated things I think.

    The next part of my query is on the PHP forum!


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
  •