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> wrote:


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: