Re: Database design issues

On Wed, 15 Aug 2001 10:44:55 -0500, "John" <jhook_at_regenstrief.org> wrote:

>Hi,
>
>We are in the process of migrating a proprietary database to Oracle and are
>having some internal discussions regarding the implementation of code tables
>in the database. By code tables, I mean small lists of values that control
>the values stored in other tables. (e.g. RACE, SEX) I'd like to hear peoples

There are many options available to you.

>there are a several problems with this approach. The biggest one being that
>I can't get it to work (at least not in Oracle 7.3). I am told by some

If you are in the process of porting to ORACLE, I'd strongly advise against doing so to 7.3. Two main reasons: first, it's not a supported version, you'de be mving again soon. Second, you are severely limiting yourself in what you can use, vis-a-vis what can be done in 8 and 9.

>
> If you have opinions and/or suggestions on how to deal with this issue, I'd
>love to hear them.
>

Sure. First of all, recreating check constraints as in your option 1 is not a major burden in any version of ORACLE. Takes the best part of 2 seconds to run and maybe a coupla minutes to write. Can't be that much, unless you anticipate to get these changes more often than the periods I just described!

Second, I can suggest an even less conventional approach, although with much better performance: define these constants and lookups as PL/SQL tables and expose access to them via methods in a package. It's really very easy to do and has the advantage that you can call them from cache anytime via your normal SQL. Very, very fast. Maintenance is resumed to changing one source file and re-compiling it to implement a change.

If you want to be really "high-tech", you define them within an object-relational type framework and expose methods to access them as functions, or procedures with OUT parameters, your choice. I've done this recently in a similar case and it is mind-blowing in performance. Want to add more check constraints? Well, code them into your PL/SQL. Want to be really flexible? Store the darn things in a table, free-format. Then load them into the package at run-time and apply as much validation as you want.

Remember, CPU-cycles are cheap (and getting much cheaper, faster) than disk accesses. Memory too. Soooooo, use the darn things instead of letting them go to waste with Open-GL screen savers! ;-)

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Thu Aug 16 2001 - 07:44:39 CDT