Re: Physical Database Design - Code Tables
- From: JApplewhite@xxxxxxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 21 Nov 2006 14:22:41 -0600
IMHO the biggest problem with one code table is that you can't create FK
constraints on the "child" tables pointing to the value column in the
single code table, since each child table only uses a subset of rows in
the code table. If all values in the code table were unique, you could,
but that's not very likely.
Individual code tables allow the declared PK/FK constraints you really
need to enforce data integrity. Consistent naming conventions can go a
long way to allowing fairly easy associations of parent code tables to
their children.
Jack C. Applewhite - Database Administrator
Austin (Texas) Independent School District
512.414.9715 (wk) / 512.935.5929 (pager)
I feel so unnecessary. -- Rufus Thomas
( "Do the Funky Chicken")
"Jared Still" <jkstill@xxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
11/21/2006 01:35 PM
Please respond to
jkstill@xxxxxxxxx
To
paulastankus@xxxxxxxxx
cc
oracle-l@xxxxxxxxxxxxx
Subject
Re: Physical Database Design - Code Tables
On 11/21/06, Paula Stankus <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
Other related posts:
