在 SQL Server 数据库中使用单行配置表。馊主意？

Using a Single Row configuration table in SQL Server database. Bad idea?

提问by David Murdoch

In developing a shopping cart application I've found that I needed to save settings and configurations based on the administrator's preferences and requirements. This information can be anything from company information, Shipping account IDs, PayPal API keys, notification preferences, etc.

在开发购物车应用程序时，我发现我需要根据管理员的偏好和要求保存设置和配置。此信息可以是公司信息、运输帐户 ID、PayPal API 密钥、通知首选项等中的任何信息。

It seems highly inappropriate to create a table to store a single row in a relational database system.

在关系数据库系统中创建一个表来存储单行似乎非常不合适。

What is the appropriate way to store this information?

存储此信息的适当方法是什么？

Note: my DBMS is SQL Server 2008 and programming layer is implemented with ASP.NET (in C#).

注意：我的 DBMS 是 SQL Server 2008，编程层是用 ASP.NET（在 C# 中）实现的。

回答by adrianbanks

I have done this two ways in the past - a single row table and a key/value pair table - and there are positives and negatives to each approach.

过去我曾用两种方法完成过——单行表和键/值对表——每种方法都有优点和缺点。

Single Row

单排

• positive: the values are stored in the correct type
• positive: it is easier to deal with in code (due to the above)
• positive: default values can be given to each setting individually
• negative: a schema change is required to add a new setting
• negative: the table can become very wide if there are lots of settings

• 正：值以正确的类型存储
• 正面：在代码中更容易处理（由于上述原因）
• 正：可以为每个设置单独提供默认值
• 否定：需要架构更改才能添加新设置
• 负面：如果有很多设置，桌子会变得很宽

Key/Value Pair

键/值对

• positive: adding new settings does not require a schema change
• positive: the table schema is narrow, with extra rows being used for new settings
• negative: each setting has the same default value (null/empty?)
• negative: everything has to be stored as strings (ie. nvarchar)
• negative: when dealing with the settings in code, you have to know what type a setting is and cast it

• 正面：添加新设置不需要架构更改
• 正面：表架构很窄，额外的行用于新设置
• 否定：每个设置都有相同的默认值（空/空？）
• 否定：所有内容都必须存储为字符串（即 nvarchar）
• 否定：在处理代码中的设置时，您必须知道设置的类型并对其进行转换

The single row option is by far the easiest one to work with. This is because you can store each setting in its correct type in the database and not have to store the types of the settings as well as their lookup keys in code.

单行选项是迄今为止最容易使用的选项。这是因为您可以将每个设置以其正确的类型存储在数据库中，而不必在代码中存储设置的类型及其查找键。

One thing I was concerned with using this approach was having multiple rows in the "special" single row settings table. I overcame this by (in SQL Server):

我担心使用这种方法的一件事是在“特殊”单行设置表中有多行。我通过（在 SQL Server 中）克服了这个问题：

• adding a new bit column with a default value of 0
• creating a check constraint to ensure that this column has a value of 0
• creating a unique constraint on the bit column

• 添加一个默认值为 0 的新位列
• 创建检查约束以确保此列的值为 0
• 在位列上创建唯一约束

This means that only one row can exist in the table because the bit column has to have a value of 0, but there can only be one row with that value because of the unique constraint.

这意味着表中只能存在一行，因为位列的值必须为 0，但由于唯一约束，该值只能存在一行。

回答by Otávio Décio

You should create a table with a column for the information type and information value (at least). This way you avoid having to create new columns every time a new information is added.

您应该创建一个包含信息类型和信息值列的表（至少）。这样您就可以避免每次添加新信息时都必须创建新列。

回答by Andomar

A single row will work fine; it will even have strong types:

单行可以正常工作；它甚至会有强类型：

show_borders    bit
admin_name      varchar(50)
max_users       int

One disadvantage is that it requires a schema change (alter table) to add a new setting. One alternative is normalizing, where you end up with a table like:

一个缺点是它需要架构更改 ( alter table) 才能添加新设置。一种替代方法是规范化，最终得到如下表格：

pref_name       varchar(50) primary key
pref_value      varchar(50) 

This has weak types (everything is a varchar), but adding a new setting is just adding a row, something you can do with just database write access.

这具有弱类型（所有内容都是 varchar），但添加新设置只是添加一行，您只需使用数据库写入访问权限即可。

回答by mjv

As you guessed, and except for the simplest situations, putting all configurations parameters in a single rows has many drawbacks. It is a bad idea...

正如您所猜测的那样，除了最简单的情况外，将所有配置参数放在一行中有很多缺点。这是一个坏主意......

A convenient way to store configuration and/or user preference type of information is in XML. Many DBMSes support the XML data type. The XML syntax allows you to expend the "language" and structure describing the configuration as this configuration evolves. One advantage of XML is its implicit support for hierarchical structure, allowing for example to store small lists of configuration parameters without having to name these with a numbered suffix. A possible drawback of XML format is that searching and generally modifying this data isn't as straight forward as other approaches (nothing complicated, but not as simple/natural)

一种存储配置和/或用户偏好类型信息的便捷方式是 XML。许多 DBMS 支持 XML 数据类型。XML 语法允许您随着配置的发展扩展描述配置的“语言”和结构。XML 的一个优点是它对分层结构的隐式支持，例如允许存储配置参数的小列表，而不必使用编号后缀命名这些参数。XML 格式的一个可能的缺点是搜索和通常修改这些数据并不像其他方法那样直接（并不复杂，但没有那么简单/自然）

If you want to remain closer to relational model, the Entity-Attribute-Value modelis probably what you need, whereby the individual values are stored in a table that typically looks like:

如果您想更接近于关系模型，实体-属性-值模型可能就是您所需要的，其中各个值存储在通常如下所示的表中：

EntityId     (foreign key to the "owner" of this attribute)
AttributeId  (foreign key to the "metadata" table where the attribute is defined)
StringValue  (it is often convenient to have different columns of different types
IntValue      allowing to store the various attributes in a format that befits 
              them)

Whereby the AttributeId is a foreign key to a table where each possible Attribute ("configuration parameter" in your case) is defined, with say

其中 AttributeId 是表的外键，其中定义了每个可能的属性（在您的情况下为“配置参数”），例如

AttributeId  (Primary Key)
Name
AttributeType     (some code  S = string, I = Int etc.)
Required          (some boolean indicating that this is required)
Some_other_fields   (for example to define in which order these attributes get displayed etc...)

Finally the EntityId allows you to identify some entity which "owns" these various attributes. In your case it could be a UserId or even just implicit if you only have one configuration to manage.

最后，EntityId 允许您识别“拥有”这些不同属性的某些实体。在您的情况下，如果您只有一个配置需要管理，它可能是 UserId 或者甚至只是隐式。

Aside from allowing the list of possible configuration parameters to grow as the application evolves, the EAV model places the "meta data", i.e. the data pertaining to the Attribute themselves, in datatables, hence avoiding all the hard-coding of column names commonly seen when the configuration parameters are stored in a single row.

除了允许可能的配置参数列表随着应用程序的发展而增长之外，EAV 模型将“元数据”，即与属性本身有关的数据放在数据表中，从而避免了所有常见的列名硬编码当配置参数存储在一行中时。

回答by E.J. Brennan

Personally, I would store it in a single row if that is what works. Overkill to store it in an SQL table? probably, but there is no real harm in doing so.

就个人而言，如果可行的话，我会将其存储在一行中。将其存储在 SQL 表中的过度杀伤力？可能，但这样做并没有真正的危害。

回答by Dave Mikesell

You certainly don't have to change your schema when adding a new configuration parameter in the normalized approach, but you're still probably changing your code to process the new value.

在规范化方法中添加新配置参数时，您当然不必更改架构，但您可能仍在更改代码以处理新值。

Adding an "alter table" to your deployment doesn't seem like that big of a tradeoff for the simplicity and type safety of the single row approach.

将“alter table”添加到您的部署中，对于单行方法的简单性和类型安全性而言，似乎并没有那么大的折衷。

回答by rizalp1

A Key and Value pair is similar to a .Net App.Config which can store configuration settings.

键值对类似于可以存储配置设置的 .Net App.Config。

So when you want to retrieve the value you could do:

因此，当您想检索该值时，您可以执行以下操作：

SELECT value FROM configurationTable
WHERE ApplicationGroup = 'myappgroup'
AND keyDescription = 'myKey';

回答by Stephano

A common way to do this is to have a "properties" table simmular to a properties file. Here you can store all your app constants, or not so constant things that you just need to have around.

执行此操作的常用方法是拥有一个类似于属性文件的“属性”表。在这里，您可以存储所有应用程序常量，或者不是那些您只需要拥有的常量。

You can then grab the info from this table as you need it. Likewise, as you find you have some other setting to save, you can add it in. Here is an example:

然后，您可以根据需要从该表中获取信息。同样，当您发现还有一些其他设置要保存时，您可以将其添加进去。 这是一个示例：

property_entry_table

property_entry_table

[id, scope, refId, propertyName, propertyValue, propertyType] 
1, 0, 1, "COMPANY_INFO", "Acme Tools", "ADMIN"  
2, 0, 1, "SHIPPING_ID", "12333484", "ADMIN"  
3, 0, 1, "PAYPAL_KEY", "2143123412341", "ADMIN"   
4, 0, 1, "PAYPAL_KEY", "123412341234123", "ADMIN"  
5, 0, 1, "NOTIF_PREF", "ON", "ADMIN"  
6, 0, 2, "NOTIF_PREF", "OFF", "ADMIN"   

This way you can store the data you have, and the data that you will have next year and don't know about yet :) .

通过这种方式，您可以存储您拥有的数据，以及明年将拥有但还不知道的数据:)。

In this example, your scope and refId can be used for whatever you want on the back end. So if propertyType "ADMIN" has a scope 0 refId 2, you know what preference it is.

在此示例中，您的范围和 refId 可用于后端所需的任何内容。因此，如果 propertyType "ADMIN" 的范围为 0 refId 2，您就知道它是什么首选项。

Property type comes in hand when, someday, you need to store non-admin info in here as well.

当有一天，您还需要在此处存储非管理员信息时，属性类型就派上用场了。

Note that you should not store cart data this way, or lookups for that matter. However if the data is Systemspecific, then you can certainly use this method.

请注意，您不应以这种方式存储购物车数据，也不应为此进行查找。但是，如果数据是特定于系统的，那么您当然可以使用此方法。

For example: If you want to store your DATABASE_VERSION, you'd use a table like this. That way, when you need to upgrade the app, you can check the properties table to see what version of your software the client has.

例如：如果你想存储你的DATABASE_VERSION，你会使用这样的表。这样，当您需要升级应用程序时，您可以检查属性表以查看客户端拥有的软件版本。

The point is you do not want to use this for things that pertain to the cart. Keep you business logic in well defined relational tables. The properties table is for system info only.

关键是您不想将它用于与购物车有关的东西。将业务逻辑保存在定义良好的关系表中。属性表仅用于系统信息。

回答by sidereal

I'm not sure a single row is the best implementation for configuration. You might be better off having a row per configuration item with two columns (configName, configValue), although this will require casting all of your values to strings and back.

我不确定单行是配置的最佳实现。您最好为每个配置项设置一行，其中包含两列（configName、configValue），尽管这需要将所有值转换为字符串并返回。

Regardless, there's no harm in using a single row for global config. The other options for storing it in the DB (global variables) are worse. You could control it by inserting your first configuration row, then disabling inserts on the table to prevent multiple rows.

无论如何，对全局配置使用单行没有坏处。将其存储在 DB（全局变量）中的其他选项更糟。您可以通过插入第一个配置行来控制它，然后在表上禁用插入以防止多行。

回答by spintool

You can do the Key/Value Pair without conversions by adding a column for each major type and one column telling you which column the data is in.

您可以通过为每个主要类型添加一列和一列告诉您数据位于哪一列，而无需转换即可进行键/值对。

So your table would look something like:

所以你的桌子看起来像：

id, column_num, property_name, intValue, floatValue, charValue, dateValue
1, 1, weeks, 51, , ,
2, 2, pi, , 3.14159, , 
3, 4, FiscYearEnd, , , , 1/31/2015
4, 3, CompanyName, , , ACME, 

It uses a little more room but at most you are using a few dozen attributes. You can use a case statement off the column_num value to pull / join the right field.

它使用了更多的空间，但您最多使用几十个属性。您可以使用 column_num 值的 case 语句来拉/加入正确的字段。