具有多个嵌套表的数据库设计

Question

我有一个关于桌子设计的问题。我有一个应该认为可行的解决方案，但是没有。

考虑具有两个实体“ Subject”和“
Process”，它们都具有某些属性。每个“主题”可以与多个“进程”相关联。根据选择哪个“过程”，存在不同数量的实体“过程属性”。换句话说，当用户将“过程”与“主题”相关联时，他应该只能编辑专门链接到该“过程”的“属性”。

最终，我希望用户能够执行3件事：

1. 创建新的“流程”并指定与其关联的“属性”
2. 列出某个“主题”的所有“过程”，即使没有“属性”链接到该“主题”
3. 将“过程”与“主题”相关联，并且仅允许评估预定义的“属性”

因此，表设计应类似于：

• tblSubject = {SubjectID，…}
• tblProcess = {ProcessID，…}
• tblProcessProperty = {PropertyID，…}
• tblRelationProcessProperty = {RelationProcessPropertyID，ProcessID，PropertyID}
• tblRelationSubjectProcessProperty = {RelationID，RelationProcessPropertyID，SubjectID，PropertyValue}

只要有一个与每个“过程”相关联的“属性”，这显然就可以工作。因此，我的错误是不直接将“主题”链接到“过程”，但是我无法直接进行表格设计。

任何帮助表示赞赏。

Answer 1

在我看来，就像您尝试实施一种EAV（实体属性值）设计一样。

您的表似乎还可以，但是这种设计固有地需要复杂的SQL。

有不同的方法可以执行此操作，但是根据您上面的故事，我会选择类似的方法。

   Subject --< Process --< RelationshipProcessProperty >-- Property

您的媒体资源看起来像这样：

    "Property"
    PK PropertyId
    Name

您的RelationshipProcessProperty可能如下所示：

    "RelationshiipProcessProperty"
    PK RelationshipProcessProperty
    FK Process
    FK Property
    Value

您的SQL会变得很复杂。当您要在同一张表中查找多个值时，进行这样的“通用”设计是有隐含的。

    ; with Property1 as(
    SELECT
    proc.Id as ProcessId,
    prop.Name,
    rrp.Value
    FROM Subject s
    LEFT JOIN Process proc
    ON s.SubjectId = proc.SubjectId
    LEFT JOIN RelationshipProcessProperty rpp
    on proc.ProcessId = rpp.ProcessId
    LEFT JOIN Property prop
    on rpp.PropertyId = prop.PropertyId
    WHERE
    s.Name = "Subject1"
    AND
    proc.Name = "Process1"
    AND
    prop.Name = "Property1"
    )

    , Property2 as(
    SELECT
    proc.Id as ProcessId,
    prop.Name,
    rrp.Value
    FROM Subject s
    LEFT JOIN Process proc
    ON s.SubjectId = proc.SubjectId
    LEFT JOIN RelationshipProcessProperty rpp
    on proc.ProcessId = rpp.ProcessId
    LEFT JOIN Property prop
    on rpp.PropertyId = prop.PropertyId
    WHERE
    s.Name = "Subject1"
    AND
    proc.Name = "Process1"
    AND
    prop.Name = "Property2"
    )

    SELECT
    p1.Name,
    p1.Value,
    p2.Name,
    p2.Value
    FROM
    Property1 p1
    LEFT JOIN Property2 p2
    on p1.ProcessId = p2.ProcessId

您可以使用此方法为同一过程获取多个属性。

为了具有指定流程的指定属性，您需要创建“流程类型表”：

    "ProcessType"
    PK ProcessType
    Type

这确实意味着您将需要在过程表中添加一个外键，以将其链接到它的类型。然后，您可以使用定义所有可用类型的关系表将ProcessType表链接到Property表。

    "EligibleProcessProperties"
    PK EligibleprocessPropertiesId
    FK ProcessType
    Fk Property

然后要找出该流程类型的所有可用属性，您将有一个相对简单的查询

    SELECT
    p.Name
    FROM
    ProcessType pt
    LEFT JOIN EligibleProcessProperties epp
    on pt.ProcessTypeId = epp.ProcessTypeId
    LEFT JOIN Property p
    on epp.PropertyId = p.PropertyId
    WHERE
    pt.Type = "Type1"

我 认为
这是您正在寻找的东西（尽管我可能会完全不在家）。如果这是您要寻找的内容，那么这里有一篇非常好的文章，提出了一些要点。

另外，我几乎100％都有更好的方法来执行长时间的“; with”查询-但这就是我所知道的。希望其他人可以提供更好的服务。关键是，使用这种设计，您 将
需要一种或另一种方式的子查询。