Database design issues

From: John <jhook_at_regenstrief.org>
Date: Wed, 15 Aug 2001 10:44:55 -0500
Message-ID: <9le4ai$bbi$1@hercules.iupui.edu>

Hi,

We are in the process of migrating a proprietary database to Oracle and are having some internal discussions regarding the implementation of code tables in the database. By code tables, I mean small lists of values that control the values stored in other tables. (e.g. RACE, SEX) I'd like to hear peoples opinions on this issue in general and also get specific suggestions on how to handle this in Oracle specifically.

Assume the following table exits:
CREATE TABLE T1 (SEX VARCHAR2(1),COLOR VARCHAR2(5)); There seem to be 3 basic options for controlling the input to the T1 table:

Restrict via check constraints

ALTER TABLE T1 ADD CONSTRAINT CK_SEX CHECK (SEX IN ('M','F')); ALTER TABLE T1 ADD CONSTRAINT CK_COLOR CHECK (COLOR IN ('RED','BLUE')); While this does what we want in restricting the values entered in the table, no one here is very happy with this solution. Using check constraints would seem to be rather inflexible and a maintenance burden on the DBA if the allowed values ever change. That is, if we want to allow PINK as a color, the check constraint will need to be recreated.

2. Small tables for each set of codes

The second option being discussed is to create a series of small tables for each list and then add a foreign key constraint that references those tables. So we would create tables such as:

CREATE TABLE SEX_CODES (CODE_VALUE VARCHAR2(1)); INSERT INTO SEX_CODES VALUES ('F');
INSERT INTO SEX_CODES VALUES ('M'); CREATE TABLE COLOR_CODES (CODE_VALUE VARCHAR2(5)); INSERT INTO COLOR_CODES VALUES ('RED');
INSERT INTO COLOR_CODES VALUES ('BLUE'); ALTER TABLE T1 ADD CONSTRAINT CK_COLOR_CODES FOREIGN KEY (COLOR) REFERENCES COLOR_CODES (CODE_VALUE) ALTER TABLE T1 ADD CONSTRAINT CK_SEX_CODES FOREIGN KEY (SEX) REFERENCES SEX_CODES (CODE_VALUE) This option has the definite advantage that adding new codes becomes just a matter of adding a new row the correct table. What some people don't like about this option is all the small tables that get created. We have these types of codes throughout our database and some people see all of these tables as unnecessary. They generally prefer the next option

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

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

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')); 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. 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. 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.

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

Thanks,
John Received on Wed Aug 15 2001 - 10:44:55 CDT