Re: Single Code Table or Separate Code tables dilemma

• Original Message -----

> As to the encapsulation: Yes things should be encapsulated, fine. But are
> you saying you do not use any queries in your encapsulating procedures?

I am using cursors ( mostly implicit ).

> Have you ever seen an app that was completely encapsulated?

Yes, I had. Not every app needs ad hoc queries..

> What about the
> developer that gets repeatedly reprimanded for not following corporate
> standards but is married to the CEO's daughter?

If the CEO still keeps him, he is dumb. If the CEO is dumb, the whole company goes to hell sooner or later.
What's the point of taking care of the project then? :-)

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

I would put this into code_type = 'specialty_type_inpatient' and code_type = 'specialty_type_outpatient', not adding the inpatient_yn column.

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

I would say we have to prevent this :-) "Code supervizing" they call it..

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

So Access report does not use the packaged code? Too bad..

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

Changes to the doctor table.

> 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.
>
> PS: I think this discussion would best be continued with copious amounts of
> alcohol. If you are ever in Dallas, look me up.

Agreed ;-) If you'll be in Washington DC, let me know!

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

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