Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
    
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 '12 at 20:59
    
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 '13 at 16:15
    
Approach 3: Single Column/Single Row with a simple data interchange format like JSON or YAML. Combines advantages from both approaches. –  schlamar Sep 27 '13 at 6:49

5 Answers 5

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

ConfigOption   |   textValue    |   DateValue   |   NumericValue

Option 1 Has the addtioal Benifit that you can very easly "Swap" entire Configurations by adding an Active Column.

share|improve this answer
    
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 '12 at 17:06
    
@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 '12 at 17:21
3  
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 '12 at 20:39
1  
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 '12 at 4:00

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.

share|improve this answer

For me, whether you go single-row or EAV 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.

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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