I personally prefer the single-row tables for most things. While it’s true that it is less flexible, unless you are expecting dynamic behavior, it’s perfectly acceptable to add extra columns later if you need to. In a way, it’s the equivalent of using a dictionary/map to hold name-value pairs vs having class members when programming. Granted, it’s not a perfect metaphor, but many of the advantages and disadvantages are paralleled when you think about it.

So would you use a dictionary/map over class members? Probably not unless you had reason to think the amount of data to be represented is entirely adaptable, much like having a name-value pair table.

I’d generally go with option 2 BUT I’d have multiple columns to enforce data type

ConfigOption   |   textValue    |   DateValue   |   NumericValue

Option 1 Has the additional Benefit that you can very easily “Swap” entire Configurations by adding an Active Column.

For me, whether you go single-row or EAV (Entity-Attribute-Value) depends on how you want to consume them.

EAV’s power is that new data can be added with no change to structure. This means that if you want a new configuration value, you just add it to the table and pull it out where you want it in code, and you don’t need to add a new field to the domain, schema, mapping, DAL queries, etc.

Its flaw is that it has only the barest structure, requiring you to deal with the data pessimistically. Every usage of any configuration value must expect the value to not be present, or not in the proper format, and behave accordingly when it isn’t. A config value may not be parsable to a double, or an int, or a char. It may be null. there may be no row for the value at all. The ways around this usually require a single valid “default” value to exist for all config values of a particular in-code type (extremely rare; more often the default value is just as problematic for consuming code as none at all), or to keep a hardcoded dictionary of default values (which must change every time a new column is added, making the primary advantage of EAV storage pretty moot).

A single wide row is pretty much the opposite. You map it to a single instance of a Configuration object with a field/property for every configuration value in existence. You know exactly what type those values should be at compile time, and you “fail fast” in the DAL if a config column doesn’t exist or doesn’t have a value of the proper type, giving you one place to catch exceptions based on configuration retrieval/hydration problems.

The main disadvantage is that a structural change is required for each new value; new DB column, new column in the DAL (either the mapping or the SQL queries/SPs), new domain column, all necessary to properly test usage.

The proper situation in which to use either of these is the situation in which the disadvantages are mitigated. For me, most situations for config coding have called for a single-row implementation. This is mainly because if you’re introducing an entirely new configuration value that governs behavior of some part of your program, you already have to change the code to use the new configuration value; why not pop over to the config object and add the value to be used?

In short, an EAV schema to store configuration really doesn’t solve the problem it purports to solve, and most of the workarounds to the problems it presents violate DRY.

Specifically for configuration values, I’d say – go with the single row. Unless you’re currently going through development, how often are those columns going to change anyway?

It’s probably best to secure the datatype of the values, rather than code for extensibility you’re not likely to have in the downtime between large(r) releases. Besides, adding or removing a single column is just about the easiest migration there is. I don’t forsee a headache when creating a new configuration option.

Additionally, you said “users” can configure these options, without giving a cap. Are they per-user configurations? If so, I’ll argue even more strongly that the configuration options should be in the columns – a single row per user. It’ll save a lot of maintenance headaches later on.

If your clients can process JSON fragments (that is not only arrays and dictionaries, but also plain strings, numbers, booleans, null values) then you can have a multi-row table with option name and a string value containing JSON. That allows you to also store structured values, and the code for processing these should already be there.

If your clients can’t process JSON fragments, get new clients.

Single row
Pros : Well defined.
Cons : Changing the configuration can be a pain. DB migrations etc..

Entity-Value
Pros : Super flexible, supports evolving your config.
Cons : Referential integrity ? More checks in your code to see if the property exists before you can do anything on it.

I would take approach 2 backed by a non-relational db like Mongo. If there is something you can be sure of, its change.

Use both!

Sort out what options can have multiple instances, and what options are generic.

The single-row table (configurations)

  id  |  company_name  |  start_fullscreen  |  refresh_seconds  |  ...
------+----------------+--------------------+-------------------+-------
  4   |  ACME Inc.     |  true              |  20               |  ...

The name-value-pair table (options)

  name             |  value          | update_time  
-------------------+-----------------+--------------
  generic_option_1 |  Option 1 Value | timestamp    
  generic_option_2 |  Option 2 Value | timestamp    
  generic_option_3 |  Option 3 Value | timestamp    
  configuration    |  4              | timestamp    
  ...              |  ...            | ...          

I think this is more flexible.