Re: DB Design question

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!!!

Combining unrelated data into a common code table is a carry over from REDEFINES in VSAM files. It has no place in the relational model. Like you said you cannot have foreign keys on this sort of data even if you wanted to.
Good luck convincing the DBA who did the design. Received on Tue Mar 26 1996 - 00:00:00 CET