在SQL Server数据库中使用单行配置表。馊主意？ [英] Using a Single Row configuration table in SQL Server database. Bad idea?

问题描述

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

似乎高度不适合创建表来存储单行

注意：我的数据库管理系统（DBMS）是SQL Server 2008和编程层是用ASP.NET（在C＃）实现。

解决方案

过去 - 单行表和键/值对表 - 每种方法都有正面和负面。

单行

• 正数：这些值以正确的类型存储


• 正面：代码中更容易处理）


• 正数：可以单独为每个设置指定默认值


• 否定：需要更改架构才能添加新设置


• 否定：如果有很多设置，表格可能变得非常宽泛

键/ h3>

• 正面：添加新设置不需要模式更改


• 正面：表模式很窄，


• 否定：每个设置都具有相同的默认值（空/空？）


• 否定：被存储为字符串（即。 nvarchar）


• negative：在处理代码中的设置时，您必须知道设置是什么类型并强制转换。

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

一个事情我关心使用这种方法是在特殊单行设置表中有多行。我在（在SQL Server中）克服了这一点：

• 添加一个默认值为0的新位列


• 创建检查约束以确保此列的值为0


• 在位列上创建唯一约束

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

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?

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.

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

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

• 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

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屋！