RE: Physical Database Design - Code Tables
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 21 Nov 2006 15:42:15 -0500
Hear, hear. Using one log table to make life "easier" (and maybe not easier,
as Jared pointed out) for developers is the tail wagging the dog. The database
design has to possess integrity and be independent of such considerations.
Paul Baumgartel
CREDIT SUISSE
Information Technology
DBA & Admin - NY, KIGA 1
11 Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of rjsearle@xxxxxxxxx
Sent: Tuesday, November 21, 2006 2:57 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Physical Database Design - Code Tables
I personally don't like these things.. Using one table for all code sets
precludes using RI to protect the contents of the code fields. This leaves RI
as a problem for the applications to manage. Long term, it is easier and safer
(IMHO) to use separate tables and let the DB do the work. I have seen a a
large-ish db (400GB) use one code table and the data quality suffered. I then
implemented a program of change to gradually separate each code table and put
RI in place. The developers got used to the change and ended up liking it (If
I did the work creating the tables :)
Russell
On 11/22/06, Jared Still < jkstill@xxxxxxxxx <mailto:jkstill@xxxxxxxxx> >
wrote:
On 11/21/06, Paula Stankus < paulastankus@xxxxxxxxx
<mailto:paulastankus@xxxxxxxxx> > wrote:
I know that for developers having the generic, one-size-fits-all codetable is
easier for them to code.
Uh-huh. They don't have to remember all those pesky code table names.
They just need to remeber the values of the identifier columns:
AddressType, CustomerType, ...
Wow! That's more work than we thought!
Maybe we could get the DBA to create some views...
However, I am very worried that having one generic codetable for all
applications, all tables and all code fields could cause serious contention.
Will the code table be updated frequently?
If so , then you may want to reduce the number of rows per block
via 'alter table TABLE minimize_records_per_block' or a high pctfree.
HTH
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
Other related posts:
