 | |
database structure for festival contest
comp.databases.mysql comp.databases.mysql
 |
| | Thread Tools | Display Modes |
#11
 
| |||
| |||
|
Re: database structure for festival contest -
03-19-2011
, 04:12 PM
|
On 2011-03-19 00:11, Captain Paralytic wrote: [...] My idea was that, if using check constraints, one would only need a single awards table, the allowance of multiple awards being done from an award properties table. If using separate tables, it can be done without the need for the check constraints. Paul, I'm not sure that I follow. Let's see if I can get this right, (let's skip the temporal aspect for simplicity) create table award_properties ( award_id, max_occurrences ) create table award_winners ( award_id, ssn ) correct so far? |
#12
| |||
| |||
|
|
On Mar 19, 7:17 am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com wrote: On 2011-03-19 00:11, Captain Paralytic wrote: [...] My idea was that, if using check constraints, one would only need a single awards table, the allowance of multiple awards being done from an award properties table. If using separate tables, it can be done without the need for the check constraints. Paul, I'm not sure that I follow. Let's see if I can get this right, (let's skip the temporal aspect for simplicity) create table award_properties ( award_id, max_occurrences ) create table award_winners ( award_id, ssn ) correct so far? spot on |
rules we like to enforce is:
R1: award with max_occurrences = 1 can only be given once, regardless
of ssn.
R2: award with max_occurrences > 1 can be given more than once but
only one time to each ssn.
R3: The same ssn can have multiple awards as long as all of them
have max_occurrences > 1
I don't see how these rules can be implemented given the two tables
above. Can you elaborate on this?
/Lennart
#13
| ||||
| ||||
|
|
On 03/19/2011 11:12 PM, Captain Paralytic wrote: On Mar 19, 7:17 am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com wrote: On 2011-03-19 00:11, Captain Paralytic wrote: [...] My idea was that, if using check constraints, one would only need a single awards table, the allowance of multiple awards being done from an award properties table. If using separate tables, it can be done without the need for the check constraints. Paul, I'm not sure that I follow. Let's see if I can get this right, (let's skip the temporal aspect for simplicity) create table award_properties ( award_id, max_occurrences ) create table award_winners ( award_id, ssn ) correct so far? spot on Sorry about late answer, had a *minor* incident with my computer. The rules we like to enforce is: When you say "The rules _WE_ like to enforce", are you working with |
Quote:
|
R1: award with max_occurrences = 1 can only be given once, regardless * * of ssn. I'm intrigued, what does "ssn" stand for? |
Quote:
|
R2: award with max_occurrences > 1 can be given more than once but * * only one time to each ssn. R3: The same ssn can have multiple awards as long as all of them * * have max_occurrences > 1 I'm not quite clear on these rules. The OP said: "For each entry, it |
then it can't win "Best of Show" also. It can only win one award, or
none.". So this makes me unsure as to what constitutes an "entry". If
an "ssn" is an entry, then according to the OP's description, it can
only have at maximum one award, regardless of how many times that
award can be made. I think R3 should say
R3: An ssn may receive either 1 or zero award(s).
Quote:
|
I don't see how these rules can be implemented given the two tables above. Can you elaborate on this? /Lennart |
will then check the award winners table to see if the condition
implied by the settings in the properties table is violated.
I would tend to expand the tables to include an event id.
If the rules are to be as simple as above, the I would change the
table definitions to:
create table award_properties ( event_id, award_id, unique_award )
where unique_award is a boolean
create table award_winners ( event_id, award_id, ssn )
With a composite primary key on all 3 fields and, assuming that my
version of R3 is correct, a unique key on event_id+ssn.
The composite primary key enforces R2.
The composite unique key enforces (my) R3.
The trigger enforces R1 by performing a JOIN query between the 2
tables ON event_id AND award_id WHERE the event is this event and the
property states that there may only be one of these awards per event.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.