Re: Database Design Best Practice help
Date: Mon, 28 Jan 2013 10:22:09 +0100
Message-ID: <51064341.5020402_at_mgm-tp.com>
Jose Soares, 28.01.2013 10:03:
> 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
That's not an advantage.
> 2. only one controller to manage the table
Why? You can also have a single controller with many tables.
With a single table the controller needs to know the code. With multiple tables the controller needs to know the table name. I don't see any difference there.
> Is there any negative point that I don't see?
Yes, you can't prevent storing the "wrong" code in a related table as you cannot have a foreign key to it.
Assume you have a list of codes for "order status" and a list of codes for employee status. With two lookup tables (and the proper foreign keys) you can make sure that only a valid employee status is stored in the employee table.
With the single table you can't prevent storing a value for "order status" as the employee table.
Your design is an anti-pattern which has a name: "one true lookup table".
Thomas
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Jan 28 2013 - 10:22:09 CET