Ed Prochak wrote:
> 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.
I agree.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Fri Jan 27 2006 - 13:05:12 CST
