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

From: Palooka <nobody_at_nowhere.com>
Date: Fri, 17 Apr 2009 20:12:15 +0100
Message-ID: <jw4Gl.43164$cX1.21696_at_newsfe28.ams2>

Michael Austin wrote:

> livefreeordie wrote:

>> 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

> 
> 
> one method would be to add the data using a procedure that would limit 
> the number.
> 
>     input: nominator
>     input: nominee
>     input: other nominee data....
>     output: ret_status
> 
> select count(*) into variablea from table where nominator=:nominator
> if variablea >=2 then return <some bad status>
> else
> insert the data
> return good status.
> 
>     

Except have the max permissible number of nominees in a parameter table. Then if the business rules change, the code doesn't have to - only the data.

Palooka Received on Fri Apr 17 2009 - 14:12:15 CDT