From sorr@arzoo.com Thu, 22 Mar 2001 12:47:41 -0800 From: "Steve Orr" Date: Thu, 22 Mar 2001 12:47:41 -0800 Subject: RE: Single Code Table or Separate Code tables dilemma Message-ID: MIME-Version: 1.0 Content-Type: text/plain > 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 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. 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" 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: _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@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@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@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@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@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@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).