Re: Table design decision

All,

Thanks for your replies so far. There is a reason why the approach proposed by Terry can't be implemented, and I alluded to it in my OP, but I guess I didn't explain it very well !

Let's say the existing poorly-designed table is table X. Table X has 50 foreign keys, with each one pointing to a different table, and each one is flagged for cascading deletes so whenever a parent row is deleted from any of tables 1-50, any child rows in table X get deleted automatically by Oracle. This behaviour has to be retained in the new model, and the structure of those 50 tables can't be messed with.

Without the use of triggers (which isn't encouraged for this app because it aims to be database-independent), I would imagine the only way of getting the *automatic* clean-up of child records would be through the use of cascading deletes on foreign keys... and I'm going to need 50 of them as there are 50 parent tables. I couldn't have 50 of them constraining the value of the same column in the same table 50 different ways (X.FK_column must equal t1.primary_column OR t2.primary_column etc - not t1.primary_column AND t2.primary_column etc).

> btw what on Earth requires an arc with 50 mutally exclusive parents?

Nothing - It's just bad design! 50 different kinds of objects in the application are each allowed an unlimited number of user-defined properties as name-value pairs, and the properties are all stored in one table (table X), regardless of the kind of object they belong to. Table X's structure is:

UID_column (unique ID for this row)
property_name
property_value
table1_UID_column (null allowed)
...
table50_UID_column (null allowed)
FK constraint on table1_UID_column linked to table1 ...
FK constraint on table50_UID_column linked to table50

Regards,
Tarby Received on Thu Jan 12 2006 - 04:17:10 CST