Re: Table design decision

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 Received on Mon Jan 16 2006 - 21:29:52 CST