database 将平台配置存储在数据库还是文件中更好？

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

提问by Pavel K.

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

ps，我几乎可以肯定有人已经有这样的问题了，但我似乎找不到

采纳答案by K2so

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.

1. 安全性 - 用户可以轻松更改文件中的设置或覆盖内容。
2. 对于分发 - 可以更新相同的设置并将其加载到网络上的任何机器上。

Disadvantages:

缺点：

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

1. 依赖数据库连接
2. 从数据库读取时的开销

Storing in file advantages:

存储在文件中的优点：

1. Fast and easy to read and modify.

1. 快速且易于阅读和修改。

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.

1. 如上所述的安全问题。
2. 可能需要对敏感数据进行加密。
3. 版本控制很困难，因为您必须为不同的版本创建单独的文件。

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.

这意味着每次我们添加平台设置时，我们都必须向该表添加一列 - 取决于您使用的数据库，但您可以将整个设置作为 XML（SQL 服务器允许这样做）存​​储在表中，所以您可以无需每次添加设置时更改表架构；您需要做的就是修改 XML，向其中添加元素或从中删除元素。

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

但最终，你必须自己决定，每个人都没有更好或更坏。

回答by Greg Beech

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& SettingNameare 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 SettingValueis a string, and then the SettingTypeis a discriminator that tells us how the setting value should be interpreted (e.g. 1 = string, 2 = bool, 3 = decimal, etc.).

该SectionName＆SettingName是主键，我们只是拆起来更容易查询什么是一节，并允许各个部分进入处理程序的加载，而不是一次装载一大堆。这SettingValue是一个字符串，然后SettingType是一个鉴别器，告诉我们应该如何解释设置值（例如1 = 字符串，2 = bool，3 = 十进制等）。

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

我们发现它是一种比文件更好的配置方式，因为这意味着您可以在需要时通过管理界面轻松地以编程方式更改配置值，这可以围绕可以进入每个设置的内容强制执行逻辑。你不能用文件那么容易地做到这一点（当然，这是可能的）。

回答by Jé Queue

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.

如果设计人员只将系统属性保留在 DB 中，那么在系统环境实施期间，我的生活会轻松 10%。

回答by z5h

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

为什么每次都有一个新列？为什么不只有 2 列：NAME 和 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.

此外，在速度方面，我们缓存配置（具有触发重新加载的能力）。每次需要属性时重新读取配置是没有意义的。所以就速度而言，这并不重要。你做一次。

回答by stefgosselin

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.

上面的答案似乎没有考虑到需要在不同环境中部署的应用程序，即：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:

每个环境都有自己的配置，考虑 symfony 布置其配置文件的方式：

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.

我的 2 美分。

回答by Hilkiah Makemo

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.

我想就像大家说的，取决于你的应用环境和要求。但如果我必须选择一个一般规则，我会说两者兼而有之。首先，创建一个数据库表来存储所有配置。这有两个好处：1) 版本控制 - 允许您轻松跟踪以前的配置。2) 管理 - 您可以创建一个界面，您的用户可以访问该界面以更改设置。

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.

其次，您创建一个文件，在您保存站点并将其发布到生产环境后，它还会将所有这些设置保存到一个易于访问的文件中。事实上，如果您使用 PHP 进行 Web 编程，那么该文件应该是一个带有不需要进一步操作的数组数据（键值对）的 php 文件。我的意思是，如果这是您需要的最终输出，则无需将您的 yaml 或 json 转换为数组。