具有多个嵌套表的数据库设计 [英] Database design with multiple nested tables
问题描述
我有一个关于表设计的问题。我有一个解决方案，应该在我看来工作，但不是。
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件事情：
- 创建新的进程并指定与其相关的属性
- 列出某个主题的所有进程，即使没有链接到它的属性。
- 将进程并且只允许评估预定义的属性
所以表设计应该是这样：
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.
这篇关于具有多个嵌套表的数据库设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
