Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Single Code Table or Separate Code tables dilemma

RE: Single Code Table or Separate Code tables dilemma

From: Norrell, Brian <>
Date: Fri, 23 Mar 2001 13:06:02 -0800
Message-ID: <>

Snipped text

> > The DBA work on that is easy, but then the developers go
> hunting in all the
> > existing program code that hits the code table (haystack)
> to find all the
> > places where that program code is actually using the code
> in question
> > (needle).
> Well, in the case of 100 tables you are still hunting all the
> code looking for pieces which are relevant to that changed table.
> Depends on your code. I prefer to encapsulate mine, so
> reference to a table is enclosed in its own package.

My point is that I can use grep to search the entire code for "order_status" and get a short list (maybe 10) of places to look. It is much more difficult to search for "code_table", get a list of 10000, then filter through the results for some bastardized variety of "code_type = 'STATUS'" to get down to those same 10.

Not everything can be encapsulated in a procedure. This is a database we are talking about, so people do still write queries where it is easier to join the lookup table to the master table directly. If the code has to be broken out of the central lookup table, you have to update every query that joins it in, even if all that query needs is the description. If it is already split out, the only code I really have to be concerned with is the places where the new functionality applies.

> > If I were starting a product from scratch and there was a
> central code table
> > I would probably code against a set of views in
> anticipation of the above
> > event, so the DBA ends up creating 10000 objects anyway.
> Views consume resources and bring new dependencies, so the
> administration becomes more complicated.
> What is your point? If somebody makes a change request, youl
> still will have to change your code. If you are adding columns and
> prefer not to change the existing procedures, add a new
> procedure ( with the same name ) which works with new attributes.

The difficulty is not in making the changes, but in doing the analysis to figure out where the changes need to be made.

> > On the other hand:
> > 1. Everywhere I have worked, there has been a central code
> table of some
> > sort.
> > 2. In all cases that code table was put in place by the
> DBAs, not the
> > developers, because they didn't want all those tables and
> were not really
> > hung up on referential integrity that the application was
> enforcing anyway.
> As Steve pointed out, it's just a matter of preference. A few
> years ago I prefered multiple similar tables vs one master lookup..
> Now I implemented the opposite approach and I am happy with
> it. Less code ( packages consuming memory ), less database objects to
> administer.

Brian Norrell

Please see the official ORACLE-L FAQ:
Author: Norrell, Brian

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: (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 Mar 23 2001 - 15:06:02 CST

Original text of this message