Database Configuration, the XML Alternative, Page 3
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.