Re: supersunday modeling question

On Feb 5, 6:46 am, "John K. Hinsdale" <h..._at_alma.com> wrote:
> 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

All,

thanks for your help. John, you are correct that the rock/paper/ scissors tables each have a bunch of attributes that are specific to them. You've also accurately anticipated suggestions from the java proggers -- who often favor something akin to the E-A-V approach, the wisedom of which has been refuted here and elsewhere.

At this point, I'm probably going to go w/ the three (3) fields, and a check contraint.

The "thing" being modeled is really a rule definition which can constrain actions based on a number of criteria (subscriberid, productid, etc.).

REgards,
Chas. Received on Mon Feb 05 2007 - 10:34:43 CST