views:

18

answers:

0

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.