SQL 应用程序配置或应用程序选项设置的最佳表设计？

Best table design for application configuration or application option settings?

提问by Andrew

I need to store a series of configuration values in a database. A couple ways I thought of to store them are: a table with 2 colums(name,value) and a row for each pair, or a table with a column for each config parameter and 1 row? With the first I only need to add another row to add a config value, with the second I need to add a column to the table. Are there any issues with either I should take in to consideration? Is one more efficient than the other?

我需要在数据库中存储一系列配置值。我想到的几种存储它们的方法是：一个有 2 列（名称，值）和每对一行的表，或者每个配置参数都有一个列和 1 行的表？第一个我只需要添加另一行来添加配置值，第二个我需要向表中添加一列。我应该考虑任何问题吗？一种比另一种更有效吗？

采纳答案by Peter Cardona

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.

SQL 擅长建模和操作任意大的类似（如果不是相同）结构化数据集。一组配置信息真的不是这样——你有一行数据或者你有多行完全不相关的数据。这表示您只是将其用作数据存储。我说跳过 SQL 数据模型并变得简单。

回答by Luc M

One more consideration: with a column for each config parameter, you can easily have versions. Each row represents a version^*

还有一个考虑：每个配置参数都有一列，您可以轻松拥有版本。每行代表一个版本^*

---

---

^*of a complete parameter set ^{(as pointed out in a comment by granadaCoder)}

^*完整的参数集^{（如granadaCoder在评论中指出的那样）}

回答by cletus

The first issue you should consider is this: stop thinking about the efficiency of retrieving the information. first and foremost, figure out how to effectivelyand correctlymodel the data and then (and only then) figure out how to do it efficiently.

您应该考虑的第一个问题是：停止考虑检索信息的效率。首先，搞清楚如何有效和正确的数据，然后（只有这样）计算出如何做到这一点的模型有效。

So it depends on the nature of the config data you're storing. If separate (name,value) pairs are basically unrelated then store it as one per row. If they are related then you may want to consider a scheme that has multiple columns.

因此，这取决于您存储的配置数据的性​​质。如果单独的 (name,value) 对基本上不相关，则将其存储为每行一个。如果它们相关，那么您可能需要考虑具有多个列的方案。

What do I mean by related? Consider some cache config. Each cache has several attributes:

我说的相关是什么意思？考虑一些缓存配置。每个缓存都有几个属性：

• eviction policy;
• expiry time;
• maximum size.

• 驱逐政策；
• 到期时间；
• 最大尺寸。

Assume each cache has a name. You could store this data as three rows:

假设每个缓存都有一个名称。您可以将此数据存储为三行：

• <name>_EVICTION
• <name>_EXPIRY
• <name>_MAX_SIZE

• <name>_EVICTION
• <name>_EXPIRY
• <name>_MAX_SIZE

but this data is relatedand you may often need to retrieve them all at once. In that case it may make sense to have a cache_config table with five columns: id, name, eviction, expiry, max_size.

但这些数据是相关的，您可能经常需要一次检索它们。在这种情况下，有一个包含五列的 cache_config 表可能是有意义的：id、name、eviction、expiry、max_size。

That's what I mean by related data.

这就是我所说的相关数据的意思。

回答by Kenny Evitt

One disadvantage of using a separate row for each (application) configuration setting (or application option) is that you can't store the setting values in a column with an appropriate data type. Can users enter data with an invalid type? Is that pertinent to your application?

为每个（应用程序）配置设置（或应用程序选项）使用单独的行的一个缺点是您无法将设置值存储在具有适当数据类型的列中。用户可以输入无效类型的数据吗？这与您的应用程序有关吗？

One benefit of using separate columns is that any code in your DB itself (e.g. stored procedures, functions, etc.) can use a value of the appropriate data-type without first needing to check for invalid values and then convert to the appropriate data type.

使用单独列的一个好处是，您的数据库本身中的任何代码（例如存储过程、函数等）都可以使用适当数据类型的值，而无需先检查无效值，然后再转换为适当的数据类型.

If you're manually deploying changes to your application DB, then yes if you're using an EAV design it is veryslightly easier to deploy new configuration settings, but really what's the savings for:

如果您手动部署更改您的应用程序数据库，然后是如果你使用的是EAV的设计是非常轻微更易于部署新的配置设置，但真的有什么节省：

INSERT Options ( ConfigurationSetting, Value )
VALUES ( 'NewConfigurationSetting', NewConfigurationSettingValue )

versus:

相对：

ALTER TABLE Options ADD NewConfigurationSetting some_datatype

UPDATE Options
SET NewConfigurationSetting = NewConfigurationSettingValue

回答by granadaCoder

I DESPISE putting non-string values in a string-column (aka, incorrect-data-types). (As @Kenny Evitt discusses above)

我鄙视将非字符串值放在字符串列中（又名，不正确的数据类型）。（正如@Kenny Evitt 在上面讨论的那样）

So I'm come up with the below alternativethat goes vertical AND deals with correct datatypes.

所以我想出了下面的替代方案，它可以垂直并处理正确的数据类型。

I don't actually use money and smallmoney. But I included them for completeness. Note, there are a few other datatypes out there

我实际上不使用钱和小钱。但为了完整起见，我将它们包括在内。请注意，还有一些其他数据类型

see

看

https://msdn.microsoft.com/en-us/library/ms187752.aspx?f=255&MSPPError=-2147217396

https://msdn.microsoft.com/en-us/library/ms187752.aspx?f=255&MSPPError=-2147217396

But the below covers most things.

但以下内容涵盖了大部分内容。

to be honest, I only use string (varchar(1024)), int, smallint and bit ... 99% of the time.

老实说，我只使用 string (varchar(1024))、int、smallint 和 bit ... 99% 的时间。

It isn't perfect. Aka, you have alot of null tuples. But since you only grab these once (and cache), the mapping to a settings object (in c# in my world) isn't difficult.

它并不完美。Aka，你有很多空元组。但是因为你只抓取一次（和缓存），映射到设置对象（在我的世界中的 c# 中）并不困难。

CREATE TABLE [dbo].[SystemSetting](
    [SystemSettingId] [int] IDENTITY NOT NULL,

    [SettingKeyName] [nvarchar](64) NOT NULL, 
    [SettingDataType] [nvarchar](64) NOT NULL, /* store the datatype as string here */

    [SettingValueBigInt] bigint NULL, 
    [SettingValueNumeric] numeric NULL, 
    [SettingValueSmallInt] smallint NULL, 
    [SettingValueDecimal] decimal NULL, 
    [SettingValueSmallMoney] smallmoney NULL, 
    [SettingValueInt] int NULL, 
    [SettingValueTinyInt] tinyint NULL, 
    [SettingValueMoney] money NULL, 
    [SettingValueFloat] float NULL, 
    [SettingValueReal] real NULL, 
    [SettingValueDate] date NULL, 
    [SettingValueDateTimeOffSet] datetimeoffset NULL, 
    [SettingValueDateTime2] datetime2 NULL, 
    [SettingValueSmallDateTime] smalldatetime NULL, 
    [SettingValueDateTime] datetime NULL, 
    [SettingValueTime] time NULL, 
    [SettingValueVarChar] varchar(1024) NULL, 
    [SettingValueChar] char NULL, 

    [InsertDate] [datetime] NOT NULL DEFAULT (GETDATE()),               
    [InsertedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),       
    [LastUpdated] [datetime] NOT NULL DEFAULT (GETDATE()),              
    [LastUpdatedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),    
)

Now, if that is too much, and you're determined to use "strings" for all the values, then here is some DDL.

现在，如果这太多了，并且您决定对所有值使用“字符串”，那么这里是一些 DDL。

DROP TABLE [dbo].[SystemSetting]
DROP TABLE [dbo].[SystemSettingCategory]

CREATE TABLE [dbo].[SystemSettingCategory] (
    [SystemSettingCategoryId] [int] NOT NULL,
    [SystemSettingCategoryName] [nvarchar](64) NOT NULL, 
    [InsertDate] [datetime] NOT NULL DEFAULT (GETDATE()),               
    [InsertedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),       
    [LastUpdated] [datetime] NOT NULL DEFAULT (GETDATE()),              
    [LastUpdatedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),    
    CONSTRAINT [PK_SystemSettingCategory] PRIMARY KEY CLUSTERED ([SystemSettingCategoryId] ASC),
    CONSTRAINT UQ_SystemSettingCategoryName UNIQUE NONCLUSTERED ([SystemSettingCategoryName])
)   




CREATE TABLE [dbo].[SystemSetting] (
    [SystemSettingId] [int] NOT NULL,
    [SystemSettingCategoryId] INT NOT NULL,     /* FK to [SystemSettingCategory], not shown here */
    [SettingKeyName] [nvarchar](64) NOT NULL, 
    [SettingValue] nvarchar(1024) NULL,
    [InsertDate] [datetime] NOT NULL DEFAULT (GETDATE()),               
    [InsertedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),       
    [LastUpdated] [datetime] NOT NULL DEFAULT (GETDATE()),              
    [LastUpdatedBy] [nvarchar](50) NOT NULL DEFAULT (SUSER_SNAME()),    
    CONSTRAINT [PK_SystemSetting] PRIMARY KEY CLUSTERED ([SystemSettingId] ASC),
    CONSTRAINT FK_SystemSettingCategory_SystemSettingCategoryId foreign key ([SystemSettingCategoryId]) references [SystemSettingCategory] ([SystemSettingCategoryId]),
    CONSTRAINT UQ_SystemSettingCategoryId_SettingKeyName UNIQUE NONCLUSTERED ( [SystemSettingCategoryId] , [SettingKeyName] )
)   



INSERT INTO [dbo].[SystemSettingCategory] ( [SystemSettingCategoryId] , [SystemSettingCategoryName] )
select 101 , 'EmployeeSettings' UNION ALL select 201, 'StopLightSettings'

INSERT INTO [dbo].[SystemSetting] ( [SystemSettingId] , [SystemSettingCategoryId] , [SettingKeyName] , [SettingValue] )
          select 1001 , 101 , 'MininumAgeRequirementMonths' , convert(varchar(16) , (12 * 18))
UNION ALL select 1002 , 101 , 'MininumExperienceMonths' , convert(varchar(8) , 24)
UNION ALL select 2001 , 201 , 'RedLightPosition' , 'top'
UNION ALL select 2002 , 201 , 'YellowLightPosition' , 'middle'
UNION ALL select 2003 , 201 , 'GreenLightPosition' , 'bottom'

/* should fail */
/* start 
INSERT INTO [dbo].[SystemSettingCategory] ( [SystemSettingCategoryId] , [SystemSettingCategoryName] )
select 3333 , 'EmployeeSettings'
INSERT INTO [dbo].[SystemSettingCategory] ( [SystemSettingCategoryId] , [SystemSettingCategoryName] )
select 101 , 'xxxxxxxxxxxxxx'
INSERT INTO [dbo].[SystemSetting] ( [SystemSettingId] , [SystemSettingCategoryId] , [SettingKeyName] , [SettingValue] )
          select 5555 , 101 , 'MininumAgeRequirementMonths' , 555
INSERT INTO [dbo].[SystemSetting] ( [SystemSettingId] , [SystemSettingCategoryId] , [SettingKeyName] , [SettingValue] )
          select 1001 , 101 , 'yyyyyyyyyyyyyy' , 777
INSERT INTO [dbo].[SystemSetting] ( [SystemSettingId] , [SystemSettingCategoryId] , [SettingKeyName] , [SettingValue] )
          select 5555 , 555 , 'Bad FK' , 555
 end */


Select * from [dbo].[SystemSetting] where [SystemSettingCategoryId] = 101 /* employee related */
Select * from [dbo].[SystemSetting] where [SystemSettingCategoryId] = 201 /* StopLightSettings related */

Now, taking it a big farther, you can still create strongly typed dotnet objects with the correct datatypes, and then convert your datareader/dataset into the strong object as seen below.

现在，更进一步，您仍然可以创建具有正确数据类型的强类型 dotnet 对象，然后将数据读取器/数据集转换为强对象，如下所示。

public class EmployeeSettings
{
    public Int16 MininumAgeRequirementMonths { get; set; }
    public Int16 MininumExperienceMonths{ get; set; }
}


public class StopLightSettings
{
    public string RedLightPosition { get; set; }
    public string YellowLightPosition { get; set; }
    public string GreenLightPosition { get; set; }
}

You can still do the C# classes (or whatever language)........and use the SettingDataType method above. The "mapping" code just needs a little extra work.

您仍然可以使用 C# 类（或任何语言）........并使用上面的 SettingDataType 方法。“映射”代码只需要一些额外的工作。

When not outvoted, I use the SettingDataType and the C# classes as seen above.

当没有被否决时，我使用 SettingDataType 和 C# 类，如上所示。

回答by Aziz

I think the 2-column (name, value) design is much better. As you said, if you need to add a new property, all you need to do is to "insert" a new row. While in the other design (single-row), you'll need to change the table schema to add a column for the new property.

我认为 2 列（名称、值）设计要好得多。正如你所说，如果你需要添加一个新的属性，你需要做的就是“ insert”一个新行。而在其他设计（单行）中，您需要更改表架构来为新属性添加一列。

This, however, depends on whether your list of properties are going to change in the future.

但是，这取决于您的属性列表将来是否会更改。

回答by JBrooks

Here I blog about when we moved our AppSettings to a Database Table.The performance isn't an issue because it is pull only once at the start of the application and stored in a dictionary for easy lookup.

我在这里写了一篇关于我们何时将 AppSettings 移动到数据库表的博客。性能不是问题，因为它在应用程序开始时只被拉取一次，并存储在字典中以便于查找。

Not sure about your application, but the important reason why we did this is now it is impossible to be using the Production values if you are in Dev, Test, etc.

不确定您的应用程序，但我们这样做的重要原因是，如果您处于开发、测试等阶段，则不可能使用生产值。

回答by GG.

You can save configuration efficiently using XML. Some Database support Pure XML feature in which you can save value as xml data type and you can run XQUERY on that particular column.

您可以使用 XML 有效地保存配置。某些数据库支持纯 XML 功能，您可以在其中将值保存为 xml 数据类型，并且可以在该特定列上运行 XQUERY。

Create a table with two column name and configuration. name with string datatype and configuration with xml data type so no need to worry about insertion and deletion of new configuration parameters, you will just a new tag in xml. And if database does not support XML then just save it as a string but in XML format so you can parse that configuration manually or using some API efficiently.

创建一个包含两个列名和配置的表。名称为字符串数据类型，配置为 xml 数据类型，因此无需担心新配置参数的插入和删除，您只需在 xml 中添加一个新标签。如果数据库不支持 XML，那么只需将其保存为字符串，但以 XML 格式保存，以便您可以手动解析该配置或有效地使用某些 API。

I think this would be better way instead of storing complete configuration as a string.

我认为这将是更好的方法，而不是将完整的配置存储为字符串。

回答by Jed Smith

CREATE TABLE Configuration (
    Name ...,
    Value ...,
);

The best way. Adding a column to a table usually sucks, and what's the point of a table with one row?

最好的方法。在表格中添加一列通常很糟糕，只有一行的表格有什么意义？

Not sure this is appropriate for SQL, but alas...question answered.

不确定这是否适用于 SQL，但唉...问题已回答。

回答by Neville Kuyt

"Best" depends entirely on context - how will this data be used?

“最佳”完全取决于上下文——如何使用这些数据？

If all you need to do is store and retrieve a single set of configuration settings, I'd question the use of a relational database in the first place - it adds no obvious benefit over config files on the file system. You can't easily use version control for you configuration files, and managing environmental differences (e.g. "DEV", "TEST" and "PRODUCTION" environments) now requires a GUI to modify the database (oh, and how do you connect to the database in the first place?).

如果您需要做的只是存储和检索一组配置设置，那么我首先会质疑关系数据库的使用 - 与文件系统上的配置文件相比，它没有明显的好处。您不能轻松地为您的配置文件使用版本控制，并且管理环境差异（例如“DEV”、“TEST”和“生产”环境）现在需要一个 GUI 来修改数据库（哦，您如何连接到首先是数据库？）。

If your application needs to "reason" about the configuration as a whole - e.g. if you have a multi-tenant solution and need to dynamically configure the application based on the current system - I'd suggest storing the configuration files as a text document in your database, with the metadata that allows the application to store/retrieve the document. Different database engines have different solutions for storing text documents. For instance, in a multi-tenancy system you might have:

如果您的应用程序需要对整个配置进行“推理” - 例如，如果您有一个多租户解决方案并且需要根据当前系统动态配置应用程序 - 我建议将配置文件作为文本文档存储在您的数据库，以及允许应用程序存储/检索文档的元数据。不同的数据库引擎对于存储文本文档有不同的解决方案。例如，在多租户系统中，您可能有：

ID client_id valid_from     valid_until configuration_file
-------------------------------------------------------
1         1   2016/03/16         NULL      <<DOCUMENT>>

This would allow you to retrieve the file for client 1, that was valid after 3 March, and do whatever the application needs.

这将允许您检索客户端 1 的文件，该文件在 3 月 3 日之后有效，并执行应用程序需要的任何操作。

If your application needs to reason about the content of the configuration, not the configuration as an entity in its own right, you have a different problem. The "name/value" solution you propose is also known as Entity/Attribute/Value (EAV), and there are lotsofSOquestionsdiscussing benefits and drawbacks. TL;DR: it's hard to convert even simple questions to SQL when using EAV.

如果您的应用程序需要对配置的内容进行推理，而不是将配置作为实体本身，那么您会遇到不同的问题。您提出的“名称/值”解决方案也称为实体/属性/值 (EAV)，并且有很多讨论优缺点的SO问题。TL;DR：在使用 EAV 时，即使是简单的问题也很难转换为 SQL。

It's much easier to query the data if each configuration setting is a column, with the appropriate data type. But this does mean that you end up with a very "wide" table (most applications have dozens or even hundreds of config values), and every time you want to add a configuration setting, you end up modifying your database schema, which isn't practical.

如果每个配置设置都是具有适当数据类型的列，则查询数据会容易得多。但这确实意味着您最终会得到一个非常“宽”的表（大多数应用程序有数十个甚至数百个配置值），并且每次您想要添加配置设置时，最终都会修改您的数据库架构，这不是实用。

The alternative, then, is to store the configuration values as a structured document - XML and JSON are widely supported. These formats can be queried by the database engine, but do not require a fixed schema.

然后，另一种方法是将配置值存储为结构化文档 - XML 和 JSON 得到广泛支持。数据库引擎可以查询这些格式，但不需要固定模式。