Re: Database Design Best Practice help
From: David Fitzjarrell <oratune_at_yahoo.com>
Date: Mon, 28 Jan 2013 09:05:23 -0800 (PST)
Message-ID: <1359392723.51368.YahooMailNeo_at_web121604.mail.ne1.yahoo.com>
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
From: Jose Soares <jose.soares_at_sferacarta.com> To: oracle-l_at_freelists.org
Sent: Monday, January 28, 2013 2:03 AM
Subject: Database Design Best Practice help
Date: Mon, 28 Jan 2013 09:05:23 -0800 (PST)
Message-ID: <1359392723.51368.YahooMailNeo_at_web121604.mail.ne1.yahoo.com>
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
From: Jose Soares <jose.soares_at_sferacarta.com> To: oracle-l_at_freelists.org
Sent: Monday, January 28, 2013 2:03 AM
Subject: Database Design Best Practice help
Hi all,
I have a question about database design best pratice.
In my db I have about one hundred tables like this:
code
description
To avoid to have a so great number of similar tables in the db I wonder if it is a good idea to unify all these tables in one big table like this:
id
code
table_ name
description
The advantages are:
- only one table in the db instead of 100
- only one controller to manage the table
Could this be a way to enhance db performance? Is there any negative point that I don't see?
Thanks for any comments.
j
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 28 2013 - 18:05:23 CET