主页 / dba / 问题 / 109293
Accepted
David Garrison
Asked: 2015-08-07 14:53:21 +0800 CST

存储可能是多种不同类型的值的正确方法

  • 772

我有一个答案表和一个问题表。

Answers 表有一个值，但根据问题，该值可能是 a bit、nvarchar或number（到目前为止）。问题有一个关于其预期答案值类型应该是什么的概念。

在某一点或另一个点解析这些答案值很重要，因为至少需要比较这些数字。

对于更多上下文，问题和潜在答案（通常是允许文本框类型输入的数据类型）由一些用户在各种调查中提供。然后由其他指定用户提供答案。

我考虑过的几个选项是：

A. 根据预期类型（在问题中跟踪）不同解析的 XML 或字符串

B. 三个单独的表，它们引用（或被引用）Answer 表并根据预期类型连接。在这种情况下，我不确定设置约束以确保每个问题只有一个答案的最佳方法，或者是否应该留给应用程序。

C. Answer 表上的三个单独的列，可以根据预期的类型进行检索。

我很乐意就这些方法的优缺点或我没有考虑过的替代方法获得一些意见。

sql-server database-design

4 个回答

  1. 根据您所说，我将使用以下一般模式：

    CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
)
CREATE TABLE [dbo].[PollOption]
(
    [PollOptionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollQuestionId] INT NOT NULL,  -- Link to the question here because options aren't shared across questions
    [OptionText] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL  -- Remove this if you don't need to hide options

    CONSTRAINT [FK_PollOption_PollQuestionId_to_PollQuestion_PollQuestionId] FOREIGN KEY ([PollQuestionId]) REFERENCES [dbo].[PollQuestion]([PollQuestionId])
)
CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

    您并不真正关心答案是否是数字、日期、单词等，因为数据是问题的答案，而不是您需要直接操作的东西。此外，数据仅在问题的上下文中才有意义。因此，nvarchar 是用于存储数据的最通用的人类可读机制。

    问题和可能的答案将从第一个用户那里收集并插入到 PollQuestion 和 PollOption 表中。回答问题的第二个用户将从答案列表中进行选择（真/假 = 2 的列表）。如果合适，您还可以扩展 PollQuestion 表以包含创建者的用户 ID，以便跟踪他们创建的问题。

    在您的 UI 上，用户选择的答案可以与 PollOptionId 值相关联。与 PollQuestionId 一起，您可以快速验证答案是否对问题有效。如果有效，他们的响应将被输入到 PollResponse 表中。

    根据您的用例的详细信息，有几个潜在的问题。如果第一个用户想要使用数学问题，并且您不想提供多个可能的答案。另一种情况是，如果初始用户提供的选项不是第二个用户可以选择的唯一选项。您可以按如下方式修改此架构以支持这些额外的用例。

    CREATE TABLE [dbo].[PollResponse]
(
    [PollResponseId] INT NOT NULL PRIMARY KEY IDENTITY,
    [PollOptionId] INT NULL,
    [PollQuestionId] INT NOT NULL,
    [UserId] INT NOT NULL,
    [AlternateResponse] NVARCHAR(50) NULL, -- Some reasonable character limit
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide answers

    CONSTRAINT [FK_PollResponse_PollOptionId_to_PollOption_PollOptionId] FOREIGN KEY ([PollOptionId]) REFERENCES [dbo].[PollOption]([PollOptionId]),
    CONSTRAINT [FK_PollResponse_UserId_to_User_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[User]([UserId])
)

    我还可能会添加一个检查约束，以确保提供选项或备用响应，但不能同时提供（选项和备用响应），具体取决于您的需要。

    编辑：为 AlternateResponse 通信数据类型。

    在一个完美的世界中，我们可以使用泛型的概念来处理 AlternateReponse 的各种数据类型。唉，我们不是生活在一个完美的世界里。我能想到的最佳折衷方案是在 PollQuestion 表中指定 AlternateResponse 数据类型，并将 AlternateReponse 作为 nvarchar 存储在数据库中。以下是更新后的问题架构和新的数据类型表：

    CREATE TABLE [dbo].[PollQuestion]
(
    [PollQuestionId] INT NOT NULL PRIMARY KEY IDENTITY,
    [QuestionText] NVARCHAR(150) NOT NULL, -- Some reasonable character limit
    [QuestionDataTypeId] INT NOT NULL,
    [Created] DATETIME2(2) NOT NULL DEFAULT SYSUTCDATETIME(),
    [Archived] DATETIME2(2) NULL,  -- Remove this if you don't need to hide questions
    -- Insert FK here for QuestionDataTypeId
)
CREATE TABLE [dbo].[QuestionDataType]
(
    [QuestionDataTypeId] INT NOT NULL PRIMARY KEY IDENTITY,
    [Description] NVARCHAR(50) NOT NULL, -- Some reasonable character limit
)

    您可以通过从此 QuestionDataType 表中选择来列出问题创建者的所有可用数据类型。您的 UI 可以引用 QuestionDataTypeId 来为备用响应字段选择正确的格式。您不仅限于 TSQL 数据类型，因此“电话号码”可以是一种数据类型，您将在 UI 上获得适当的格式/屏蔽。此外，如果需要，您可以通过简单的 case 语句将数据转换为适当的类型，以便对备用答案进行任何类型的处理（选择、验证等）。

    • 4
  2. Best Answer

    这实际上取决于您的前端如何访问数据。

    如果您使用的是 O/R-mapper，请关注类的面向对象设计，而不是数据库设计。然后数据库只是反映类设计。确切的数据库设计取决于您使用的 O/R 映射器和继承映射模型。

    如果您通过记录集、数据表、数据读取器等直接访问表，那么简单的事情就是使用不变的文化将值转换为字符串并将其存储在简单的文本列中. 当然，为了在读取值时将文本转换回专门的值类型，再次使用相同的文化。

    或者，您可以为每个值类型使用一列。我们今天有 TB 驱动器！

    XML 列是可能的，但与简单的文本列相比可能会增加更多的复杂性，并且做几乎相同的事情，即序列化/反序列化。

    分离的连接表是正确的规范化做事方式；但是，它们也增加了相当多的复杂性。

    把事情简单化。

    另请参阅我对问卷数据库设计的回答 - 哪种方式更好？.

    • 2

  3. 看看EAV 到底有什么不好？由 Aaron Bertrand 提供有关 EAV 模型的一些信息。

    以多种方式为每种数据类型设置一列而不是使用 XML 或多个表可能会更好。

    约束部分很简单：

    CHECK 
(
    CASE WHEN col1 IS NOT NULL THEN 1 ELSE 0 END + 
    CASE WHEN col2 IS NOT NULL THEN 1 ELSE 0 END + 
    CASE WHEN col3 IS NOT NULL THEN 1 ELSE 0 END = 1
)

    该站点上有许多现有的问题和答案，标记，并且可能还有其他提问者不知道在他们的问题中使用该术语的问题和答案。

    我强烈建议通读这些内容，因为它们可能涵盖所有优点和缺点（这可以防止人们在这里重新散列它们，而实际上它们并没有改变）。

    根据Aaron Bertrand留下的问题评论回答

    • 0

  4. 我认为这个问题被考虑得太多，或者为什么某些答案可能比其他答案更容易接受还有一些额外的限制。目前似乎没有证据表明数据库必须以任何方式处理答案，而只是作为日志字段。

    我会使用 NVARCHAR(MAX)，然后让前端处理存储/检索内容。可能是一个 IS_CORRECT 位字段，如果答案正确，前端可以在其中存储。

    • -2

相关问题