Better database table design

Question

I need to store a series of configuration values in a database. A couple ways I thought of to store them are: a table with 2 colums(name,value) and a row for each pair, or a table with a column for each config parameter and 1 row? With the first I only need to add another row to add a config value, with the second I need to add a column to the table. Are there any issues with either I should take in to consideration? Is one more efficient than the other?

Answer 1

I think the 2-row (name, value) design is much better. As you said, if you need to add a new property, all you need to do is to "insert" a new row. While in the other design (single-row), you'll need to change the table schema to add a column for the new property.

This, however, depends on whether your list of properties are going to change in the future.

Answer 2

CREATE TABLE Configuration (
    Name ...,
    Value ...,
);

The best way. Adding a column to a table usually sucks, and what's the point of a table with one row?

Not sure this is appropriate for SQL, but alas...question answered.

Answer 3

The first issue you should consider is this: stop thinking about the efficiency of retrieving the information. first and foremost, figure out how to effectively and correctly model the data and then (and only then) figure out how to do it efficiently.

So it depends on the nature of the config data you're storing. If separate (name,value) pairs are basically unrelated then store it as one per row. If they are related then you may want to consider a scheme that has multiple columns.

What do I mean by related? Consider some cache config. Each cache has several attributes:

• eviction policy;
• expiry time;
• maximum size.

Assume each cache has a name. You could store this data as three rows:

• <name>_EVICTION
• <name>_EXPIRY
• <name>_MAX_SIZE

but this data is related and you may often need to retrieve them all at once. In that case it may make sense to have a cache_config table with five columns: id, name, eviction, expiry, max_size.

That's what I mean by related data.

Answer 4

I have used both methods and I prefer the 2 column method. The draw back to the a new column for each configuration is that you need to change code to add new settings.

I do prefer to use the One column per setting method (when I am accessing the value). This is because the configuration settings are more explicitly set. But that that preference does not out weigh the difficulty of adding a new configuration to the table.

I would recommend the 2 column method. Then setup an accessor function/sproc to get at the values.

Answer 5

One more consideration : with a column for each config parameter, you can easily have versions. Each row represents a version.

Answer 6

For config data, I'd use the key/value structure with a row per configuration entry. You're likely to read this data once and cache it, so performance isn't an issue. As you point out, adding columns each time the set of config keys changes requires a lot more maintenance.

SQL excels at modeling and manipulating arbitrarily large sets of similarly (if not the same) structured data. A set of configuration information really isn't that -- you've got a single row of data OR you've got multiple rows of completely unrelated data. That says you're just using this as a data store. I say skip the SQL data model and go simple.

Answer 7

depends.

If you have less than say 15 values, I'd make a column for each.

If you change the number of settings regularly, or if you often don't use all of the settings, I'd consider making a row per setting.

Beyond that, it's probably a tossup. Depends on your usage patterns. If you always need to grab all the settings, it's probably quickest to have them in one row.

Adding columns isn't too hard, and if you program sensibly, you usually don't have to update any of your other code.

Answer 8

You can save configuration efficiently using XML. Some Database support Pure XML feature in which you can save value as xml data type and you can run XQUERY on that particular column.

Create a table with two column name and configuration. name with string datatype and configuration with xml data type so no need to worry about insertion and deletion of new configuration parameters, you will just a new tag in xml. And if database does not support XML then just save it as a string but in XML format so you can parse that configuration manually or using some API efficiently.

I think this would be better way instead of storing complete configuration as a string.

Answer 9

Here I blog about when we moved our AppSettings to a Database Table. The performance isn't an issue because it is pull only once at the start of the application and stored in a dictionary for easy lookup.

Not sure about your application, but the important reason why we did this is now it is impossible to be using the Production values if you are in Dev, Test, etc.