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

Question

currently we are developing an app in which we use database table to store platform settings, like maximum file size, maximum users number, support email, etc.

it means that each time we add a platform setting we have to add a column to this table.

in my previous projects i am used to store such information in file.

what is better/faster approach?

ps, i am almost sure someone already had such question, but i can't seem to find it

Answer 1

Why a new column every time? Why not just 2 columns: NAME and VALUE.

What we do is set defaults in a file, then override those defaults in the database when needed, per deployment.

Also, in terms of speed, we cache the configuration (with the ability to trigger a reload). Makes no sense to re-read the configuration every time you need a property from it. So in terms of speed, it doesn't really matter. You do it once.

Answer 2

We store config settings in a key/value type table, something like:

CREATE TABLE Configuration.GlobalSettings
(
    SectionName VARCHAR(50),
    SettingName VARCHAR(50),
    SettingValue VARCHAR(1000),
    SettingType TINYINT
);

The SectionName & SettingName are the primary key, we just split them up to make it easier to query what is in a section, and to allow the loading of individual sections into handlers rather than loading the whole lot at once. The SettingValue is a string, and then the SettingType is a discriminator that tells us how the setting value should be interpreted (e.g. 1 = string, 2 = bool, 3 = decimal, etc.).

This means you don't have to change the table structure for new settings, just add a new one in the deployment script or wherever it is you set these things up.

We find it a better way do do config than a file because it means you can easily programmatically change config values through an admin interface when needed, which can enforce logic around what can go into each setting. You can't do that so easily with a file (though, of course, it is possible).

Answer 3

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.

Answer 4

I can tell you as I manage a particularly large application at numerous sites that keeping configs in local files is a complete pain. Often times the configs are read and cached and not able to be changed during run time others have scale-out systems where configs need to be repeatedly changed and bounced.

My life would be 10% easier during system landscape implementation if the designers would just keep system properties at the DB.

Answer 5

If you need to add a new column if a new type of whatever you are storing appears, your schema is probably not normalized.