Best table design for application configuration or application option settings?

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

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 2

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

---

^* of a complete parameter set ^{(as pointed out in a comment by granadaCoder)}