http://www.developer.com/

Back to article

Database Configuration, the XML Alternative


September 27, 2004

When creating software, it is a wise decision to separate business logic from information used to configure that software. Over the years, developers have used a number of techniques to externalize configuration. The goal is to make it possible to customize the functionality of a program without changing code. Today, a majority of business applications use a relational database to store data. This article will look at some common ways to store configuration data. Then, it will demonstrate how to use a database to accomplish this task.

The XML Craze

I was inspired to mention XML (Extensible Markup Language) in this article's title because XML seems to have become the configuration 'language' of choice for many software developers. XML was created to exchange data, in a platform-neutral way, between disparate systems. However, XML's use is not strictly limited to data exchange. For example, though small in number when compared to database-driven programs, some applications use XML as a primary storage medium. Another way in which XML can be used is of direct relevance to our topic at hand: software configuration.

In the world of J2EE, let's look briefly at how XML is used for configuration. Applications built with Java Servlets use a web.xml file to wire various software components together. In its most basic form, a web.xml looks like this:

<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE web-app
   PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN"
   "http://java.sun.com/dtd/web-app_2_3.dtd">

<web-app>
   <display-name>My Web App</display-name>
   <description>Here is my web app.</description>
</web-app>

A Servlet Container validates against a DTD (Document Type Definition), and then parses web.xml upon application startup. To define a specific servlet for use in your program, you would add the following:

   <servlet>
      <servlet-name>MyServlet</servlet-name>
      <servlet-class>MyServlet</servlet-class>
      <load-on-startup>1</load-on-startup>
   </servlet>

   <servlet-mapping>
      <servlet-name>MyServlet</servlet-name>
      <url-pattern>/MyServlet</url-pattern>
   </servlet-mapping>

Many popular frameworks are available for J2EE applications. One of the most popular is Apache Struts. In addition to the web.xml file, Struts requires you create a struts-config.xml file. Struts-config.xml maps URLs to Java classes that will process user requests. Using XML files is a good choice here. Configuration data is often hierarchical in nature and the value of an element may be dependent on its context within the larger document. Defining nested relationships is difficult to do using a properties file, but XML does the job quite well. Also, just because you are building a Struts-based, J2EE application, for example, does not mean you will use a database to store program data.

But what if the application does use a database, as an overwhelming number of them do? Are XML files the best way to store all types of configuration? This seems to be the preferred approach for many developers.

Consider a scenario where one might use XML to configure a user interface. In reality, user interface customization is only one of many ways to make use of external configurations, but we will use it as an example. In a fictitious HR system, there is a customizable interface that allows users to view and potentially modify (yikes!) employee salary history. Interface settings are defined in a file called emp_sal.xml. Here is a snippet of that file:

<employee-salary-form title="ABC Employee Salary History"
        show-delete-link="false" show-edit-link="false" >

   <salary-history-table show-edit-link="true"
                         show-delete-link="true"
                         max-rows-to-display="5"
                         default-sort- column="Entry Date"/>

</employee-salary-form>

And, if you were to use a properties file for this:

employee-salary-form.title=ABC Employee Salary History
employee-salary-form.show-delete-link=false
employee-salary-form.show-edit-link=false
employee-salary-form.salary-history.table-show-edit-link=true
employee-salary-form.salary-history-table.show-delete-link=true
employee-salary-form.salary-history-table.max-rows-to-display=5
employee-salary-form.salary-history-table.default-sort-column=Entry Date

Figure 1 shows what this interface might look like. It displays a title, employee ID and name, and a table with records containing salary history. Each detail record contains links to edit or delete this data. The form can be configured to display n number of rows with a default sort column. Because the salary-history-table element is a child of employee-salary-form, specifying such information with XML is more appealing than using a properties file, which is harder to read. To customize this form, the user could modify the XML file by hand. However, this approach can be prone to error. More likely, the user will be shown a configuration screen. When saved, the operating system file is updated.

Figure 1. Employee Salary History Form

ABC Employee Salary History


ID: 4949448Name: John Eads


SalaryEntry Date 
$55,00010/23/2004
$51,00010/22/2003
$48,00010/20/2002
$42,50009/30/2001
$40,00009/17/2000



There is nothing inherently wrong with using XML to configure this interface. However, in this situation we have another option. More than likely, this system's data resides in a database. So, let's look at how we can use a database to configure the application.

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

Database Configuration—Drawbacks, Benefits, and Considerations

Database configuration has both its supporters and its critics. I will first attempt to address the reasons some developers give for not storing this information in a database. Then, I will list the benefits of this approach, followed by a list of things to consider when using database configuration.

Drawbacks. Database data is typically relational in nature. Tables are designed in such a way as to eliminate redundancy and avoid data inconsistencies. This process is called normalization. Configuration data is usually not relational in nature. Therefore, the argument is made that a database is not suited to house this information. However, just because a database typically stores relational data, there is nothing that dictates that it can contain only relational data. First, configuration data, unlike application data, is likely to be fairly limited. Moving configurations into the database is not likely to add any significant overhead. Secondly, databases are a proven entity when it comes to storing and retrieving mission-critical data and you can be sure that your configurations are in safe keeping.

A second criticism is that putting configuration data in the database makes it more difficult to modify without using the application's interface. For starters, codelist and codelist_value are tables. A user with the appropriate permissions can issue SQL statements to modify settings. Also, although it may be convenient for a developer to modify a framework's XML files by hand, it is less likely that an end user will need to do this. Some software applications like to provide this option to users. However, allowing end users to modify configurations by hand can be error-prone. It is debatable whether you want to provide them with this functionality at all.

Benefits. There are numerous benefits to letting your database store configurations. As was previously mentioned, databases are a trusted, secure way to store any type of data. If your application is using a database, and it is unlikely that it will remove its dependency on that database, make good use of it. It is a powerful tool. You will have the option of defining a richer set of security requirements for your configurations. Also, if necessary, you can easily report on them using SQL.

Using codelist tables, an application can reduce the number of tables it requires. As mentioned earlier, it is possible to store each configuration in a separate table. Certainly, there are times when this makes sense. However, using codelist tables can help to reduce schema bloat. Consider an application with a table called 'status'. In this table, status codes are defined along with their textual representation ('A', 'Active', 'R', 'Retired', and so forth). Depending on how status is used, it might be possible to store this data in a codelist, eliminating the need for a separate table.

Considerations. Concluding this discussion, I've included some helpful tips when putting configuration data in the database.

  1. Keep it simple. If you require more than 2 or 3 attributes (fields) to define a configuration, consider creating a new table. In the status example described above, we needed to store only a code and text, so using a codelist seems appropriate. As requirements have a tendency to change, try to be as forward thinking as possible. Of course, in a well-designed application (for example, using the Data Access Object pattern in J2EE), switching from a codelist query to a table query should require only minimal code changes.
  2. Avoid storing relational data. In the salary history form example, the screen settings applied to all users of the system. If, on the other hand, you were storing user preferences, use a table. An SQL join would be required to associate preferences to a particular user ID. If you used a codelist, not only would you create a large number of records, you would need to store user id in the codelist. It would not be possible to create a foreign key constraint for only one codelist as a constraint on the codelist table would apply to all codelist entries. Codelists do not accomodate data requiring key constraints.
  3. Consider user access. Will users have the ability to modify values without using the application's front end? If so, consider the error-handling necessary to allow a user to safely update information through SQL. This goes back to the potential drawback of allowing users to manually edit XML files. Direct manipulation of the codelist tables can be easily prevented by managing database privileges.
  4. Manage complexity. In effect, each codelist and its corresponding codelist_value records form a virtual, masterdetail set of tables. Sometimes, an SQL query might require multiple joins against the same codelist tables, creating some rather confusing queries. In certain situations, it might make sense to create a database view for codelist data. Creating views should, however, not be overused as it could result in a large number of schema objects to manage—something we wanted to avoid in the first place.

Conclusion

This article demonstrated how to effectively store software configurations in a database. Database configuration can be a robust alternative to the many common approaches in use today.

About the Author

Michael Klaene is a Senior Consultant with Sogeti LLC. He has spent over 9 years in Information Technology and is an experienced Systems Analyst, delivering solutions that involve numerous technologies, such as J2EE and .NET.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date