RE: Single Code Table or Separate Code tables dilemma

Code tables... been there done that (with PowerBuilder/Oracle) and I don't like it. Here's why...

Large apps may consist of 100's or 1000's of lookup tables so duhvelopers like the "master code table" idea because they only have to build one front end for maintaining all the "lookup" values. But what about referential integrity? If you have to do it against one massive code table via triggers or from front end code then you're adding work back to the coding effort. What about database tuning? Lookup tables are good candidates for caching... Are you going to cache one huge, denormalized code table? If your lookup values are in multiple normalized tables then you the DBA can choose which tables are suitable for caching.

With a few exceptions, most "Lookup tables" have a common structure with just two columns: one for the PK value and another for the description. You could review all the referential integrity/data lookup requirements in your app and come up with a common structure for all lookup tables that could handle most situations. Here's are some example columns: <table name>_ID
(the primary key);

short_label; long_label; short_description; long_description; enabled_flag; effective_date; expiration_date; date_created; last_update; last_updated_by.

I'd put my foot down and place the following challenge to the duhvelopers:

"Any SAVVY developer worth his salt should be able to create a robust, object oriented design to make coding a snap no matter how many lookup tables there are. [Good] Developers can do this by inheriting from a parent window or set of objects in his class library. The label and description columns could be for GUI display. The enabled_flag could default to 'Y' and be referenced as standard practice in the where clause of every lookup query. Ditto for the effective_date and expiration_date columns where your validations have a time fence constraint such as a 'date_DBA_hourly_rate_increase_becomes_billable column." :>)

Ready for duhveloper combat...
Steve Orr

-----Original Message-----
Sent: Wednesday, March 21, 2001 4:32 PM
To: Oracledba (E-mail); ORACLE-L (E-mail)

Guys,

We r working on a Datawarehouse solution.

Our Duhvelopers want to merge all code tables into a single table by adding a codetype column.

with reference to this, i came across this article from Steve's site http://www.ixora.com.au/tips/design/meta-data.htm

i want to put them into different individual code tables instead of a single table, for the foll reasons.

1. specific attributes for a particular code type is logically and physically seperated from other code types.
2. a table lock affects only the concerned code table
3. granular control over the individual code table

i am short of arguments

wld be grateful, if ull can advise me which would be better from performance perspective.

-Mandar

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steve Orr
  INET: sorr_at_arzoo.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).