Best Way to Manage Configuration Data

Question

I'm working on a SaaS application where each customer will have different configurations depending on the edition they have purchased, additional features they have purchased, etc. For example, a customer might have a limit of 3 custom reports.

Obviously I want to store this configuration in the database, but I am unsure of the best approach. We want to be able to add additional features in the future without requiring a change to the database schema, so a single table with a column per configuration option isn't sensible.

Possible options are a table with one entry per customer, with an XML field containing the entire configuration for that customer, but that adds complexity when the XML schema changes to add additional features.

We could use a table with key value pairs, and store all configuration settings as strings and then parse to the correct data type, but that seems a bit of a cludge, as does having a seperate table for string config options, integer config options, etc.

Is there a good pattern for this type of scenario which people are using?

Answer 1

The key value pair table, but with everything is stored as a string and with another column (if necessary) saying which type should the value be casted to.

CREATE TABLE configKVP(clientId int, key varchar, value varchar, type varchar)

If the value cannot be casted to the type, then you know it's a misconfiguration and there's no ambiguity.

Answer 2

If your database is SQL Server 2005+, your key / value table can use the SQLVARIANT data type for the value field - with a third column to store the data type you need to cast it to for use.

That way you can literally insert numbers & text values of varying sizes into the same field.

Ron

Answer 3

I think this would depend on how your product was sold to the customer.

If you only sell it in packages...

PACKAGE 1 -> 3 reports, date entry, some other stuff.
PACKAGE 2 -> 6 reports, more stuff
PACKAGE 3 -> 12 reports, almost all the stuff
UBER PACKAGE -> everything

I would think it would be easier to setup a table of those packages and link to that.

If you sell each module by itself with variations...

Customer wants 4 reports a week with an additional report every other tuesday if it's a full moon.

Then I would --

Create a table with all the product features.
Create a link table for customers and the features they want.
In that link table add an additional field for modification if needed.

CUSTOMERS

customer_id (pk)

MODULES

module_id (pk)
module_name (reports!)

CUSTOMER_MODULES

module_id (pk) (fk -> modules)
customer_id (pk) (fk -> customers)
customization (configuration file or somesuch?)

This makes the most sense to me.

Answer 4

Actually, I don't see the need for different configurations here. What you need is authorization levels and proper user interface not to show the functions the user hasn't paid for.

A good authorization data model for such application would be Role Based Access Control (RBAC). Google is your friend.

Answer 5

Why are you so afraid of schema change? When you change your application, you will doubtless require additional configuration data. This will entail other schema changes, so why be afraid?

Schema change is something that you should be able to tolerate, incorporate into your development, testing and release process, and make use of in design changes in the future.

Schema changes happen; get used to it :)