Database table for global settings

Question

I am designing a database for a web application and would like to have a table for configurable global settings. Is there any established best practice for the design of this table? I can conceive of two possible solutions, and I'm sure there must be others.

The simplest would be just storing key/value pairs in there as strings. This would only take two columns, but would not give me any ability to add type constraints to individual items.

The other approach would be to add a column for every setting, but then the entire table would only have one row, and that feels a little odd.

How is this handled in most cases any of you have dealt with?

Answer 1

What we use at my current project is a System Paramaters table called SYSPARMS. This table is mainly a key/value table. It uses a VARCHAR for both the key and value. This allows us to specify the key in any form as well as the value (which really translates to the action).

I like using the DB because we can make real time adjustments that effect the applications. For example, we have a value in there we set when the system is down for maintenance so no users can access the the web app. We can at any time turn this off or on without having to deploy any apps.

Answer 2

The way settings are saved in merb (and probably rails) is as key/value pairs, but not in a database table, rather in a YAML file, which has the advantage of being both human-readable and easily parse-able.

Answer 3

The way that I like to do this if I am to do it in the database is to store attributes. An attribute has a key/value pair. But it also has an attribute type (which points to attributeTypes table) such as logging, siteConfig, externalReferences, ...what ever. Then you can also have a table at the AttributeType level that specifies the datatype that is to be held in the attributes table. DataTypeID would be stored with AttributeTypes. This then points to a DataTypes table for lookup reasons. This then allows you to know that you are working with numbers, dates, strings, xml, etc. I find that this offers the most flexibility...if you need it.

Answer 4

In ASP.Net you can store global configuration values in the Web.Config file in Name/Value pairs.

Answer 5

Zend Framework uses a config.ini and has a great parser that splits and forks the keys into arrays and subarrays depending on dot notation. The config.ini can be loaded as an instance and stored in the registry.

example:

db.name 'myname'

db.host 'myhost'

db.user 'myuser'

db.password 'mypassword'

I can now get these values like:

$config->db->name; or the full array $config->db;