Re: Database design issues

On Wed, 15 Aug 2001, jhook_at_regenstrief.org wrote:

> 3. One big table to hold all of the codes.

I really like this approach. I used it on the last 5 or so databases I designed and implemented. Was easy to understand, one place for maintenance and is very easy to write code against.

> The final option that we have been discussing is to put all of
> the codes into a single table with a column to identify the
> various lists. The table would look something like:
>
> CREATE TABLE CODES (
> LISTNAME VARCHAR2 (30),
> CODE_VALUE VARCHAR2 (50) );
>
> This table would contain entries like:
>
> LISTNAME CODE_VALUE
> -------- ----------
> COLOR RED
> COLOR BLUE
> SEX M
> SEX F
I would change the design a bit.

CREATE TABLE CODES (
  dummy_id integer;
  LISTNAME VARCHAR2 (30),
  CODE_VALUE VARCHAR2 (50) ); Use a sequence, or just create it yourself, to generate the dummy_id (call it something appropriate like code_id) and store this id in all of your tables. I would even get more anal and create a code_type table to store you LISTNAME values and have a dummy_id in the codes table for that.

Then, I would use RI for the constraints.

> The advantage of this option is that all of these codes are
> stored in a single table making it easy to report, browse,
> edit the values. However, there are a several problems with
> this approach. The biggest one being that I can't get it to
> work (at least not in Oracle 7.3). I am told by some people
> that DB2 would allow this arrange via a select statement in a
> check constraint using something along the lines of:
>
> ALTER TABLE T1 ADD CONSTRAINT CK_SEX CHECK (SEX IN (SELECT
> CODE_VALUE FROM CODES WHERE LISTNAME = 'SEX'));
Use RI and forget about coding your own RI check constraints.

> Oracle 7.3 balks at this syntax.
>
> I also can't figure out how to make a foreign reference that
> would be limited by both the LISTNAME and CODE_VALUE
> columns.

With a dummy_id, you can use RI nicely.

> Also the size of the CODE_VALUE column in the CODES table
> doesn't match the size of the referencing column in the T1
> table.

The dummy_id gets rid of this as well.

> People here speculate that there is someway around this but no
> one can figure out the syntax.
>
> Another problem, as I see it with this approach, is that
> inevitably someone is going to want to store additional
> attributes with some of these lists of codes.
>
> Then we going to be adding columns whose meaning is context
> dependant.

Yes, that could be a problem. Maybe, on the outliers, you then create a particular table to take care of these, but still use RI from the Codes table?

Another option from this is, for each code type, you could create a view, and then the outlier view could join to its particular outlier table to retrieve those particular columns.

> If you have opinions and/or suggestions on how to deal with
> this issue, I'd
> love to hear them.

-- 
Galen Boyer
It seems to me, I remember every single thing I know.