Re: supersunday modeling question

On Feb 5, 3:12 am, "Martin T." <bilbothebagginsb..._at_freenet.de> wrote:
> On Feb 5, 12:22 am, "foothillbiker" <foothillbi..._at_gmail.com> 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
>
> What about this:
>
> ALTER TABLE RPS_TABLE ADD (
> CONSTRAINT RPS_TABLE_C1 CHECK ((ROCKID IS NOT NULL AND PAPERID IS
> NULL AND SCISSORSID IS NULL)
> OR (ROCKID IS NULL AND PAPERID IS NOT NULL AND
> SCISSORSID IS NULL)
> OR (ROCKID IS NULL AND PAPERID IS NULL AND SCISSORSID IS NOT
> NULL))
> );
>
> It's not a thing of beauty either, but imho it works.
>
> cheers,
> Martin- Hide quoted text -
>
> - Show quoted text -

All three columns appear to have only one valid value, if so, then it would seem that rock, paper, sissors are all just different values of a single entity. Each is just a different state of the attribute hand position: fist, open palm down, and two fingers extended). If true then a single lookup table with three rows should be used to store the three values and then only one column in the rowshambow table is needed with a FK to the lookup table.

IMHO -- Mark D Powell -- Received on Mon Feb 05 2007 - 07:14:11 CST