Best Table Design for Application Configuration or Application Option Settings

Best table design for application configuration or application option settings?

For config data, I'd use the key/value structure with a row per configuration entry. You're likely to read this data once and cache it, so performance isn't an issue. As you point out, adding columns each time the set of config keys changes requires a lot more maintenance.

SQL excels at modeling and manipulating arbitrarily large sets of similarly (if not the same) structured data. A set of configuration information really isn't that -- you've got a single row of data OR you've got multiple rows of completely unrelated data. That says you're just using this as a data store. I say skip the SQL data model and go simple.

How to design application settings table in database

Use the second method. Obviously it is far more scalable. You will never need to add additional columns when you need to add more options.

I would only advocate the first method if the options are very limited and fixed, and all have different data types. That's an area where the two differ considerably - if you have a big mix of numeric and character columns, you have really no choice with the second option but to store them all as VARCHAR. However, for a settings table that will have a very limited number of rows and won't be subject to a lot of INSERT and UPDATE, that probably isn't a big issue.

You would not want to use the second method for a regular table (not storing mostly static application settings) that needs to be highly accessible, or used for calculations for example, where you would constantly need to be type casting values to manipulate them.

For static data infrequently accessed or modified, the second method works well though.

Best Way to Manage Configuration Data

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.

Database design - configuration/settings table

I've found that I like working with a single table for this sort of scenario. Its especially useful when you have some common base fields that all the types will share. ORM frameworks such as the Entity Framework will allow you to split a single table into multiple entities and define the inheritance relationships.

Table Design For SystemSettings, Best Model

This is a variation of an "Entity Attribute Value" schema (Joel and random SO question)

It has a few pros and more cons, and it pretty much guaranteed to end in tears.

is it better to store platform configuration in database or a file?

It really depends on your application.

Storing the settings in the database has several advantages:

1. Security - users can easily alter settings in the file or overwrite the contents.
2. For Distribution - the same settings can be updated and loaded onto any machines on the network.

Disadvantages:

1. Relies on database connection
2. Overhead when reading from database

Storing in file advantages:

1. Fast and easy to read and modify.

Disadvantages:

1. Security issue as mentioned above.
2. May need encryption on sensitive data.
3. Versioning is difficult, since you have to create separate files for different versions.

it means that each time we add a platform setting we have to add a column to this table
- depending on what database you are using, but you can store the whole settings as XML (SQL server allows this) in the table, so you do not need to alter the table schema everytime adding a settings; all you need to do is to modify the XML, adding elements to it or removing from it.

but in the end, you have to decide yourself,
there's no better or worse for everyone.

How should I structure my settings table with MySQL?

Table name = 'settings'

name  | varchar <-- primary key
value | varchar

Then you can query like this:

SELECT * FROM settings WHERE name = 'default_printer';

This option is nice and easy and it will work well with 10, or 10,000 settings. With the other option you'll have to add a new column, which would be a completely pointless waste of time.

Edit

After your 1st comment you could choose multiple values like this:

SELECT * FROM settings WHERE name IN ('default_printer','default_page_size');

:-)

---

Related Topics