Re: supersunday modeling question

On Feb 5, 8:14 am, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:

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

Mark,
what foothillbiker is probably not mentioning tho, is that the FK's for ROCKID, SCISSORID, and PAPERID probably point to more detailed tables (with data specific to rocks, scissors, and papers, repsectively) each of which have _their_ own constraints on what data must (or need not) be present. Collapsing the polymorphic entity "a-scissors-or-paper-or-rock" into one row in the master ROWSHAMBOW still begs the question of how and where to store this detail data.

It's also unclear as to whether changing the design which requires exactly one of the three IDs present is an option. If not, then the tricker or CHECK constraint is probably the way to go.

JH Received on Mon Feb 05 2007 - 08:21:45 CST