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
Configure Salary History Form
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.
- 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.
- 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.
- 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.
- 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.
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.