Check for no more than two entries of each value in a column?

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 Received on Fri Apr 17 2009 - 12:40:56 CDT