How to model data entities with multiple parents?

Question

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.

Answer 1

Yes you can do this. You need to introduce another table call EntityParentRelation as structured bellow

EntityParentRelation
--------------------------------------------------------------------------------
ID                           bigint                  identity(1,1)   pk   
EntityID                     bigint                  not null        fk
ParentID                     bigint                  not null        fk

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 :).