Re: Database Design Best Practice help

From: Toon Koppelaars <toon.koppelaars_at_rulegen.com>
Date: Mon, 28 Jan 2013 10:13:36 +0100
Message-ID: <CAA9w=EsLbk+OMRHr6vHO=ZiHrSAGSrZv0d+uZfY__XsaDzsn_g_at_mail.gmail.com>

How is one table, instead of one hundred tables, an advantage? The many tables probably have one big advantage, in that they allow FK-constraints from other tables to the appropriate one-of-the-hundred table to enforce correct codes in the other tables.

You can still have "one controller" to manage all these tables as one big table.

Just create a union-all view on top of those hundred tables, and write a couple of instead-of triggers to deal with inserts/updates/deletes. Then have your "controller" based on top of this view.

On Mon, Jan 28, 2013 at 10:03 AM, Jose Soares <jose.soares_at_sferacarta.com>wrote:

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

-- 
Toon Koppelaars
RuleGen BV
Toon.Koppelaars_at_RuleGen.com
www.RuleGen.com
TheHelsinkiDeclaration.blogspot.com

(co)Author: "Applied Mathematics for Database Professionals"
www.rulegen.com/am4dp-backcover-text


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

Received on Mon Jan 28 2013 - 10:13:36 CET