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'm not sure I agree with Daniel's solution, but I agree that you haven't considered all the posibilities.

These 50 child tables sound like attributes that are missing from their respective parent tables. Someone went normalization crazy and split these attributes. Consider puting them back in their respective parent tables. Then there is no child table involved in a cascade delete.

I'll bet half or more of those child tables go away.   Ed. Received on Thu Jan 26 2006 - 16:29:02 CST