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

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 f开发者_开发技巧ile.

what is better/faster approach?

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

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

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.

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.

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.

To be fair, the answer is not so cut and clear.

The answers above do not seem to take into account applications that need to be deployed in different environnements ie: dev , qa, staging, prod.

They also do not take into account the importance of versioning configuration, ie knowing who changed what, where and when.

All modern frameworks provide a way of fetching proper configuration for specific environments, usually via environment variable.

Each environnment has its own config, consider the way symfony lays out its configuration files:

your-project/
├─ app/
│  ├─ ...
│  └─ config/
│     ├─ config.yml
│     ├─ config_dev.yml
│     ├─ config_prod.yml
│     ├─ config_test.yml
│     ├─ parameters.yml
│     ├─ parameters.yml.dist
│     ├─ routing.yml
│     ├─ routing_dev.yml
│     └─ security.yml
├─ ...

For these reasons, I most certainly prefer configuration in file.

My 2 cents.

I think like everyone said, depends on your application environment and requirements. But if I had to pick a general rule, I'd say BOTH. First, you create a database table to store all your configurations. This is good for two reasons: 1) Versioning - allows you to easily keep track of previous configurations. 2) Management - You can create an interface that your users can access to change settings.

Secondly, you create a file that after you saved and published the site to production, it will also save all those settings into a file that will be easily accessed. In fact, if you are programming in PHP for the web, that file should be a php file with array data (key-value pairs) that need no further manipulation. By this I mean, no need to convert your yaml, or json into array if that is the final output you need to have.