Re: Database Design Best Practice help
- From: Hans Forbrich <fuzzy.graybeard@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 28 Jan 2013 11:08:34 -0700
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 ...
/Hans
--
//www.freelists.org/webpage/oracle-l
Other related posts:
