The best way to manage configuration data

Question

I am working on a SaaS application, where each client will have different configurations depending on the version they purchased, additional features that they acquired, etc. For example, a client may have a limit of 3 custom reports.

Obviously, I want to save this configuration to the database, but I'm not sure about the best approach. We want to be able to add additional functions in the future, without requiring changing the database schema, so one table with a column parameter for each configuration does not make sense.

Possible parameters are a table with one record for each client with an XML field containing the entire configuration for this client, but this adds complexity when changing the XML schema to add additional functions.

We could use a table with pairs of key values ​​and save all configuration parameters as strings, and then analyze them with the correct data type, but this seems a bit cluttered, and also has a separate table for row configuration parameters, integer configuration parameters, etc. .

Is there a good template for this type of scenario that people use?

Answer 1

I think it will 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 have thought it would be easier to set up a table of these packages and a link to it.

If you sell each module yourself with the changes ...

Customer wants 4 reports a week with an additional report every other tuesday if it 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.

CLIENTS

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?)

It matters to me the most.

Answer 2

, . , , .

(RBAC). Google - .

Answer 3

SQL Server 2005+, / SQLVARIANT - , .

, .

Answer 4

? , , . , ?

Changing the design is something that you must be able to tolerate, implement in the development, testing and release process and use design changes in the future.

Scheme changes occur; get used to it :)

Answer 5

A table of value key pairs, but with everything saved as a row and with another column (if necessary), which indicates which type should be assigned.

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

If a value cannot be assigned to a type, you know that this is the wrong configuration and there is no ambiguity.