Re: Single Code Table or Separate Code tables dilemma

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

Queries still can be ( and should be ) encapsulated in packages. If you are taking this to-be-enhanced 'status' entity out of the master lookup table, all you have to do is to develop a new package, change the calling code and prohibit the usage of code_type = 'status' in the master lookup table. This still would be changed if you had a separate package and table for the 'status' entity.

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

please see above. The changes still would 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.

No arguing with the latest passage? :-)

Regards,
Michael Netrusov

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Michael Netrusov
  INET: mn_at_g-fax.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).