Re: DB Design question

>
> Ken Ferrell <73573.2066_at_CompuServe.COM> 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.). 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
>
>

Change now, before it is too late. Changing will (usually) increase performance, lessen the amount of space needed, and make it quite a bit more understandable. It does increase the DBA's work (slightly), but shoving everything into one table is just selfish.

Bob Walters                     bobwal_at_infocom.com