Re: Database design issues

From: Keith Boulton <kboulton_at_ntlunspam-world.com>
Date: Thu, 16 Aug 2001 07:39:24 +0100
Message-ID: <5PJe7.36694$zs.351807@news11-gui.server.ntli.net>

"Galen Boyer" <galenboyer_at_hotpop.com> wrote in message news:u7kw53z2u.fsf_at_verizon.net...
> On Wed, 15 Aug 2001, jhook_at_regenstrief.org wrote:
>
> > 3. One big table to hold all of the codes.
>
> I really like this approach. I used it on the last 5 or so
> databases I designed and implemented. Was easy to understand,
> one place for maintenance and is very easy to write code against.
>

I prefer the table per code approach. So you have a few more tables - so what?

One table per code guarantees the integrity of the data because it takes care of locking which is painful to manage manually. It's almost certainly faster as well, since the alternative involves writing a trigger to validate the data on entry or a pl/sql function that can be used in a check constraint (if that's possible, I've never tried it).

One option I've come across that's alleged to be faster is to maintain a table of column names and the allowable values ie Table: T1 Column: SEX Value: M, T1 Column: SEX Value: F... and then have a procedure that dynamically builds check constraints to be invoked after code table maintenance. Received on Thu Aug 16 2001 - 01:39:24 CDT