| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Single Code Table or Separate Code tables dilemma
As to the encapsulation: Yes things should be encapsulated, fine. But are you saying you do not use any queries in your encapsulating procedures? Have you ever seen an app that was completely encapsulated? What about the developer that gets repeatedly reprimanded for not following corporate standards but is married to the CEO's daughter?
You keep referring to code that has to be changed anyway. Perchance another example, we'll use the DOCTOR table from the Ghosalkar/Kilchoer thread:
CREATE TABLE I_DONT_LIKE_MASTER
( CODE_TYPE VARCHAR2(4) NOT NULL,
CODE_VALUE VARCHAR2(3) NOT NULL,
CODE_DESC VARCHAR2(20) ,
PRIMARY KEY (CODE_TYPE, CODE_VALUE) )
SPLT N SDFLJDSL SPLT FP SDFSDKFDS SPLT OBG SFLSDJFSD TYPE MD SDFLSDFSDF TYPE FP SDFJDSFJ TYPE OPH SDLFJDSKF
CREATE TABLE DOCTOR
(.....,
SPECIALITY_CODE_TYPE VARCHAR2(4), SPECIALITY_CODE_VALUE VARCHAR2(3), TYPE_CODE_TYPE VARCHAR2(4), TYPE_CODE_VALUE VARCHAR2(3),
D1 SPLT N TYPE OPH D2 SPLT N TYPE MD
The users tell us: We need to track specialties as being primarily inpatient or outpatient so that extra information can be collected for inpatient situations (I know, those of you doing work in health care can shoot all kinds of holes in this.)
The one table way:
CREATE TABLE SPECIALTIES
(CODE_VALUE VARCHAR2(3) NOT NULL,
CODE_DESC VARCHAR2(20) ,
INPATIENT_YN VARCHAR2(1),
PRIMARY KEY (CODE_VALUE) )
Then I copy all the entries from the code table. (script that must run at
the production site - asking for trouble), remove them from the old table
(even more dangerous). Update the master table entry program and procedures
to not allow the archived code any more. Rip through every scrap of code in
the system to find any reference to I_DONT_LIKE_MASTER to make sure it does
not have any need of the archived code type. Please bear in mind that
although you are being a very good programmer and using encapsulation, the
idiots that got fired last week were not, so we still have to look. Any
that are found will have to be changed. Now I create my new entry form to
add the field and update the registration code. Then I sit back and wait
for 1) one of the client's Access reports to return no data because they did
not realize the data had been moved, or 2) some of our code to break because
we overlooked something.
The multiple table way:
CREATE TABLE SPECIALTIES
(INPATIENT_YN VARCHAR2(1))
Now I create my new entry form to add the field and update the registration
code. Done. Where are these other code changes that have to be done?
All the old code (encapsulated or not) still works the way it used to. If I overlook something, it may not have the added functionality, but it will not break.
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
PS: I think this discussion would best be continued with copious amounts of alcohol. If you are ever in Dallas, look me up.
> -----Original Message-----
> From: Michael Netrusov [mailto:mn_at_g-fax.com]
> Sent: Friday, March 23, 2001 4:31 PM
> To: Multiple recipients of list ORACLE-L
> Subject: 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).
>
-- 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).Received on Mon Mar 26 2001 - 09:40:39 CST
 |
 |