Re: supersunday modeling question

On Feb 5, 3:12 am, "Martin T." <bilbothebagginsb..._at_freenet.de> wrote:
> 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))
> );

A slighly more maintainable version is

    alter table rsp_table add constraint rps_table_c1 check

    (  decode(rockid, null, 0, 1)
    + decode(scissorsid, null, 0, 1)
    + decode(paperid, null, 0, 1)

• 1 );

If the day comes that a fourth ID (e.g,. THUMBSUPID) is added, you need add only one line to the constraint above, whereas w/ the boolean expression you'll progress from 9 to 16 (i.e, N^2) expressions total.

Looks like a polymorphic thingy is being modelled. See the section "Implementing inheritance in a relational database" in the paper "Mapping objects to relational databases" by Scott Ambler:
http://www-128.ibm.com/developerworks/library/ws-mapping-to-rdb/#h2

The RPS_TABLE seems to use the third mapping technique described above.
In practice, I use the third usually, the second sometimes, and the first
very rarely. Note that it is possible (and often desirable) to apply a
"mix" of the three mapping techniques in a single model.

One thing to be on the lookout for is Java nuts who start out their database design with an "object model," even as their data does not lend itself to an O-O approach, then proceed to do "O-O relational mapping" on it and end up with a mess, when a simple traditional RDMBS oriented model would have sufficed. Mark these designers as "object happy" and a-v-o-i-d.

HTH,
JH Received on Mon Feb 05 2007 - 08:46:02 CST