Re: Physical Database Design - Code Tables

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_at_gmail.com>
Sent by: oracle-l-bounce_at_freelists.org
11/21/2006 01:35 PM
Please respond to
jkstill_at_gmail.com

To
paulastankus_at_yahoo.com
cc
oracle-l_at_freelists.org
Subject
Re: Physical Database Design - Code Tables

On 11/21/06, Paula Stankus <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

--
http://www.freelists.org/webpage/oracle-l