RE: Single Code Table or Separate Code tables dilemma

From: Toepke, Kevin M <ktoepke_at_cms.cendant.com>
Date: Fri, 23 Mar 2001 16:09:17 -0800
Message-ID: <F001.002D6C44.20010323143031@fatcity.com>

I've found so many bugs in code caused by developers forgetting to add "AND code_type = 'SOME_VALUE'" to there WHERE clauses when multi-purpose code tables are used.....

Kevin Toepke

The information in this electronic mail message is Cendant Confidential and may be legally privileged. It is intended solely for the addressee(s). Access to this Internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or action taken or omitted to be taken in reliance on it is prohibited and may be unlawful.

The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking protective and remedial action about viruses and other defects. Cendant Corporation is not liable for any loss or damage arising in any way from this message or its attachments.

-----Original Message-----
Sent: Friday, March 23, 2001 3:46 PM
To: Multiple recipients of list ORACLE-L

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: http://www.orafaq.com
-- 
Author: Norrell, Brian
  INET: BNorrell_at_QuadraMed.com

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Toepke, Kevin M
  INET: ktoepke_at_cms.cendant.com

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 Mar 23 2001 - 18:09:17 CST