Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Single Code Table or Separate Code tables dilemma

Re: Single Code Table or Separate Code tables dilemma

From: Michael Netrusov <mn_at_g-fax.com>
Date: Thu, 22 Mar 2001 14:35:04 -0800
Message-ID: <F001.002D5662.20010322142244@fatcity.com>

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

What's wrong with a composite foreign key constaint? Works for me all the time..

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

I DO. Nothing is mixed - you can even partition this table if you prefer to keep unlike data in different places. As for not creating indices for small tables: small lookup tables do not need any index except PK index. Not having a PK constraint is not a good thing anyway - how you will define an intergrity constraint? For small tables FTS would be done by CBO regardless of index presence ( unless you specify hints ).

> > 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 don't think a join to 100K rows table versus a join to 10 rows table would make a big difference.. Most likely the join was slow  was it? ) because of some other factors.

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

Do developers design ER models in your shop? It should be you or a DA. :-)

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

In my current shop I am a pl/sql developer :-) ... as well as a DA and a DBA. This saves me a LOT time and nerves.

Regards,
Michael Netrusov.

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

-- 
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).
Received on Thu Mar 22 2001 - 16:35:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US