I have a database for an annual festival with an awards contest. For
each year, there are a number of entries. So I have a table for
FestivalYears, with each year, and Entries, with an id and its
appropriate year:

FestivalYears.festival_year

Entries.entry_id
Entries.festival_year -> FestivalYears.festival_year ( many to 1 )

So also for each year, there are a number of awards. Some awards, like
"Best of Show", can only be given once. Others, like "Honorable
Mention" can be given to any number of entries. So for each year, I
want only one "Best of Show" and a few others, and then multiple
"Honorable Mentions" and "Official Selections". For each entry, it
can have 0 or 1 award. For instance, if it's an honorable mention,
then it can't win "Best of Show" also. It can only win one award, or
none.

I'd like to see if I can enforce this logic in the database structure.
However, the way I can see to do it feels like a hack: if I had two
tables, one for multiple-winner awards, and the other for single-
winner awards, then I could enforce those relational rules.

These tables hold the names of the awards:
SingleWinnerAwards.award_name
SingleWinnerAwards.award_id

MultipleWinnerAwards.award_name
MultipleWinnerAwards.award_id

Then I have these tables which would store the actual winners

FestivalSingleWinners.entry_id -> Entries.entry_id
FestivalSingleWinners.award_id -> SingleWinnerAwards.award_id
... which has a unique key, so entries can only win one award from
single awards.

And then,

FestivalMultipleWinners.entry_id -> Entries.entry_id
FestivalMultipleWinners.award_id -> MultipleWinnerAwards.award_id
... which allows multiple awards of the same name.

However, this structure doesn't prevent entries from winning multiple
awards. There's nothing that would stop any particular entry from
having a related record in both FestivalSingleWinners and
FestivalMultipleWinners.



Can I enforce these rules in the db structure, or will I need to rely
on code to do that?

In article <6a82f860-c8f1-4469-8b58-63f52ca164dd (AT) x8g2000prh (DOT) googlegroups.com>, Steven Lefevre <steven.m.lefevre (AT) gmail (DOT) com> wrote:

This smells like homework...

On Mar 16, 8:11*pm, Steven Lefevre <steven.m.lefe... (AT) gmail (DOT) com> wrote:
You can do either or both.

On Mar 17, 6:05*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

I wasn't clear.

How can I enforce these rules in the database alone? I've yet been
unable to figure it out on my own.

On Mar 18, 4:07*pm, Steven Lefevre <steven.m.lefe... (AT) gmail (DOT) com> wrote:
I'm not trying to be obtuse (for a change) but the answer as to how
you should do this really is "it depends", because there are lots of
ways and the one you choose depends on considering other requirements.

You could set it up such that awards that must be given only once per
year are held in a table with a unique index (possibly the primary
key) of say event+award, whilst those that can be offered many times
are held in a table with a unique index of event+award+auto-increment
or event+award+award-recipient.

Using a slightly different key structure you could combine this with
some application code to have all the awards in a single table but
have an incrementing column only incremented for multiple-allowed
awards.

As has been mentioned in another recent thread, MySQL does not support
CHECK CONSTRAINTS but this would be a great way of implementing this.
In the table that lists the awards you could have a maximum number of
awards per event or a flag to specify unlimited. You would then use a
check constraint to limit them. Lucklily the thread also offered
alternatives using triggers, so you could use one of them.

I'm sure given time I could think of other ways, but as I say, I
usually start my design based on knowledge of what is required and
what I am working with, which enables me to discard some options
quickly.

On Mar 18, 12:45*pm, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

I understand Can you give one example, for the requirements
outlined in the preceding text?

I can do this easily in code; however I posted here to help my
understanding of relational databases. I'd like to see one solution
that's a pure database solution, to help get me to the next level
I'll take it and adapt it to the actual project on my own time.

On 2011-03-16 21:11, Steven Lefevre wrote:

[...]

Just a sketch, assuming there where check constraints (can be emulated
with triggers):

create table awards (
award_id int not null,
award_type char(1) not null,
-- constraint c1_awards check ( award_type in ('S','M'),
constraint pk_awards primary key (award_id),
constraint ak_awards unique (award_id, award_type)
) engine = innodb;

-- populated via triggers
create table award_type_winners (
year int not null,
ssn int not null,
award_type char(1) not null,

-- constraint c1_award_type_winners check ( award_type in ('S','M'),
constraint pk_award_type_winners primary key (year, ssn),
constraint ak_award_type_winners unique (year, ssn, award_type)
) engine = innodb;

create table single_award_winners (
year int not null,
ssn int not null,
award_id int not null,
award_type char(1) default 'S' not null,

-- constraint c1_single_award_winners check (award_type = 'S'),
constraint pk_single_award_winners primary key (year, award_id),
constraint fk1_single_award_winners
foreign key (award_id, award_type)
references awards (award_id, award_type),
constraint fk2_single_award_winners
foreign key (year, ssn, award_type)
references award_type_winners (year, ssn, award_type)
) engine = innodb;

create table multiple_award_winners (
year int not null,
ssn int not null,
award_id int not null,
award_type char(1) default 'M' not null,

-- constraint c1_multiple_award_winners check (award_type = 'M'),
constraint pk_multiple_award_winners
primary key (year, ssn, award_id),
constraint fk1_multiple_award_winners
foreign key (award_id, award_type)
references awards (award_id, award_type),
constraint fk2_multiple_award_winners
foreign key (year, ssn, award_type)
references award_type_winners (year, ssn, award_type)
) engine = innodb;

delimiter @
create trigger populate_type_s_winners
before insert on single_award_winners
for each row begin
if not exists (
select 1 from award_type_winners
where year = new.year
and ssn = new.ssn
) then
insert into award_type_winners
(year, ssn, award_type)
values (new.year, new.ssn, 'S');
end if;
end @
delimiter ;

delimiter @
create trigger populate_type_m_winners
before insert on multiple_award_winners
for each row begin
if not exists (
select 1 from award_type_winners
where year = new.year
and ssn = new.ssn
) then
insert into award_type_winners
(year, ssn, award_type)
values (new.year, new.ssn, 'M');
end if;
end @
delimiter ;


insert into awards (award_id, award_type)
values (1, 'S'), (2, 'S'),(3, 'M'), (4,'M'), (5,'M');

insert into single_award_winners
(year, ssn, award_id, award_type)
values (1, 1, 1, 'S');

-- Should fail, ssn 1 already got award 1
insert into single_award_winners
(year, ssn, award_id, award_type)
values (1, 2, 1, 'S');

ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

insert into single_award_winners
(year, ssn, award_id, award_type)
values (1, 2, 2, 'S');

-- should fail, ssn 1 already got single
insert into single_award_winners
(year, ssn, award_id, award_type)
values (1, 1, 3, 'M');

ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails ("X"."single_award_winners", CONSTRAINT
"fk2_single_award_winners" FOREIGN KEY ("year", "ssn", "award_type")
REFERENCES "award_type_winners" ("year", "ssn", "award_type"))

-- ok, a user can have multiple M
insert into multiple_award_winners
(year, ssn, award_id, award_type)
values (1, 3, 3, 'M'), (1, 3, 4, 'M');

-- ok, several users can have the same M
insert into multiple_award_winners
(year, ssn, award_id, award_type)
values (1, 4, 3, 'M'), (1, 4, 4, 'M');

-- should fail, user 3 already got 3
insert into multiple_award_winners
(year, ssn, award_id, award_type)
values (1, 3, 3, 'M');

ERROR 1062 (23000): Duplicate entry '1-3-3' for key 'PRIMARY'



/Lennart

On 2011-03-18 22:29, Lennart Jonsson wrote:
[...]
Needless to say it is not necessary to specify the award_type


insert into single_award_winners
(year, ssn, award_id)
values (1, 1, 1);

-- Should fail, ssn 1 already got award 1
insert into single_award_winners
(year, ssn, award_id)
values (1, 2, 1);

ERROR 1062 (23000): Duplicate entry '1-1' for key 'PRIMARY'

insert into single_award_winners
(year, ssn, award_id)
values (1, 2, 2);

-- should fail, ssn 1 already got single
insert into single_award_winners
(year, ssn, award_id)
values (1, 1, 3);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key
constraint fails ("X"."single_award_winners", CONSTRAINT
"fk2_single_award_winners" FOREIGN KEY ("year", "ssn", "award_type")
REFERENCES "award_type_winners" ("year", "ssn", "award_type"))

-- ok, a user can have multiple M
insert into multiple_award_winners
(year, ssn, award_id)
values (1, 3, 3), (1, 3, 4);

-- ok, several users can get the same M
insert into multiple_award_winners
(year, ssn, award_id)
values (1, 4, 3), (1, 4, 4);

-- should fail, user 3 already got 3
insert into multiple_award_winners
(year, ssn, award_id)
values (1, 3, 3);

ERROR 1062 (23000): Duplicate entry '1-3-3' for key 'PRIMARY'

On Mar 18, 9:29*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
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.

On 2011-03-19 00:11, Captain Paralytic wrote:
[...]
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?