Search Postgresql Archives

Re: database design best pratice help

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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




--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux