From mn@g-fax.com Thu, 22 Mar 2001 10:59:49 -0800 From: "Michael Netrusov" Date: Thu, 22 Mar 2001 10:59:49 -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: 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).