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: Michael Netrusov <>
Date: Fri, 23 Mar 2001 12:07:16 -0800
Message-ID: <>

> Preface: I am a developer!
> If the DBA wants to have 10000 tables, no skin off my nose. A generic
> maintenance form and LOV form for a set of tables all with the same format
> is not that huge a task.
> The best developer argument AGAINST a centralized code table is that
> eventually someone will request an enhancement that adds functionality to
> one of the codes beyond a simple code/description table. This means the
> codes have to be pulled out of the central code table and created as a new
> table with the 3 flags and 5 coded fields to support the new functionality.
> 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.

> 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.

> 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.

Michael Netrusov

Please see the official ORACLE-L FAQ:
Author: Michael Netrusov

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 - 14:07:16 CST

Original text of this message