RE: Single Code Table or Separate Code tables dilemma

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

This requires a "composite referential integrity constraint" such as: alter table tname

      add constraint fk_whatever
          foreign key (extra_column_for_code_table_key,
                       column_i_really_care_about)
          references  master_codes(lookup_id, lookup_type);

Still don't like it. Maybe it's just a preference thing...

> granular control over the individual code table
You DON'T have granular control for caching specific tables because everything is mixed together. (Hawaiian pidgin translation: "All kalikaka li' dat. Da' kine chop suey. Easy Brah." :) No control for different indexing requirements or not even having an index for small tables where it would be better to do a FTS.

> prefer to have multiple numerous small tables or one large?
Numerous small tables are not a problem, actually a benefit because you have more granular control for tuning. I once had the misfortune of having to do reports where the "mother of all code tables" had around 100,000 rows. (It was a big, dumb 3rd party app with roots in COBOL and it had a lot of unmaintained junk in it). To pick up a description in a large multi-table join query I had to join against the mother of all code tables where a small 10 row table would have sufficed.

I'm not exactly a relational purist and for some apps code tables may work just fine. But usually it's just for duhveloper convenience (laziness) and why make life easy for them ;-) at the expense of "normal" relational design. Especially if they haven't bothered to answer the challenge I posed below? When I was a developer I had a set of objected oriented class libraries and could bang out a new window for a lookup table in 60 seconds. Of course I probably spent 600 hours developing and maintaining my libraries... sigh. Oh yeah, developer designed tables containing metadata come in handy also.

Duhveloper combat is so much fun! :-) Unfortunately they usually outnumber us DBA's so we have to be particularly nimble. ;-)

Regards,
Steve Orr

-----Original Message-----
Netrusov
Sent: Thursday, March 22, 2001 10:56 AM
To: Multiple recipients of list ORACLE-L

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

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