Database Configuration, the XML Alternative, Page 2

Database Configuration

In our fictitious application, we use an Oracle database. As we have decided to store configurations in the database, one option is to define a new table. This, however, is usually overkill. There may be hundreds of screens to configure. We would unnecessarily pollute our schema with a great many tables containing very little information. A better choice is to use a 'codelist'. A codelist is a logical entity formed by storing name and value pairs in a set of tables. Let's take a look at how this works. In an Oracle database, the following script creates two tables named codelist and codelist_value:

CREATE TABLE codelist
(
  codelist_id    NUMBER           NOT NULL,
  name           VARCHAR2(30)     NOT NULL,
  description    VARCHAR2(200),
  active         CHAR(1)          NOT NULL,
  max_name       NUMBER,
  max_value      NUMBER,
  default_value  VARCHAR2(1000),
  created_on     DATE,
  created_by     VARCHAR2(30),
  updated_on     DATE,
  updated_by     VARCHAR2(30)
)
/
ALTER TABLE codelist 
 ADD CONSTRAINT pk_codelist PRIMARY KEY (codelist_id)
/
ALTER TABLE codelist
 ADD CONSTRAINT uk_codelist_id_name UNIQUE (codelist_id, name)
/

CREATE TABLE codelist_value
(
  codelist_value_id  NUMBER        NOT NULL,
  codelist_id        NUMBER,
  active             CHAR(1)       NOT NULL,
  name               VARCHAR2(30),
  value              VARCHAR2(1000),
  disp_seq           NUMBER,
  created_on         DATE,
  created_by         VARCHAR2(30),
  updated_on         DATE,
  updated_by         VARCHAR2(30)
)
/
ALTER TABLE codelist_value 
 ADD CONSTRAINT pk_codelist_value PRIMARY KEY (codelist_value_id)
/
ALTER TABLE codelist_value
 ADD CONSTRAINT fk_codelist_value_codelist FOREIGN KEY (codelist_id)
 REFERENCES codelist (codelist_id)
/ 

The codelist table contains entries for a specific configuration. Codelist_value contains the actual settings. Revisiting the salary history form, we will create a codelist entry for the interface's configuration settings.

INSERT INTO codelist
   (codelist_id,name,description,active,max_name,max_value,
    default_value,created_on,created_by,updated_on,updated_by)
   VALUES
   (1,'EMPLOYEE-SALARY-FORM','This codelist entry configures the
                              employee salary form.',
    'Y',30,30,'no value',SYSDATE,USER,SYSDATE,USER);

The XML file contained a parent element called employee-salary-form, so we've name this codelist entry 'EMPLOYEE-SALARY-FORM'. It specifies several other pieces of information, including the maximum length for values, a default value for these settings, and audit fields for the record. Once this record has been inserted, the detail entries follow. Here are a few of those entries:

INSERT INTO codelist_value
   (codelist_value_id,codelist_id,active,name,value,disp_seq,
    created_on,created_by,updated_on,updated_by)
   VALUES
   (1,1,'Y','TITLE','ABC Employee Salary History',1,
    SYSDATE,USER,SYSDATE,USER);
/
INSERT INTO codelist_value
   (codelist_value_id,codelist_id,active,name,value,disp_seq,
    created_on,created_by,updated_on,updated_by)
   VALUES
   (2,1,'Y','SHOW-DELETE-LINK','TRUE',2,SYSDATE,USER,SYSDATE,USER);
/

INSERT INTO codelist_value
   (codelist_value_id,codelist_id,active,name,value,disp_seq,
    created_on,created_by,updated_on,updated_by)
   VALUES
   (3,1,'Y','SHOW-EDIT-LINK','TRUE',3,SYSDATE,USER,SYSDATE,USER);
/

...

With these inserts, we assigned values to the title, show-delete-link, and show-edit-link configuration settings. The foreign key, codelist_id, on the codelist_value table is what associates these entries with their parent record in table codelist. Note the disp_seq column. This can be used to determine the order in which to display these fields in a form. Figure 2 displays one such form. No parameter value on this particular configuration screen may exceed 30 characters and, if no value is set, the text 'no value' is displayed for the field.

Figure 2. Configuring the Employee Salary History Form

Configure Salary History Form

---

TITLE:
SHOW-DELETE-LINK:
SHOW-EDIT-LINK:
SAL-SHOW-DELETE-LINK:
SAL-SHOW-EDIT-LINK:
MAX-ROWS-TO-DISPLAY:
DEFAULT-SORT-COLUMN:

---

The program will need to retrieve these settings and build the form appropriately. These values would probably be queried upon starting the application, and then cached for later use. Using XML configurations, you would need to write code to parse the document and store the results. With this data in the database, we simply execute a query in the same way we would query other application data. Below is a query to load configuration settings for this data entry screen. Note that you would probably want to change the hard coded name to a variable, and then reuse this query to obtain other configurations.

SELECT cv.name, cv.value
  FROM codelist c, codelist_value cv
  WHERE c.codelist_id = cv.codelist_id
  AND c.name = 'EMPLOYEE-SALARY-FORM'
  AND cv.active = 'Y'
  ORDER BY cv.disp_seq;

NAME                           VALUE
------------------------------ -------------------------------------
TITLE                          ABC Employee Salary History
SHOW-DELETE-LINK               TRUE
SHOW-EDIT-LINK                 TRUE