RE: Physical Database Design - Code Tables

  • From: "Ken Naim" <kennaim@xxxxxxxxx>
  • To: <oracledba.williams@xxxxxxxxx>, <jkstill@xxxxxxxxx>
  • Date: Tue, 21 Nov 2006 16:43:47 -0500

I have used many third party apps with single code tables and they have
caused me many nightmares, performance being the least of them. The
performance issue we solved by having 1 records per block reducing the hot
block contention. 
 
One nightmare that I remember was pretty simple a developer decided to
reorder a drop down list on the front end, unfortunately the application
tool changed the codes values to accommodate this change and since the app
enforced referential integrity, all 4 million members now lived in a
different state and I had to clean it up, based on zip code for the members
who had them (90%) and based on street and city for the rest of the records
that didn't have them.
 
Ken
 
  _____  

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Dennis Williams
Sent: Tuesday, November 21, 2006 4:30 PM
To: jkstill@xxxxxxxxx
Cc: paulastankus@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Physical Database Design - Code Tables
 
Paula,
 
One issue I don't see mentioned is the number of columns. In my experience,
some codes tables have a different number of columns than others, and those
columns may have different properties (data type, column name, etc.). If you
use a single codes table, then all codes must have the same columns. If at a
later point, some codes are found to need an additional column, then all
codes will get the added column. If you add that column, then you have to
hope that all the application code will be unaffected. This isn't probably a
big deal, but I think it points to the non-normalized situation that a
shared code table gets you into. 
 
Dennis Williams

Other related posts: