Multi-user Configuration Settings design

Question

I am designing a flexible and extensible way to store configuration settings for users.

Database Table Design:

╔═════════════════════════════╗ 
║ ConfigurationItemDefinition ║ 
╠═════════════════════════════╣ 
║ ID                          ║ 
║ Name                        ║ 
║ Type                        ║ 
║ DefaultValue (varchar)      ║ 
║ ConfigurationItemAdapterID  ║ 
╚═════════════════════════════╝ 
              |
╔═══════════════════════════════╗
║ ConfigurationItem             ║
╠═══════════════════════════════╣
║ ID                            ║
║ UserID                        ║
║ ConfigurationItemDefinitionID ║
║ Value (varchar)               ║
╚═══════════════════════════════╝

Explanation:

The ConfigurationItemDefinition table holds the definition for configuration settings. Type is the .NET type which the value would be parsed/serialized into when queried in the code.

The ConfigurationItem table is where configuration values are set for each user. If there is no value explicitly set for a specific user for some configuration item definition, then the DefaultValue value from ConfigurationItemDefinition is used.

ConfigurationItemAdapterID is determines what technique is to be used to 'get' and 'set' the configuration value. For example, an int value would use a basic Parse (from string -> int). A CultureInfo object would need to use a serialization adapter. The way in which configuration values are get and set is determined by the adapter (examples below).

C# Code

In C#, a setting would be acquired like this:

// This would use the Parse adapter
int timeout = someUser.GetConfigurationValue<int>("TimeoutValue"); 

// This would use the Serialization adapter
CultureInfo cultureInfo = someUser.GetConfigurationValue<CultureInfo>("CultureInformation");

And set like this:

// This would use the Parse adapter
someUser.SetConfigurationValue<bool>("IsLive", true);

Please critique this design. Are there more standard ways of dealing with such a problem?

Answer 1

That solution looks altogether workable to me.

One nitpick: I find that when I have to store different types of things in a uniform way, and there are only a small, non-expandable number of types that can exist, I'd rather have separate methods readInt(), readString() etc. and live with a small amount of duplication rather than specify the generic parameter on every access. At least, I would write "syntactic sugar" wrappers in that style. But that really is a question of what you consider more readable. Obviously, if there are an unbounded number of types that you have to support, the generic solution is the way to go.

Answer 2

I have built solutions like this in the past, but I would argue that the proposed design is actually a relational database anti-pattern since you store data without (from the database perspective) enforceable typing. You are basically using a relational database like a key/value store.

In my opinion using regular tables with regular columns set up with correct data types for each property is easier, more maintainable and plays to the advantages of the relational database you are using. If you feel that adding or changing columns/properties in the system is hassle to you, you may want to focus on improving that process instead which would be beneficial for the development of the entire system.

Drawbacks of your proposed solution, the way I see it:

• More complex
• More difficult to run ad-hoc queries like, what users have a time-out larger than 500?
• Not as extendable as you may think - how do you store a list or something more complex like relationships? Sure, you can solve that with your proposal but that will come with even more complexity.
• Likely to be less performant

As a final thought, if this is a great idea, why stop with with user configuration settings and not use this solution for all entities that are stored in the database?

Answer 3

Your solution seems fine.

But with the improving support of JSON in database systems (SQL and NoSQL), you could consider storing the user configuration in JSON. That is a often a good way for storing data the structure of which is not fix, but evolves dynamically.

For the default values (and the .NET types if still required), you would have one additional JSON record in the database.

However, the main downside of a JSON solution is that your code will stick to one database system since JSON support differs a lot.