Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Database design issues

Re: Database design issues

From: John Darrah <john.darrah_at_usa.net>
Date: 17 Aug 2001 08:21:59 -0700
Message-ID: <4af5a619.0108170721.102abb65@posting.google.com>


My 2 cents. Option 3 doesn't buy you anything. There is the problem with the foriegn key relationships you mentioned. Also, you penalize all of your type lookups by sticking them in one big table. Say you have type of color with 200,000 different colors in the same table with sex code. Even if you stick an index on listname, your going to have to rely on the optimizer to choose the index on sex code and the full scan on color in order for your lookups to preform well. The second part of this is that you don't need an all in one solution.  A lookup like sex code is a perfect canidate for a check constraint because unless something really weird comes up you will have 3 sex codes (male,female,unknown) for now and into the forseeable future.

"John" <jhook_at_regenstrief.org> wrote in message news:<9le4ai$bbi$1_at_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:
>
> 1. 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 Fri Aug 17 2001 - 10:21:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US