SQL 数据库存储不同类型的值（在单个字段中或模拟为单个字段）

Question

在数据库中，我希望能够将不同类型的值分配给变量表中的变量。那么我是否需要为每种值类型准备一个单独的值表？如果是这样，我不确定您如何实际将值链接到正确的表，从而链接到正确的值。我怎样才能实现我所追求的目标？

Variables
    ID
    Name

VariableValuesLink
    ID
    IDVars
    IDVals

Values
    IDvals

ValuesValueLink
    ID
    IDvals
    IDval

ValuesInt
    IDval
    IntVal

ValuesFloat
    IDval
    FloatVal

ValuesDouble
    IDval
    DoubleVal

etc...
etc...
etc...
etc...

我的目标是得到这样的结果：

Variable: 
    ezas123
Values:
    1 (Int)
    2.0 (Float)
    3.0 (Double)

Variable:
    QuickFox
Values:
    The (TinyText)
    Quick (TinyText)
    Brown (TinyText)
    Fox (TinyText)
    Jumped (TinyText)
    Over (TinyText)
    The (TinyText)
    Lazy (TinyText)
    Dog (TinyText)

Variable:
    Pangrams
Values:
    The Quick Brown Fox Jumped Over The Lazy Dog (Text)
    How quickly daft jumping zebras vex (Text)

因此，当我查询数据库时，我将能够取回这组结果（其中值的类型不同）

Variable    Value
ezas123     1
ezas123     2.0
ezas123     3.0
QuickFox    The
QuickFox    Quick
QuickFox    Brown
QuickFox    Fox
QuickFox    Jumped
QuickFox    Over
QuickFox    The
QuickFox    Lazy
QuickFox    Dog
Pangrams    The Quick Brown Fox Jumped Over The Lazy Dog
Pangrams    How quickly daft jumping zebras vex

Answer 1

快点说，您可以通过这种方式简化您的设计——只需让每个值表指向变量表即可。不需要链接表。我能想到的链接表的唯一原因是如果您想要一种“更简单”的方式来跨所有变量类型进行序列。如果不需要，则使用下面的设计：

Variable
    ID
    Name

ValuesInt
    IDvariable
    IntVal

ValuesFloat
    IDvariable
    FloatVal

ValuesDouble
    IDvariable
    DoubleVal

etc...
etc...
etc...

您的 sql 如何简单：

select v.name as Variable,
       coalesce(cast(vi.IntVal as varchar(max)),
                cast(vf.FoatVal as varchar(max)),
                cast(vd.DoubleVal as varchar(max)),
                '') as Value
From Variable V
JOIN ValuesInt vi on V.ID = vi.IDvariable
JOIN ValuesFloat vf on V.ID = vf.IDvariable
JOIN ValuesDouble vd on V.ID = vd.IDvariable

Answer 2

几点：

• 在您的示例中，变量 ezas123 具有三个具有不同数据类型的值，这意味着变量本身实际上没有定义的数据类型。这可能会导致下游出现问题，并且可能表明数据的定义相当糟糕。我会考虑加入一个限制，即给定变量的所有值必须具有相同的数据类型。

• Hogan 的 SQL 查询指出，每当您按照请求的方式列出值（即跨具有不同数据类型的变量）时，您都必须将结果转换为 varchar 或类似的形式来显示它（因为您可以' t 在同一输出列中具有不同数据类型的值）。考虑到这一点，您真的需要不同的数据类型，还是 varchar 类型适合您正在处理的所有数据？

如果需要不同的类型，我会考虑将所有不同的 IntVal、FloatVal、DoubleVal 等列放入一张表中。然后，您的表定义可能类似于：

Variables
      ID          NOT NULL
     ,Name        NOT NULL
     ,DataType    NOT NULL CHECK (DataType IN ('INT','FLOAT','DOUBLE','TEXT'))  
   ,CONSTRAINT PK_Variables PRIMARY KEY (ID)
   ,CONSTRAINT UQ_Variables_1 UNIQUE (Name)
   ,CONSTRAINT UQ_Variables_2 UNIQUE  (ID,DataType)

    Values
      IDvals      NOT NULL
     ,ID          NOT NULL
     ,DataType    NOT NULL CHECK (DataType IN ('INT','FLOAT','DOUBLE','TEXT'))
     ,IntVal      NULL
     ,FloatVal    NULL
     ,DoubleVal   NULL
     ,TextVal     NULL
   ,CONSTRAINT PK_Values PRIMARY KEY (IDvals)
   ,CONSTRAINT FK_Values_Variable FOREIGN KEY (ID,DataType) REFERENCES Variables(ID,DataType)
   ,CONSTRAINT CH_Values CHECK ( NOT(DataType <> 'INT'    AND IntVal     IS NOT NULL)  AND
                                 NOT(DataType <> 'FLOAT'  AND FloatVal   IS NOT NULL)  AND
                                 NOT(DataType <> 'DOUBLE' AND DoubleVal  IS NOT NULL)  AND
                                 NOT(DataType <> 'TEXT'   AND TextVal    IS NOT NULL)
                                )

• 可能需要对变量（ID、数据类型）进行 UNIQUE 约束（DBMS？）以允许您使其成为 FK 的主题；
• CHECK 约束确保仅使用有效的数据类型，并且仅可以填充正确的值列；
• 在值和变量中包含 DataType 意味着可以使用 FK 和 CHECK 的组合来确保给定变量的所有值都具有相同的数据类型，而不必使用触发器或应用程序逻辑。

针对表的查询看起来像这样：

SELECT v.name as Variable,
       COALESCE(cast(a.IntVal       as varchar(max)),
                cast(a.FloatVal     as varchar(max)),
                cast(a.DoubleVal    as varchar(max)),
                cast(a.TextVal      as varchar(max)),
                '') as Value
FROM 
Variables V
JOIN Values a on V.ID = a.ID AND v.DataType = a.DataType

这也可以用基于 Variable.DataType 的 CASE 来编写（可能更正确），用于选择相关列。

将所有值放在一张表中意味着数据库中的表/约束/索引更少，并且意味着扩展解决方案以保存新数据类型仅意味着向值表添加新列（并修改约束），而不是添加新表。