Configuration data: single-row table vs. name-value-pair table
- by Heinzi
Let's say you write an application that can be configured by the user. For storing this "configuration data" into a database, two patterns are commonly used.
The single-row table
CompanyName | StartFullScreen | RefreshSeconds | ...
---------------+-------------------+------------------+--------
ACME Inc. | true | 20 | ...
The name-value-pair table
ConfigOption | Value
-----------------+-------------
CompanyName | ACME Inc.
StartFullScreen | true (or 1, or Y, ...)
RefreshSeconds | 20
... | ...
I've seen both options in the wild, and both have obvious advantages and disadvantages, for example:
The single-row tables limits the number of configuration options you can have (since the number of columns in a row is usually limited). Every additional configuration option requires a DB schema change.
In a name-value-pair table everything is "stringly typed" (you have to encode/decode your Boolean/Date/etc. parameters).
(many more)
Is there some consensus within the development community about which option is preferable?
