# Data structure problem

• Aug 1, 2003, 21:20
tempyyyst
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?
• Aug 2, 2003, 05:55
r937
your opening paragraph contains a splendid description, a gold mine of information to model your data properly
Quote:

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"
Quote:

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/
• Aug 3, 2003, 23:43
tempyyyst
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!