在 SQL Server 数据库中使用单行配置表.馊主意? [英] Using a Single Row configuration table in SQL Server database. Bad idea?
问题描述
在开发购物车应用程序时，我发现我需要根据管理员的偏好和要求保存设置和配置.此信息可以是公司信息、运输帐户 ID、PayPal API 密钥、通知首选项等中的任何信息.
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.
在关系数据库系统中创建一个表来存储单行似乎非常不合适.
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?
注意:我的 DBMS 是 SQL Server 2008，编程层是用 ASP.NET(在 C# 中)实现的.
Note: my DBMS is SQL Server 2008 and programming layer is implemented with ASP.NET (in C#).
推荐答案
我过去做过这两种方法 - 单行表和键/值对表 - 每种方法都有优点和缺点.
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.
- 正:值以正确的类型存储
- 肯定:在代码中更容易处理(由于上述原因)
- positive:可以为每个设置单独指定默认值
- 否定:需要架构更改才能添加新设置
- 否定:如果有很多设置，表格会变得很宽
- 肯定:添加新设置不需要更改架构
- 正:表架构很窄，额外的行用于新设置
- 否定:每个设置都有相同的默认值(空/空?)
- 否定:所有内容都必须存储为字符串(即 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.
使用这种方法时我担心的一件事是在特殊"文件中有多行.单行设置表.我通过(在 SQL Server 中)克服了这个问题:
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):
- 添加一个默认值为 0 的新位列
- 创建检查约束以确保此列的值为 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.
这篇关于在 SQL Server 数据库中使用单行配置表.馊主意?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
