Re: DB Design question

Ken Ferrell wrote:
>
> I'm working on a PB/ORACLE project. Our DBA has
> implemented a concept of 'common code tables'. A single table
> for all our of our codes (state abbreviations, status codes,
> employee types, etc.).[stuff snipped]
> Right now we are at the point where we can go back and
> create separate code tables (STATE_CODES, STATUS_CODES) and drop
> the common code table. Currently the common code table contains
> over 50 specialized codes with over 1000 rows of code
> information.
> Any suggestions, feedback or comments would be greatly
> appreciated!!!
>
> Ken Ferrell

Ken,

We have a similar situation. Our solution has been to build separate "setup" tables for those field elements that need additional information beyond what the common code table can deliver, leaving everything else in the common table only. Whether or not this will work for you is really a function of how many of your fields require such additional information.

For example, we have a common translate table that holds all fields that can logically fit into the following schema:

Fieldname   (Key)	char(20),
Fieldvalue1  		char(1),
Fieldvalue2 		char(2),
Fieldvalue3 		char(3),
Fieldvalue4 (Key)	char(4),
Descrshort		char(10),
Descr			char(30).

If a fieldname only has codes which go up to, for example, 2 characters, those values are duplicated in the Fieldvalue3 and Fieldvalue4 columns, just to keep the key unique.

For fields requiring additional information, we create a setup table specifically for them.

Good Luck!

Larry Tipton

-- 
*************************************************************
"Be proud of those who are willing to give their life for
their country, be afraid of those who WANT TO!"

My opinions only; nobody else's.  Deal with me alone!
*************************************************************