How do you structure config data in a database?

Question

What is people's prefered method of storing application configuration data in a database. From having done this in the past myself, I've utilised two ways of doing it.

1. You can create a table where you store key/value pairs, where key is the name of the config option and value is its value. Pro's of this is adding new values is easy and you can use the same routines to set/get data. Downsides are you have untyped data as the value.
2. Alternatively, you can hardcode a configuration table, with each column being the name of the value and its datatype. The downside to this is more maintenance setting up new values, but it allows you to have typed data.

Having used both, my preferences lie with the first option as its quicker to set things up, however its also riskier and can reduce performance (slightly) when looking up data. Does anyone have any alternative methods?

Update

It's necessary to store the information in a database because as noted below, there may be multiple instances of the program that require configuring the same way, as well as stored procedures potentially using the same values.

Answer 1

You can expand option 1 to have a 3rd column, giving a data-type. Your application can than use this data-type column to cast the value.

But yeah, I would go with option 1, if config files are not an option. Another advantage of option 1 is you can read it into a Dictionary object (or equivalent) for use in your application really easily.

Answer 2

Since configuration typically can be stored in a text file, the string data type should be more than enough to store the configuration values. If you're using a managed language, it's the code that knows what the data type should be, not the database.

More importantly, consider these things with configuration:

• Hierarchy: Obviously, configuration will benefit from a hierarchy
• Versioning: Consider the benefit of being able to roll back to the configuration that was in effect at a certain date.
• Distribution: Some time, it might be nice to be able to cluster an application. Some properties should probably be local to each node in a cluster.
• Documentation: Depending on if you have a web tool or something, it is probably nice to store the documentation about a property close to the code that uses it. (Code annotations is very nice for this.)
• Notification: How is the code going to know that a change has been made somewhere in the configuration repository?

Personally, i like an inverted way of handling configuration, where the configuration properties is injected into the modules which don't know where the values came from. This way, the configuration management system can be very complex or very simple depending on your (current) needs.