Re: Design Question
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: Jared Still <jkstill@xxxxxxxxx>
- Date: Mon, 19 Oct 2009 09:13:56 -0700 (PDT)
Personally, I never liked the single table approach for codesets/lookup data.
I've started down that path a few times and always decided that it was more
hassle and risk than individual codeset tables. I think it's a bit of a
holdover from non-relational days and involves several negatives.
Negatives include lack of the ability to define FK's (sure, you can design
triggers, but what a hassle), lack of clarity with respect to the nature of the
data in the table (I know what person_type_code stores, I have no idea what a
table called codesets really stores), hierarchial issues (the design itself
requires a multi-tier hierarchy of data. IE: code_set_type,
code_set_identifier, code_set_value) which complicates the SQL generated.
It might work for a small design with only a few lookups, but for a large
design I think it's just asking for trouble.
My opinion, YMMV.... :-)
RF
Robert G. Freeman
Oracle ACE
Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide
(Sybex)
Oracle Database 11g New Features (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Other various titles
Blog: http://robertgfreeman.blogspot.com
________________________________
From: Jared Still <jkstill@xxxxxxxxx>
To: robertgfreeman@xxxxxxxxx
Cc: Muru.Balakrishnan@xxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Fri, October 16, 2009 3:41:12 PM
Subject: Re: Design Question
On Fri, Oct 16, 2009 at 2:25 PM, Robert Freeman <robertgfreeman@xxxxxxxxx>
wrote:
>
>...
>One thing I've found is that the *more* codesets you have, the more complex
>the queries can get. The more complex a query gets, the more difficult it
>becomes to tune. I had a model a couple of years ago with some very complex
>security rules. Each rule really translated into it's own codeset table. Also,
>because of the complexity of the security rules, SQL had to be dynamically
>generated based on various contexts (what a mess!!). It involved lots of
>sub-queries, etc. Performance was a bear for a number of reasons.
>
>
Another approach to that was one advocated by Gurry and Corrigan in
Oracle Performance Tuning (O'Reilly 1996) is to combine multiple lookup
tables into a single table.
IIRC another column was added to indicate what the codes were used for.
That approach may be useful, and as usual, YMMV and it requires testing.
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Oracle Blog: http://jkstill.blogspot.com
Home Page: http://jaredstill.com
Other related posts:
