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 kinda funky.

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

Answer 1

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 2

If there's only going to be one row in the table, then why use a database? Store the configuration, in XML, in a file.

If you still need to use a database, then store the configuration, in XML, in a column in the one row of the one table of the database.

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

I like the YAML approach that STAii advocates.

If this a .NET or Java application you can also consider XML and use serialization to get at instances of your user settings conveniently.

Answer 6

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 specificy 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 7

If your using Visual Studio 2008, the database is called ASPNETDB . MY VERSION OF instructions on setting it up are in my blog at: http://codingathome.blogspot.com/2009/04/how-to-change-location-of-aspnetdb.html

If its PHP then I'd use http://www.c-worker.ch/txtdbapi/manual_html_eng OR SQLLite for your database instead of a "socket listening database"

Answer 8

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;