Thread: database design best pratice 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

I'll answer with the same things I did on the Oracle list :)

> 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
theproper 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

Jose Soares wrote:
> 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?

If the resulting table grows to be big, that might be
a problem.  Imagine that a query has to do a sequential scan
for a certain query.

Also, are there no foreign key constraints between tables?
If no, the design is questionable.  If yes, how would you
handle them?

I guess it depends on what your application does, but I
wouldn't merge the tables.  One hundred tables are not so
terribly many.

Yours,
Laurenz Albe

Jose Soares <jose.soares@sferacarta.com> wrote:

> 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

> Could this be a way to enhance db performance?

No.  It could easily hurt performance, and will create a mess of
your data.

> Is there any negative point that I don't see?

For starters you can't use foreign keys to ensure data integrity.

I would not only leave the separate tables, but I would create a
separate domain for the code column of each, to be used everywhere
a code is of that nature.

I recommend reading up on concepts for normalizing a relational
database.  If you mix different logical types of data in one
column, you don't even meet the requirements of first normal form,
and reduce the ability to cleanly apply relational concepts to your
data.

-Kevin

> 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

Bad idea.

E.g. how do you want to warrant (integrity!) that records in
the table <table_x> point only to codes that are valid for this table.

To implement this as a constraint and at object level would create an
obfuscated mess.

Besides, that "id" column is a bad idea as well. The primary key of such
a table would have to be composed of "code" and "table_name", of course.

> 2. only one controller to manage the table

With any reasonable database framework that I know of, it's
ridiculously trivial to declare these codes as de-normalises "one to
many"-attributes at the object level. No need to implement *any*
"controllers" at all.

Sincerely,

Wolfgang