One of the things people keep reinventing in all kinds of information systems is settings management. There are a number of ways to go about it, which is what probably confuses developers: settings can go into simple property files, XML/YAML files, structured/typed DB tables and general purpose key-value DB tables, among others.
Each of these is best under specific circumstances and it is worth looking at how things are done in really successful software.
Java uses simple property files which get packaged into .jars and are typically used as read-only mappings which are quick and simple to process and easy to understand. Providing GUI element translations in such files is a fairly sensible use of .properties files. Because no assumptions can be made about what the code does at the language level, using a database is out of the question.
Log4j used to be configured using properties files, but property files being basically maps, this was too strict a constraint to represent the richness of concepts the log4j developers wanted people to be able to express. They solved the problem by switching to XML with which they retained the immediate access to the content, sufficient readability (mostly due to limited document size) and reasonably simple processing. Again, there is no such thing as a log4j database to keep settings in so a database table is not an option.
Having said all this, it is very rare that people develop software for the x86 platform without using some kind of database. Even applications which don’t typically look like they would know of or care about databases use them extensively: Internet browsers, image collection management tools, music players, e-mail clients – more often than not, they all use fairly sophisticated databases under the hood, away from users’ eyes. This is especially true for enterprise information systems where the database is the most valuable part of the system because it contains the data. In my experience, most software developers work on such systems which makes enough room for a default best approach for settings management.
In systems built on top of a database, a single table should be used to keep all settings. Developers learn (some more quickly than others) that creating new columns for new settings is far from ideal: there is no practical way to group related settings (column names are not very flexible), sometimes there is a limited number of available columns so limited information can be stored, the application has to be recompiled etc.
Settings should therefore be kept in a database table of the following structure:
- id: int
- key: varchar
- value: varchar
- user_id: int
Each row in such a table defines a specific property called “key” and its “value”. The middle columns are the most important ones and can be configured as large varchars (e.g. 8k chars) to allow for a significant amount of information. The id may or may not be needed, but might be useful e.g. when an ORM is used to access the database. Finally, a user_id or similar field can be added to make it possible to manage per-user configuration, if any: the value is null for global settings and is set to a user ID when it is a user property.
These are the effects of such an approach:
- read-write instead of read-only access: an interface can be built to allow users to change settings easily
- real time updates: configuration files are almost always not re-read until the application is restarted
- simpler maintenance: settings are treated like any other data and accessed through the same interfaces
- archiving: settings get archived and/or restored together with the rest of the database, which is as it should be because they represent a part of the global system state
- code reuse: settings management code can basically be reused with no changes between systems in a single technological environment (e.g. Java and Hibernate)
In short, if no strong reason comes to mind against a general purpose settings table, this should probably be the default approach to settings management.