| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Single Code Table or Separate Code tables dilemma
ok.
lookup_id varchar2(20) pk lookup_type_id varchar2(20) pk, fk description varchar2(255)
I use the first approach when lookup_type has several invariant values.
One index with an extra level of index block causes a performance hit only when you are running your DB on a PII/ 256M/ 1 IDE hard drive NT box :-)
Less tables = less packages, less sql in your sql area, less database objects.
Nice, but this construct violates Second Normal Form. I only denormalize for compelling reasons, like for performance reasons, and I don't see how performance is enhanced by jamming all this stuff into one big table. You may end up with an index that has an extra level of index blocks, which comes with a performance hit.
>>> mn_at_g-fax.com 03/22/01 01:55PM >>>
<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
> 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: Tim Sawmiller INET: sawmillert_at_state.mi.us 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 Fri Mar 23 2001 - 13:12:06 CST
 |
 |