Re: Database Design Best Practice help

From: Hans Forbrich <>
Date: Mon, 28 Jan 2013 11:08:34 -0700
Message-ID: <>

On 28/01/2013 10:05 AM, David Fitzjarrell wrote:
> Any change to a schema invariably involves changes to the application; you currently have an app that accesses these tables and consolidating them into a single 'unified' entity would require recoding that application to use this new object rather than the dozens/hundreds you currently have. Other people have brought forth the notion that these may be set up in a primary key/foreign key relationship and if that is also true you would also need to rework the schema by changing all of those foreign key constraints (if possible) to use this new table. Of course the new primary key for this 'one-size-fits-all' table would include table_name, a column not included in any of the other tables where foreign key constraints against these lookup tables are in place. This places yet another burden on you to add table_name to every affected table so proper foreign key constraints can be enabled.
> This sounds like a Herculean task that I would not care to undertake.
> David Fitzjarrell

(thinking out loud here) David,

Your comment raised an interesting question: with 11g's Virtual Column capability, is it feasible to define a foreign key constraint where the child contains a virtual column set as a constant with a default to a literal that identifies the 'table', as well as the data which is the code in that 'table'. Use a view to mimic the code-related table and set the primary or reference key to the compound key in the underlying value attribute table.

My biggest single objection to VATs always has been the inability to create the required foreign key without using up space, and some form of trigger. That and the implications in confusing the optimizer.

I think I'm going to have to test this ...


Received on Mon Jan 28 2013 - 19:08:34 CET

Original text of this message