Re: variable column table

From: Herr Otto Partz <cris119_at_operamail.com>
Date: Wed, 28 May 2003 10:38:11 +0200
Message-ID: <bb1shk$h4l$1@panco.nettuno.it>

Billy Verreynne wrote:

> In essense, such a db design calls for table with variable columns.. (for a
> lack of a better description)
>
> Any comments, ideas or suggestions on how to tackle this in Oracle?

One of our partners used a sort of "matrix table" for chemical analysys (simplified):

CREATE TABLE PARAMETERS_DICTIONARY (
   PARAMETER_ID VARCHAR2(20) PRIMARY KEY,    DESCRIPTION VARCHAR2(255) NOT NULL,
   [... other metadata here ... ]
) ;

CREATE TABLE CHEMICAL_ANALYSIS (
   ANALYSIS_DATE DATE,
   PARAMETER_ID VARCHAR2(20) REFERENCES PARAMETERS_DICTIONARY (PARAMETER_ID),
   PRIMARY KEY (ANALYSIS_DATE, PARAMETER_ID),    VALUE VARCHAR2(255) NOT NULL
) ;

I don't like this approach for two reasons: (1) producing reports is a nightmare and (2) you lose the Oracle datatype by using a unique VARCHAR2(255)column for all parameters values.

Here's the approach I used in one of my projects that had similar requirements. First of all, I designed a parameter dictionary:

CREATE TABLE PARAMETERS_DICTIONARY (
   PARAMETER_ID VARCHAR2(20) PRIMARY KEY,    COLUMN_NAME VARCHAR2(30) UNIQUE,
   DESCRIPTION VARCHAR2(255) NOT NULL,
   [... other metadata here ... ]
) ;

INSERT INTO PARAMETERS_DICTIONARY VALUES ('FOO','AA','The Foo Parameter') ;
INSERT INTO PARAMETERS_DICTIONARY VALUES ('BAR','AB','The Bar Parameter') ;
INSERT INTO PARAMETERS_DICTIONARY VALUES ('STUFF','CA','Other 

Interesting Stuff') ;

Then I designed a chemical analysis table with columns named "the Excel way":

CREATE TABLE CHEMICAL_ANALYSIS

       (ANALYSIS_DATE DATE PRIMARY KEY,
        AA          NUMBER,
        AB          NUMBER,
        AC          NUMBER,
        [...]
        BA          VARCHAR2(255),
        BB          VARCHAR2(255),
        BC          VARCHAR2(255),
        [...]
        CA          INTERVAL DAY TO SECOND,
        CB          INTERVAL DAY TO SECOND,
        CC          INTERVAL DAY TO SECOND,
        [... up to 200 or more columns named using this schema ... ]

) ;

This design give to you the desired flexibility (you can add parameters simply by describing more columns in the dictionary) without the SELECT nighmares associated to the "matrix table". Moreover, it allows you to use different Oracle datatypes for different parameters.

-- 
Cris aka H8P
"Ubi solitudidem faciunt, pacem appellant"
"Dove fanno un deserto, lo chiamano pace"
"Where they make a desert, they call it peace" (Tacito, "Agricola")

Received on Wed May 28 2003 - 03:38:11 CDT