Best table design for application configuration or application option settings?

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.

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)}

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.