Re: supersunday modeling question

foothillbiker wrote:
> All,
>
> Suppose I have a table like this:
>
>
> 15:14:55 system_at_ta64 SQL> desc rowshambow
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> ROWSHAMBOWID NOT NULL NUMBER
> ROCKID NUMBER
> PAPERID NUMBER
> SCISSORSID NUMBER
>
>
> rockid/paperid/scissorsid are all foreign keys to rocktable/
> papertable/
> scissorstable, respectively.
>
>
> The biz requirements are that every record in the table must have
> exactly one (1) of these three fields populated.
>
>
> Questions:
> - any clever ideas on how to enforce the rule about exactly one (1)
> of
> the fields having data? I assume a trigger is the only option, and
> it's not a thing of beauty

A sensible person would use a properly normalized set of base relations and then make this godawful mess a view.

> - should I perhaps punt on this approach and just use a single field
> which can hold either a rockid or a paperid or a scissorsid?
> Downside, of course, is no FK constraint possible (unless I'm missing
> something).
>
> I'm pretty sure this problem has been encountered, solved multiple
> times...

Somehow, I doubt that. Received on Mon Feb 05 2007 - 14:07:36 CET