supersunday modeling question

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

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

Any advice welcome.

Platform: oracle 10.2.0.2.0 on sixty-four (64) bit x86 solaris.

Thanks.

REgards,
Chas. Received on Mon Feb 05 2007 - 04:51:37 CET