sql-server - TSQL 表设计：如何在数据库表中表示“null 的原因”类别？

Question

我正在寻求有关如何最好地代表以下客户要求的任何建议或想法。这是一个相当长的帖子，所以希望这是正确的论坛 - 如果有更好的地方，很高兴得到纠正。

我有几种方法可以从技术上解决需求，但我正在尝试寻找一种有效的方法，因为大多数方法似乎都是一种妥协。

源数据

• 我正在接收需要存储在关系表（SQL Server）中的 json 形式的不同源“数据集”。
• 每个数据集都是嵌套的，但不是太深，逻辑上它看起来像行和列（或 csv 文件）
• 每天将增加大约 500,000 行，
• 每个数据集与另一个数据集的列数不同（但每个数据集在历史上是一致的）。单个数据集中最多可能有 50 个数据列。
• 每个列/属性数据可能是不同的数据类型，例如 nvarchar、integer、DateTime 等
• 某些列/属性数据合法地为空，但记录了“ null 的原因”
  • 不为空（我们有一个合法的值）
  • 未知（正常的 null 含义）
  • 直到未来的日期才会知道
  • 等待回复
• 单个数据集表中的许多列都有“null 原因”选项

技术设计思想

1) 基于列的设计方案一

• 每个“数据”列都可以有一个次要的“null 原因”列。缺点是我们将表中的列数增加了一倍。
• 包含数据和“null 原因”数据的表
• “空的原因”定义表 - 即外键关系

IE

CREATE TABLE dbo.DataSet1
(
    [RowID]         BIGINT IDENTITY(1,1) NOT NULL,
    [DataAttribute01] NVARCHAR (128) NULL,
    [DataAttribute01_ReasonForNull] TINYINT NOT NULL, -- FK to dbo.ReasonForNullCategoryType
    [DataAttribute02] DateTime NULL,
    [DataAttribute02_ReasonForNull] TINYINT NOT NULL, -- as above comment
    [DataAttribute03] UNIQUEIDENTIFIER NULL,
    [DataAttribute03_ReasonForNull] TINYINT NOT NULL, -- as above comment
    [DataAttribute03] [money] NULL,
    [DataAttribute03_ReasonForNull] TINYINT NOT NULL, -- as above comment
)


CREATE TABLE dbo.ReasonForNullCategoryType
(
    [ReasonForNullCategoryTypeID]   TINYINT NOT NULL,
    [ReasonForNullCategoryTypeName] NVARCHAR (128) NULL,

)

INSERT INTO dbo.ReasonForNullCategoryType([ReasonForNullCategoryTypeID],[ReasonForNullCategoryTypeName]) VALUES(1, 'Not Null - a Legitimate Value')
INSERT INTO dbo.ReasonForNullCategoryType([ReasonForNullCategoryTypeID],[ReasonForNullCategoryTypeName]) VALUES(2, 'Null Value')
INSERT INTO dbo.ReasonForNullCategoryType([ReasonForNullCategoryTypeID],[ReasonForNullCategoryTypeName]) VALUES(3, 'Won't be known until a future date')
INSERT INTO dbo.ReasonForNullCategoryType([ReasonForNullCategoryTypeID],[ReasonForNullCategoryTypeName]) VALUES(4, 'Waiting for a response')

2) 基于列的设计方案二

• 一张数据表
• “空值原因”数据的一张表

IE

CREATE TABLE dbo.DataSet1
(
    [RowID]         BIGINT IDENTITY(1,1) NOT NULL,
    [DataAttribute01] NVARCHAR (128) NULL,
    [DataAttribute02] DateTime NULL,
    [DataAttribute03] UNIQUEIDENTIFIER NULL,
    [DataAttribute03] [money] NULL,
)

CREATE TABLE dbo.DataSet1_ReasonForNull
(
    [RowID]         BIGINT NOT NULL, -- same Id as in dbo.DataSet1 table
    [DataAttribute01_ReasonForNull] TINYINT NOT NULL, -- FK to dbo.ReasonForNullCategoryType
    [DataAttribute02_ReasonForNull] TINYINT NOT NULL, -- as above comment
    [DataAttribute03_ReasonForNull] TINYINT NOT NULL, -- as above comment
    [DataAttribute03_ReasonForNull] TINYINT NOT NULL, -- as above comment
)

3) 基于行的设计

每个“数据”列都可以表示为单独的行，属性名称列，nu​​ll 的原因有一个次要的“null 原因”列。

缺点是：

• 是我们大大增加了每个数据集上传的行数。
• 我们需要将不同的数据类型表示为单个数据类型
• 强制每个数据集行的正确行数
  • 即 SQL 行数应与数据集“行”列数相同
• 如果将其表示为字符串，则会丢失十进制类型数据的精度

CREATE TABLE dbo.DataSet1
(
    [RowID]         BIGINT IDENTITY(1,1) NOT NULL,
    [DataAttributeName] NVARCHAR (128) NOT NULL,
    [DataAttributeValue] NVARCHAR (1024) NULL,
    [ReasonForNull] TINYINT NOT NULL, -- FK to dbo.ReasonForNullCategoryType table
)

搜索数据

• 需要能够回答有关数据的问题。
• 对每个数据集上传的 [Money] 列求和
• 计算“无效原因”的数量（作为每个数据集上传的总数）
• 计算每列“空的原因”的数量
  • DataAttribute01 列等有多少“等待响应”原因
  • 重复其他“原因”

任何想法都非常感谢。我更倾向于选项 1，但我敢肯定有很多我没有想到的选项，而其他人有！

谢谢。

标签： sql-serverdatabase-design