Re: Code Tables

I tend to be a "normalization purist" and side with you on this however... this approach is not unusual. I've seen a couple of commercial grade apps that use this approach. Generally, simple validation tables consistently have the same columns: a key and a description with maybe some additions like "last_update," and "last_update_by." Some may have columns for "effective_date" and "expire_date," etc. In large apps there will be 100's of these tables and most are relatively small so they lump them all up into one denormalized validation table. One commercial app I saw did this then and just had one user interface window to develop versus one interface for each of several hundred tables. The referential integrity was enforced in the app only, not in the database. Supposedly they were saving themselves lots coding work on the front end. (I guess you could still enforce referential integrity with triggers but that's more coding.)

But I still think you could have your cake and eat it too. A good object oriented tool with robust class libraries could dynamically setup its connection to the table and generate the necessary objects for the window: input boxes, menus, title bars, scroll bars, buttons, etc. That way you could still reduce the code to one interface window and have referential integrity in the database without any additional coding. The commercial apps referred to above were OLD. With today's tools there should be no reason to make this design compromise.

Don't compromise! Just normalize!

IMHO,
Steve Orr

• Original Message ----- From: Malcolm Turner <MTurner_at_wcs.dnet.co.uk> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Thursday, May 11, 2000 3:46 PM Subject: Code Tables

> A design question -
> In a recent development, the designer used a single
> CODES table with a domain to identify the codes i.e.
> (DOMAIN_CODE VARCHAR2(12)
> ,CODE VARCHAR2(12)
> ,DESCRIPTION VARCHAR2(30))
> I maintained that there should have been a separate
> table for each domain for a variety of reasons - the
> main one being that I don't see how you could have
> referential integrity with 1 generic table.
>
> Could I ask for views for/against, please.
>
> TIA
>
> Malcolm
> ============================
> Malcolm Turner
>
> Oracle Developer,
> Belfast
> email :- mal_at_wcs.dnet.co.uk
> ============================
>
>
> --
> Author: Malcolm Turner
> INET: MTurner_at_wcs.dnet.co.uk
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Fri May 12 2000 - 10:32:13 CDT