Re: Single Code Table or Separate Code tables dilemma

<developer combat starts>

Referential integrity is still present if you create Master lookup table with type attribute:

lookup_id         varchar2(20)  pk
lookup_type     varchar2(20)  pk
description       varchar2(255)

> 1. specific attributes for a particular code type is logically and physically
>separated from other code types.

It does not matter - just don't read the attributes' values that are irrelevant

> 2. a table lock affects only the concerned code table

who needs a table lock in a lookup table??!! :-)

> 3. granular control over the individual code table

still present with the lookup_type column.

</developer combat ends>

So now for the DBA side: do you prefer to have multiple numerous small tables or one large? :-)

Regards,
Michael Netrusov,
www.atelo.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, March 22, 2001 11:50

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

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