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.