Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Physical Database Design - Code Tables

RE: Physical Database Design - Code Tables

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Tue, 21 Nov 2006 15:42:15 -0500
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663B8CB@ENYC11P32005.corpny.csfb.com>


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_at_credit-suisse.com
www.credit-suisse.com

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of rjsearle_at_gmail.com Sent: Tuesday, November 21, 2006 2:57 PM To: oracle-l_at_freelists.org
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_at_gmail.com <mailto:jkstill_at_gmail.com> > wrote:

On 11/21/06, Paula Stankus < paulastankus_at_yahoo.com <mailto:paulastankus_at_yahoo.com> > 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
==============================================================================
-- http://www.freelists.org/webpage/oracle-l
Received on Tue Nov 21 2006 - 14:42:15 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US