Re: Table design decision

Martin Doherty wrote:
> DA Morgan wrote:
> [snip]
>
> > I respectfully disagree. At least based on what you have written here
> > so far. Consider the following lookup table design:
> >
> > ID NUMBER(10),
> > OBJECT_TYPE VARCHAR2(5),
> > OBJECT_VALUE VARCHAR2(30));
> >
> > It can handle an infinite number of objects of an infinite number of
> > types ... all in one object. I'm not saying it would work for you ...
> > but 50 lookup tables ought to drive any report writer that has to deal
> > with this later absolutely crazy.
> >
> > Ah you have already met with the report writers and gotten their
> > requirements and input haven't you?
>
> That would be the neatest solution, and echoes the approach used in
> Oracle E-Business Suite for lookup values, but bumps up against the
> unfortunate requirement of 50 different FK constraints that support a
> cascading delete.
>
> It may be a failure of my imagination but I can't see any other way to
> maintain that behavior without either the monster table of 50 mutex FK
> columns, or 50 separate tables. Adding a 51st object type will result
> in either adding a column to the monster table, or creating a new
> table. Personally I'd probably stick with the monster table to keep my
> logical schema more manageable (49 fewer entities).
>
> Martin

I agree. Presumably you have 50 tables like EMPLOYEES, HATSTANDS, TOASTER_OVENS etc, and then MONSTER_PROPERTIES contains something like:

( employee_id REFERENCES employees ON DELETE CASCADE

, hatstand_id REFERENCES hatstands ON DELETE CASCADE
, toaster_oven_id REFERENCES toaster_ovens ON DELETE CASCADE
, ... another 47 columns ...
, property_name VARCHAR2(30) NOT NULL
, property_value VARCHAR2(100) NOT NULL ... )

versus this ALTERNATIVE_MONSTER_PROPERTIES:

( generic_id NUMBER(10) -- references nothing in particular

, property_type VARCHAR2(30)  -- tells you which table
, property_name VARCHAR2(30) NOT NULL
, property_value VARCHAR2(100) NOT NULL )

or 50 of these:

EMPLOYEE_PROPERTIES

( employee_id REFERENCES employees ON DELETE CASCADE
, property_name VARCHAR2(30) NOT NULL
, property_value VARCHAR2(100) NOT NULL )

HATSTAND_PROPERTIES (hatstand_id ...as above... )

TOASTER_OVEN_PROPERTIES (toaster_oven_id ...as above...)

The ALTERNATIVE_MONSTER_PROPERTIES with the not-quite-FK looks neat on paper but as Tarby says there is no way to cascade-delete without triggers or other custom code, and I can see it being a PITA to query because you always have to include the 'property_type' column, e.g.

WHERE p.generic_id = h.hatstand_id
AND p.property_type = 'HATSTAND';

instead of just

WHERE p.hatstand_id = h.hatstand_id;

which you get with either the original monster table or the 50 monsterettes. Received on Tue Jan 17 2006 - 12:47:14 CST