Hi,

I need to create an app where any given employee may nominate up to
TWO other employees per year for an award. This number could change.

Now I could create a table like this:

PK, U1 nominator (varchar2(12))
PK, U1 year (int)
U1 nomination1_id (int)
U1 nomination2_id (int)

Then another table:
PK nomination_id (int)
nominee (varchar2(12))
nomination_data ....

However, this would force me to update any Views or Code every time
the number of allowed nominations changes.

I'd much rather do this:

PK nomination_id (int)
year (int)
nominator (varchar2(12))
nominee (varchar2(12))
nomination_data ...

And have some kind of Check Constraint to only allow two nominees per
nominator per year. But I don't know how to do that.

Thoughts?

~ Jamie

livefreeordie schreef:
You could create a trigger, checking the number of records for one
nominator on insert of a new one, and let it fail if two already exist.

OR: create an extra column for nominee number, with a constraint that it
can be 1 or 2, and let the nominator fill that column (1 for first
nominee, 2 for second) and create a unique constraint on nominator,
nominee number, nomination year. This way you only have to change the
check constraint on nominee number when more nominees are allowed.

OR: other options....

Shakespeare

On Apr 17, 12:40*pm, livefreeordie <jpittm... (AT) gmail (DOT) com> wrote:
Answered in comp.databases.oracle.server. Posting to more than one
newsgroup doesn't always get faster response.



David Fitzjarrell

On Apr 17, 2:28*pm, Shakespeare <what... (AT) xs4all (DOT) nl> wrote:
Ah that's a good solution. Thanks.

On 18.04.2009 14:06, livefreeordie wrote:
If I am not mistaken this one will fall into the "mutating table" trap.

This makes additional business logic necessary, especially determining
the appropriate number before the insert.

Here's another option: create another table with PK (nominator, year)
and additional column count (default = 0, check constraint which limits
values to 0,1 or 2). Create an insert trigger on the first table which
inserts or updates the counter table. Create update and delete triggers
on the first table which update counter table.

This version is also concurrency safe because you have exclusive access
on the counter records per (nominator, year).

Kind regards

robert