Re: supersunday modeling question

On Feb 5, 11:34 am, "foothillbiker" <foothillbi..._at_gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -

Bummer on the design team's failure to make proper use of a relational database to store relational

I will agree with John in that I believe that in a relational database you should store relational data in a fully relational design. Objects belong in the middle layer. Trying to store objects in the database just complicates the design, negatively impacts performance, and makes accessing the data using any adhoc tools extremely difficult.

IMHO -- Mark D Powell -- Received on Wed Feb 07 2007 - 10:49:38 CST