sql - 如何建模以下数据结构？

Question

抱歉，这个问题有点抽象，因此有点难以定义，所以我可能需要多次编辑问题以澄清:

我有一个需要解析的配置文件，其中每个相关行都包含以下格式之一:

FieldName = Value
FieldName(Index) = Value
FieldName(Index1, Index2) = Value
FieldName(Index1, Index2, ...IndexN) = Value

例如:

Field0 = 0
Field1(0, 0) = 0.01
Field1(0, 1) = 0.02
Field1(1, 0) = 0.03
Field1(1, 1) = 0.04
Field1(2, 0) = ADF0102BC5
Field1(2, 1) = ADF0102BC6
Field2(0, 0) = 0
Field2(0, 1) = 2
Field3(1) = 5
Field3(2) = 7
Field3(3) = 9
Field4(0, 0, 1) = 64.75
Field4(0, 1, 0) = 65.25
Field4(1, 0, 0) = 72.25

相关的行很简单，可以使用正则表达式从文件中解析，我已经处理了这一点。我遇到的问题是如何对数据库中的数据建模，以便在新索引进入字段范围时，可以自动添加它，而无需将新列添加到表中。

FieldName 始终是最大长度为 50 的 Varchar

Value 始终是一个以需要的多种字符串格式之一表示的数值
单独解析和为了这个问题的目的在很大程度上是无关紧要的。

每个索引(如果一个字段有它们)都是一个整数值。每个都有其自身的含义，但一起用作一组值到字段名称的映射。

字段名的每个实例，即 Field1 将具有恒定数量的索引，即您永远不会有 Field1(0, 0) 和 Field1(0, 0, 0)。如果 Field1 在配置文件的一行中有 2 个索引，则 Field1 的所有实例都将有 2 个索引。

我需要系统足够灵活以解析文件并根据需要为每个字段附加尽可能多的索引。

我有 2 个想法 - 我是否将“方程”的整个左侧视为标签，因此 Field1(0, 0) 成为“FieldName”，这使得按索引查询非常困难，或者我是否对我的数据进行建模以便这些索引有效地成为字段值的坐标？

如果索引在所有文件中保持不变，我可以使用以下方法对其进行建模:

Table Fields(
    FieldId Integer Identity(1, 1) Primary Key,
    FieldName VarChar(50)
)

Table FieldValues(
    FieldId Integer Constraint FK_FV_FID Foreign Key References Fields(FieldId)
    Index1 Integer
    Index2 Integer
    Index3 Integer
    Index4 Integer
    Value  Varchar(50)
)

不幸的是，由于在解析文件之前索引的数量未知，这使得建模该关系变得更加复杂。

存储数据后，我需要能够简单地使用字段名进行查询，以获取所有相应索引引用及其值的列表，即

Field1
------
0, 0 = 0.01
0, 1 = 0.02
1, 0 = 0.03
1, 1 = 0.04
2, 0 = ADF0102BC5
2, 1 = ADF0102BC6

或者

Field1 Where Index1 = 0
-----------------------
0, 0 = 0.01
0, 1 = 0.02

或者

Field1 Where Index 2 = 1
------------------------
0, 1 = 0.02
1, 1 = 0.04
2, 1 = ADF0102BC6

或者

Field1 Where Index1 = 0 And Index2 = 1
--------------------------------------
0, 1 = 0.02

如果我有一个复杂的表结构，它会使简化查询更加麻烦。

Answer 1

这是我对这种情况的思考过程，
将有两种主要的查询。一种结果不是由 IndexPostion 和/或 IndexValue 切片的。和第二个结果被他们分割的地方。

没有任何单一的表格设计可以给我这样的结果而无需任何权衡。权衡可能是存储、性能或查询复杂性。

下面的解决方案是“放手存储”，但在访问此架构时会注意性能和查询的简单性。

对于第一类查询，将仅使用表“SO_FieldIndexValue”。

但是对于第二种类型的查询，我们需要将它与其他两种类型的查询连接起来，我们需要由 IndexPosition/IndexPositionValue 过滤的结果。

    IF OBJECT_ID('SO_FieldIndexPositionValue') IS NOT NULL 
        DROP TABLE SO_FieldIndexPositionValue
    IF OBJECT_ID('SO_FieldIndexValue') IS NOT NULL 
        DROP TABLE SO_FieldIndexValue
    IF OBJECT_ID('SO_IndexPositionValue') IS NOT NULL 
        DROP TABLE SO_IndexPositionValue

    CREATE TABLE SO_FieldIndexValue
        (
          FIV_ID        BIGINT NOT NULL IDENTITY
            CONSTRAINT XPK_SO_FieldIndexValue PRIMARY KEY NONCLUSTERED
          ,FieldName    NVARCHAR(50)NOT NULL
          ,FieldIndex   NVARCHAR(10) NOT NULL
          ,FieldValue   NVARCHAR(500) NULL
        )
    CREATE UNIQUE CLUSTERED INDEX CIDX_SO_FieldIndexValue
    ON SO_FieldIndexValue(FIV_ID ASC,FieldName ASC,FieldIndex ASC)
    CREATE NONCLUSTERED INDEX NCIDX_SO_FieldIndexValue
    ON SO_FieldIndexValue (FIV_ID,FieldName) 
    INCLUDE (FieldIndex,FieldValue)

    CREATE TABLE SO_IndexPositionValue
        (
            IPV_ID              BIGINT  NOT NULL IDENTITY
                CONSTRAINT XPK_SO_IndexPositionValue PRIMARY KEY NONCLUSTERED
            ,IndexName          SYSNAME NOT NULL
            ,IndexPosition      INT     NOT NULL
            ,IndexPositionValue BIGINT  NOT NULL
        )
    CREATE UNIQUE CLUSTERED INDEX CIDX_SO_IndexPositionValue 
    ON SO_IndexPositionValue(IPV_ID ASC,IndexPosition ASC, IndexPositionValue ASC)

    CREATE TABLE SO_FieldIndexPositionValue
        (
          FIPV_ID       BIGINT NOT NULL IDENTITY
                CONSTRAINT XPK_SO_FieldIndexPositionValue PRIMARY KEY NONCLUSTERED
          ,FIV_ID           BIGINT NOT NULL REFERENCES SO_FieldIndexValue (FIV_ID)
          ,IPV_ID       BIGINT NOT NULL REFERENCES SO_IndexPositionValue (IPV_ID)
        )
    CREATE CLUSTERED INDEX CIDX_SO_FieldIndexPositionValue 
    ON SO_FieldIndexPositionValue(FIPV_ID ASC,FIV_ID ASC,IPV_ID ASC)

我提供了一个简单的 SQL API 来演示如何使用单个 API 轻松处理插入到这个模式中。

有很多机会可以使用此 API 并根据需要进行自定义。例如，如果输入格式正确，则添加验证。

    IF object_id('pr_FiledValueInsert','p') IS NOT NULL
        DROP PROCEDURE pr_FiledValueInsert
    GO
    CREATE PROCEDURE pr_FiledValueInsert
    (
        @FieldIndexValue    NVARCHAR(MAX)
        ,@FieldValue        NVARCHAR(MAX)=NULL
    )
    AS
    BEGIN
    SET NOCOUNT ON
    BEGIN TRY
    BEGIN TRAN
            DECLARE @OriginalFiledIndex NVARCHAR(MAX)=@FieldIndexValue
            DECLARE @FieldName              sysname=''
                    ,@FIV_ID                BIGINT
                    ,@FieldIndex            sysname
                    ,@IndexName             sysname
                    ,@IndexPosition         BIGINT
                    ,@IndexPositionValue    BIGINT
                    ,@IPV_ID                BIGINT
                    ,@FIPV_ID               BIGINT
                    ,@CharIndex1            BIGINT
                    ,@CharIndex2            BIGINT
                    ,@StrLen                BIGINT
                    ,@StartPos              BIGINT
                    ,@EndPos                BIGINT

            SET @CharIndex1 = CHARINDEX('(',@OriginalFiledIndex)
            SET @StrLen     = LEN(@OriginalFiledIndex)
            SET @CharIndex2 = CHARINDEX(')',@OriginalFiledIndex)
            SET @FieldName  = RTRIM(LTRIM(SUBSTRING(@OriginalFiledIndex,1,@CharIndex1-1)))
            SET @FieldIndex = RTRIM(LTRIM(SUBSTRING(@OriginalFiledIndex,@CharIndex1+1,@StrLen-@CharIndex1-1)))


            --Insert FieldIndexValue and Get @FIV_ID
            SELECT @FIV_ID = FIV_ID 
            FROM SO_FieldIndexValue 
            WHERE FieldName=@FieldName
            AND FieldIndex=@FieldIndex
            IF @FIV_ID IS NULL
            BEGIN
                INSERT INTO SO_FieldIndexValue ( FieldName,FieldIndex,FieldValue )
                SELECT @FieldName,@FieldIndex,@FieldValue
                SELECT @FIV_ID = SCOPE_IDENTITY()
            END
            ELSE
            BEGIN
                RAISERROR('Filed and Index Combination already Exists',16,1)
            END


            --Find the First IndexPosition and IndexPositionValue and Get @IPV_ID
            SELECT @StartPos=CHARINDEX('(',@OriginalFiledIndex,1)+1
            SELECT @EndPos = CASE   WHEN CHARINDEX(',',@OriginalFiledIndex,@StartPos)<>0
                                    THEN  CHARINDEX(',',@OriginalFiledIndex,@StartPos)- @StartPos
                                    ELSE CHARINDEX(')',@OriginalFiledIndex,@StartPos) - @StartPos
                                END
            SELECT @IndexPosition = 1
            SELECT @IndexPositionValue = SUBSTRING(@OriginalFiledIndex,@StartPos,@EndPos)
            SELECT @IndexName = 'Index'+CAST(@IndexPosition AS Sysname)

            --Insert IndexPositionvalue
            SELECT @IPV_ID = IPV_ID
            FROM SO_IndexPositionValue
            WHERE IndexPosition=@IndexPosition
            AND IndexPositionValue = @IndexPositionValue
            IF @IPV_ID IS NULL
            BEGIN
                INSERT SO_IndexPositionValue
                        ( IndexName ,
                          IndexPosition ,
                          IndexPositionValue
                        )
                SELECT @IndexName,@IndexPosition,@IndexPositionValue
                SET @IPV_ID = SCOPE_IDENTITY()          
            END

            --Insert the First FieldIndexPositionValue
            IF NOT EXISTS(
                            SELECT TOP(1) 1 
                            FROM SO_FieldIndexPositionValue
                            WHERE FIV_ID = @FIV_ID
                            AND IPV_ID = @IPV_ID
                        )
            BEGIN
                INSERT SO_FieldIndexPositionValue( FIV_ID, IPV_ID )
                SELECT @FIV_ID,@IPV_ID
            END

            --If More than One Index exist, process remining indexpositions
            WHILE @StrLen>@StartPos+@EndPos
            BEGIN           
                SET @StartPos = @StartPos+@EndPos+1
                SET @EndPos = CASE WHEN CHARINDEX(',',@OriginalFiledIndex,@StartPos)<>0
                                    THEN  CHARINDEX(',',@OriginalFiledIndex,@StartPos)- @StartPos
                                    ELSE CHARINDEX(')',@OriginalFiledIndex,@StartPos) - @StartPos
                                END

                SELECT @IndexPosition = @IndexPosition+1
                SELECT @IndexPositionValue = SUBSTRING(@OriginalFiledIndex,@StartPos,@EndPos)
                SELECT @IndexName = 'Index'+CAST(@IndexPosition AS Sysname)

                --Insert IndexPositionvalue
                SET @IPV_ID = NULL
                SELECT @IPV_ID = IPV_ID
                FROM SO_IndexPositionValue
                WHERE IndexPosition=@IndexPosition
                AND IndexPositionValue = @IndexPositionValue
                IF @IPV_ID IS NULL
                BEGIN
                    INSERT SO_IndexPositionValue
                            ( IndexName ,
                              IndexPosition ,
                              IndexPositionValue
                            )
                    SELECT @IndexName,@IndexPosition,@IndexPositionValue
                    SET @IPV_ID = SCOPE_IDENTITY()
                END

                --Insert FieldIndexPositionValue
                IF NOT EXISTS(
                                SELECT TOP(1) 1 
                                FROM SO_FieldIndexPositionValue
                                WHERE FIV_ID = @FIV_ID
                                AND IPV_ID = @IPV_ID
                            )
                BEGIN
                    INSERT SO_FieldIndexPositionValue( FIV_ID, IPV_ID )
                    SELECT @FIV_ID,@IPV_ID
                END
            END
    COMMIT TRAN
    END TRY
    BEGIN CATCH
        ROLLBACK TRAN
        SELECT ERROR_MESSAGE()
    END CATCH
    SET NOCOUNT OFF
    END
    GO

现在采样输入数据

    EXECUTE pr_FiledValueInsert 'FIELD1(0,1,0)',101
    EXECUTE pr_FiledValueInsert 'FIELD1(0,1,2)','ABCDEF'
    EXECUTE pr_FiledValueInsert 'FIELD1(1,0,1)','hello1'

    EXECUTE pr_FiledValueInsert 'FIELD2(1,0,0)',102
    EXECUTE pr_FiledValueInsert 'FIELD2(1,1,0)','hey2'
    EXECUTE pr_FiledValueInsert 'FIELD2(1,0,1)','hello2'

示例查询 1

    SELECT FieldName,FieldIndex,FieldValue 
    FROM dbo.SO_FieldIndexValue
    WHERE FieldName = 'Field1'

样本结果 1



示例查询 2

    SELECT FieldName,FieldIndex AS CompeleteIndex,IndexPosition,IndexPositionValue,FieldValue
    FROM SO_FieldIndexPositionValue fipv
    JOIN dbo.SO_IndexPositionValue ipv
        ON ipv.IPV_ID=fipv.IPV_ID
    JOIN dbo.SO_FieldIndexValue fiv
        ON fiv.FIV_ID=fipv.FIV_ID
    WHERE
    (IndexPosition=2 AND IndexPositionValue=1)
    AND FieldName = 'Field1'

样本结果 2