Re: DB Design question

Ken Ferrell <73573.2066_at_CompuServe.COM> wrote:

If you have different attributes for things (eg Governor of State) then they are different entities and you should have no concerns whatsoever about implementing them as separate tables. You'll be pleased you did in the long run because you can be _sure_ that other attributes will come along which will need adding to the table. Do it now before it's too late or too hard.

> 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.). This table has a column for fldnm (the
>field name of the referencing column, i.e. 'state' or
>'emp_status'). If you want all the states, you SELECT * from
>CODE_LOOKUP WHERE fldnm = 'STATE'; We are running into two
>problems. First, we can not enforce referential integrity through
>foreign keys and we are needing more information about these
>codes that is not common to the other codes stored in this table
>(i.e. the governor for the state or the default pay rate for the
>employee type). Short of adding these specialized columns to the
>common code table, we must create 1 to 1 tables
>(EMPLOYEE_TYPE_INFO, STATE_INFO) to store the specialized info.
> 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
Received on Tue Mar 26 1996 - 00:00:00 CET