创建一个可以具有级联子父关系的 SQL 表 [英] Create a SQL table that can have cascading child parent relationships

问题描述

我有一组可以有子实体的实体，这些子实体本身有子实体，它们有子实体......等等.问题是实体之间后续子实体的数量不是恒定的.

I have a set of entities that can have child entities which themselves have child entities and they have child entities .... etc. The problem is that the number of subsequent child entities is not constant between entities.

示例:

汽车有一个引擎，引擎有多个部件，可能由多个部件组成，而部件可能有更小的部件

Car has an engine, the engine have multiple components which may comprise multiple parts and the parts my have smaller parts

如上所述，例如火车和汽车之间级联的后续子实体的数量是不同的，因此我无法为每个父实体创建不确定的子表.

As stated above, the number of subsequent child entities cascading down is different for instance between a Train and a Car, so I cannot create indefinite child tables for each parent.

我的问题是，存储此类数据并维护每个父级和后续子级之间关系的最有效方法是什么?一个例子将不胜感激.

My question is, what is the most efficient way to store such data and maintain a relationship between each parent and subsequent child? An example will be much appreciated.

推荐答案

最简单的数据模型是这样的树:

The simplest data model would be a tree like this:

TABLE Entity (Id, Name, EntityTypeId, ParentEntityId NULL)
TABLE EntityType (Id, Name, ParentEntityTypeId NULL)

在更复杂的模型中，例如不同的汽车型号共享相同的电机型号(不清楚您是在谈论汽车型号还是特定汽车)，ParentEntityId 列将替换为关系表:

In a more complex model, e.g. different car models sharing the same motor model (and it's not clear whether you are talking about car models, or specific cars), the ParentEntityId column would be replaced by a relation table:

TABLE Entity (Id, Name, EntityTypeId)
TABLE EntityHierarchy (Id, ParentEntityId, ChildEntityId)

EntityHierarchy 表中的条目将受到 EntityTypes 树的约束(在应用程序级别)

The entries in the EntityHierarchy table would be constrained (on application level) by the tree of EntityTypes

在更复杂的模型中，ParentEntityTypeId 也将替换为层次结构表.

In a more complex model, the ParentEntityTypeId would also be replaced by a hierarchy table.

如果您的实体或其类型随时间发生变化，您还可以向这些表中的任何一个添加 DATE 范围.

If your entities or their types change over time, you would also add a DATE range to any of these tables.

这篇关于创建一个可以具有级联子父关系的 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！