78

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.

  1. The single-row table

      CompanyName  |  StartFullScreen  |  RefreshSeconds  |  ...
---------------+-------------------+------------------+--------
  ACME Inc.    |        true       |       20         |  ...

  2. 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?

Improve this question
6
  • 2
    There's no reason the 'vertical' approach cant have different data types. Add an int, float, and text column per row. Save/load values from it using type-specific functions, such as 'SaveConfigInt( 'field', n)'
    – GrandmasterB
    Sep 4, 2012 at 20:59
  • 4
    There is an excellent StackOverflow question asking about this, and the top answer gives pros and cons to both approaches. stackoverflow.com/questions/2300356/…
    – Kevin
    Aug 2, 2013 at 16:15
  • 1
    Approach 3: Single Column/Single Row with a simple data interchange format like JSON or YAML. Combines advantages from both approaches.
    – schlamar
    Sep 27, 2013 at 6:49
  • what about using single-row table with complex data containing xml/json such as <config><CompanyName>ACME Inc.</CompanyName><StartFullScreen>true </StartFullScreen>20<RefreshSeconds></RefreshSeconds></config> and validate object in business layer?
    – John
    Feb 26, 2016 at 22:37
  • 2
    @John: Good idea, if hierarchical structures are needed. If they aren't, it's just option 2 with added complexity.
    – Heinzi
    Feb 28, 2016 at 17:46

7 Answers 7

Reset to default
22

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.

Improve this answer
2
  • What if the data to be stored is user-defined ? i.e. think of an UI where user can create a "field" by specifying the field label, the type of data it will hold etc.. That would mean executing DDL statements from code. Would you still go with option 1 ?
    – devanalyst
    Oct 1, 2019 at 19:39
  • 1
    @devanalyst No, if the data could change from component to component, then it wouldn't make sense to attempt to create a static table to represent it. In that case it would be better to use the second option.
    – Neil
    Oct 3, 2019 at 7:00
12

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.

Improve this answer
4
  • If you're going to allow configurations to be disabled (for option 1), at least make it an activatedOn timestamp, so you can tell when it was activated. And if you're going with option 2... what happens if the end up storing values in multiple columns (or it's oracle, where (apparently) null and an empty string are equivalent)?
    – Clockwork-Muse
    Sep 4, 2012 at 17:06
  • 1
    @X-Zero, storing Multiple Configs is usually done for testing purposes, but a time stamp cant hurt. The Config Maintenance, call to get the value would know what column to check, if you really wanted to, you could add a column for data type.. But i think that is over kill...
    – Morons
    Sep 4, 2012 at 17:21
  • 5
    an EATV (Entity-Attribute-Type-Value) schema breaks third normal form; the Type column is only indirectly related to the primary key of the table, through the Value column which the Type column describes. In addition, dynamic type storage and instantiation doesn't solve much; if a GetConfigValue() method can return any type, it must return Object (or be given the expected type somehow) which must still be evaluated at runtime.
    – KeithS
    Sep 4, 2012 at 20:39
  • 7
    Everytime option 1 has been implemented in software I've seen, it had to be converted to option 2. Option 2 is easier to maintain over time, just takes a touch more time to implement correctly the first time. Option 1 is quick and easy to implement but maintenance over time is horrible unless your software is tiny with no chance of growing.
    – Jimmy Hoffa
    Sep 5, 2012 at 4:00
12

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.

Improve this answer
4

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.

Improve this answer
2

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.

Improve this answer
1

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.

Improve this answer
1

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.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.