Re: Database Design Best Practice help

From: Nuno Souto <dbvision_at_iinet.net.au>
Date: Tue, 29 Jan 2013 19:12:39 +1100
Message-ID: <51078477.9040500_at_iinet.net.au>

Toon Koppelaars wrote,on my timestamp of 28/01/2013 8:13 PM:
> 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.

Great solution, thanks for that, Toon.
The only instance I can think of where having multiple tables might be an advantage is if the number of tables changes frequently - for whatever reasons. That might then bring on frequent major existing SQL re-writes to accomodate loss and gain of tables.
Whereas presumably a single table might just be a case of "found/not found" for a particulat code/value combination type - hence no code change needed. If that is not the case, then multiple tables as in above concept would work perfecly well - and an extension of it could be used to circumvent the problem I described.

-- 
Cheers
Nuno Souto
in sunny Sydney, Australia
dbvision_at_iinet.net.au
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 29 2013 - 09:12:39 CET