如何对具有多个父项的数据实体进行建模?
我如何对多个实体之间的关系进行建模,其中一个实体可以是两个独立的、不相关的层次结构的一部分,并且每个实体可以以非层次结构的方式与 1 个或多个其他实体相关?我只想在数据库中的 2 或 3 个表中执行此操作。
我目前将其建模为两个表:
Entities
----------------------------------------------------------------
ID bigint identity(1, 1) PK
ParentID bigint null FK
Name varchar(100) not null
Description varchar(256) null
EntityRelationships
----------------------------------------------------------------
LEntityID bigint not null PK, FK
REntityID bigint not null PK, FK
EntityRelationshipTypeID int not null PK, FK
两列 LEntityID 和 REntityID 是 Entities.ID 列的 FK,而 ParentID 是 ID 列的 FK。只要一个实体永远不会有多个父实体,这个模型就可能工作得很好。我需要能够允许一个实体拥有多个父级。
表中的自然键是:
Entities: ParentID, Name
EntityRelationships: LEntityID, REntityID, EntityRelationshipTypeID
是的,两个实体可以以两种或多种不同类型的关系相互关联。
感谢您的帮助。
How would I model a relationship between multiple entities where one entity could be part of two separate, unrelated, hierarchies and each entity could be related to 1 or more other entities in a non-hierarchical fashion? I would like to do this in only 2 or 3 tables in the database.
I currently have it modeled into two tables:
Entities
----------------------------------------------------------------
ID bigint identity(1, 1) PK
ParentID bigint null FK
Name varchar(100) not null
Description varchar(256) null
EntityRelationships
----------------------------------------------------------------
LEntityID bigint not null PK, FK
REntityID bigint not null PK, FK
EntityRelationshipTypeID int not null PK, FK
The two columns, LEntityID and REntityID are FKs to the Entities.ID column and the ParentID is an FK to the ID column. This model would probably work fine, as long as an entity could never have more than one parent. I need to be able to allow an entity to have more than one parent.
The natural keys on the tables are:
Entities: ParentID, Name
EntityRelationships: LEntityID, REntityID, EntityRelationshipTypeID
Yes, two entities could be related to one another in two or more different types of relationships.
Thank you for any help.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
是的,你可以这样做。您需要引入另一个表,称为 EntityParentRelation,其结构如下
,其中 EntityID 和 ParentID 都是 Entities.ID 的 fks。并从实体中删除 ParentID。
对 EntityRelationships 中的关系进行一点修改,
不再将 fk 指向实体,您需要将 LEntityID 和 REntittyID 的 fk 指向 EntityParentRelation.ID。
希望它会有所帮助:)。
Yes you can do this. You need to introduce another table call EntityParentRelation as structured bellow
where both EntityID and ParentID are fks to Entities.ID. And remove ParentID from Entities.
And a little modification on relationship in EntityRelationships
Instaead of pointing fk to Entities, you need to point fk to EntityParentRelation.ID for LEntityID and REntittyID.
Hope it will help :).