Re: Database Design Best Practice help

From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Mon, 28 Jan 2013 07:53:04 -0700
Message-ID: <510690D0.1010808_at_gmail.com>

Sounds like they might be the traditional 'lookup tables': states, provinces, statuses, colors, valid_sizes, etc. Not uncommon in a real application to have several hundred of these as a way of both enforcing the constraint and providing a dynamic list of values to be presented to the user.

When you consider that commercial applications these days easily run into the 1,000s of tables and have 10s-100s or thousands of columns, and need the flexibility of customer-defined LOVs, these hundreds of code tables are pretty much normal.

The Value Attribute Table (one official name) that attempts to merge the code tables into one is a common variant that nearly every mature architect I know has tried, and abandoned after reality, maintenance and politics set in.

/Hans

On 28/01/2013 6:57 AM, John Hurley wrote:
> I guess my first question is do you know "why you have about one hundred tables
> like this ..." in the current database?
>
> Do they represent different entities in some fashion that correspond to some
> part of the real world?
>
> Do these tables ONLY have the two columns code and description or is there all
> sorts of other columns in ( some/all ) of them?
>
> Do you have any understanding of the history of how and why the current set of
> tables were ( pick one ) created/designed/arrived in your database?
>
>
>
> ----- Original Message ----
> From: Jose Soares <jose.soares_at_sferacarta.com>
> To: oracle-l_at_freelists.org
> Sent: Mon, January 28, 2013 4:03:16 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:
>
> 1. only one table in the db instead of 100
> 2. 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jan 28 2013 - 15:53:04 CET