Re: DB Design question

David J Roth wrote:
>
> 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

A solution we have adopted in the project I am now on is to have a common code table (which, as noted above, is very easy to maintain), and then to define appropriate Entities for each type of code in the common table.

Each Entity is instantiated by views on the common code table which returns only those codes in a given code type. A non-server enforced foreign key can also be defined in a table pointing to the code view.

Access to the by developers should be through the views, not the code table.

-- 
Jeffrey M. Stander
WillStand Consultants Pty/Ltd
GPO Box 269
Hobart Tasmania 7001
Australia
jstander_at_acslink.aone.net.au