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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
