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

问题描述

我有一个关于表设计的问题。我有一个解决方案，应该在我看来工作，但不是。

I have a question concerning table design. I have a solution that should work in my opinion but doesn't.

考虑有两个实体主题和过程，都有某些属性。每个主题可以与多个过程相关联。根据选择的过程，存在不同数量的实体过程属性。换句话说，当用户将过程与主题关联时，他应该只能编辑与其特定关联的属性。

Consider having two Entities "Subject" and "Process", both with certain attributes. Every "Subject" may be associated with multiple "Processes". Depending on which "Process" is chosen there is a varying number of the entity "Process-Property". In other words when the User associates a "process" with a "subject" he should only be able to edit the "properties" specifically linked to it.

用户能够做3件事情：

1. 创建新的进程并指定与其相关的属性
   
2. 列出某个主题的所有进程，即使没有链接到它的属性。


3. 将进程并且只允许评估预定义的属性

所以表设计应该是这样：

So the table design should be something like:

• tblSubject = {SubjectID，...}


• tblProcess = {ProcessID，...}


• tblProcessProperty = {PropertyID，...}


• tblRelationProcessProperty = {RelationProcessPropertyID，ProcessID，PropertyID}


• tblRelationSubjectProcessProperty = {RelationID， RelationProcessPropertyID，SubjectID，PropertyValue}

• tblSubject={SubjectID,...}
• tblProcess={ProcessID,...}
• tblProcessProperty={PropertyID,...}
• tblRelationProcessProperty={RelationProcessPropertyID, ProcessID, PropertyID}
• tblRelationSubjectProcessProperty={RelationID, RelationProcessPropertyID, SubjectID, PropertyValue}

这显然是有效的，只要有一个属性与每个进程相关联。所以我的错误是不直接链接主题到过程，但是我不能得到表设计直。

This does obviously work as long as there is a "Property" associated with every "Process". So my mistake is not to directly link "Subject" to "Process" but then I can't get the table design straight.

任何帮助是赞赏。提前感谢

Any help is appreciated. Thanks in advance

Jon

推荐答案

尝试实现一种EAV（Entity-Attribue-Value）设计。

It looks to me like your trying to implement a sort of EAV (Entity-Attribue-Value) design.

你的表似乎确定，但这个设计本质上需要复杂的SQL。

Your table seems ok, but this design inherently requires complicated SQL.

有不同的方法，但根据你的故事，我会去这样的东西。

There are different methods of doing this but based on your tales above I would go with something like this.

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

您的属性将如下所示：

    "Property"
    PK PropertyId
    Name

您的RelationshipProcessProperty可能如下所示：

Your RelationshipProcessProperty could look like this:

    "RelationshiipProcessProperty"
    PK RelationshipProcessProperty
    FK Process
    FK Property
    Value

它会变得复杂。像这样的通用设计，它是隐含的，因为你在同一个表中查找多个值。

Your SQL is where it would get complicated. Doing a 'generic' design like this has it's impliations as you're looking for multiple values in the same table.

    ; 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

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

You can use this method to get multiple properties for the same process.

属性，您需要创建流程类型表：

In order to have specified properties for a specified process, you would need to create Process Type Table:

    "ProcessType"
    PK ProcessType
    Type

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

And this does mean that you will need to add a foreign key to your process table to link it to which type it is. You can then link your ProcessType table to your Property table with a relationship table that defines all of the available types.

    "EligibleProcessProperties"
    PK EligibleprocessPropertiesId
    FK ProcessType
    Fk Property

将所有可用的属性传递给该进程类型，您将有一个相对简单的查询

Then to find out all of the available properties to that process type, you would have a relatively simple query

    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"

认为这是你正在寻找的东西（虽然我可以完全关闭）。如果这是您正在寻找的，有一个非常好的帖子，有一些好点。

I think that this is the sort of thing you're looking for (though i could be completely off). If this is what you're looking for, there's a really good post here that makes some good points.

此外，我几乎100％有更好的方法来做我的长';与'查询 - 但这是我所知道的。希望别人能提供一个更好的。关键是，使用这种设计，您会以这种方式需要子查询。

Also, I'm almost 100% there are better ways to do my long ';with' query - but this is all I know. Hopefully someone else can provide a better one. The point is that with this design, you will need sub-queries one way or another.

这篇关于具有多个嵌套表的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！