Re: DB Design question

Austin Moseley <\"moseba_at_audv55.aud.alcatel.com'> wrote:
>
> >> 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
> ..
>
> >> > 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
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> !!!!
>
>
> Sounds like your data has not been analyzed, thus your tables
> are not normalized. You are lucky you are running into this problem now,
> not later when it costs you many hours to make a simple change in order
> to make a "quick" bug fix, when your referential integrity is enforced by
> your software not by the database product.
>
> I would call a halt to everything and reexamine your E-R diagrams
> and business rules for holes and redundancies. Don't add requirements or
> do design without referring back to these for any unforseen effects.
> Nothing should be done which is not dictated by the nature of the data.
>
> If these are really common tables, then provide stored procedures to
> access them. If the tables change, then the changes ripple up to
> the call interface, not all the way through the project. Also, you
> can compile your code without the Sp being completed. Also, the SQL
> to get the data or insert the data only has to be written once, not
> x times by k people.
>
> This will mean less work for all, both app guys and the DBAs.
>
>
> Austin
>
>
>
>

A common code table makes a lot of sense if it is used properly.

First, remember the definition of an entity;

Something of SIGNIFICANCE about which information must be know or held.

If it is significant it should be on your ER diagram and NOT in a common code table.

If it is just a translation from a code to a user friendly caption then use a common code table.
- Advantages. One table to manage. One table to buffer in the SGA. - Good control of codes.

David Roth

My opinions are my opinions Received on Fri Apr 05 1996 - 00:00:00 CEST