Please let me know what you think of this scheme I have come up with
(not to imply that noone else has before me) for managing
relationships.
I have created an entities table with Individual and Organizational
subtypes. I need to be able to relate them to eachother (e.g., some
are customers of or suppliers to others, some have employer/employee
relationships,...). I know this is not an uncommon thing to do.
So, I have created relationship pairs with left and right values. The
pairs are things like employee/employer, customer/vendor,
contractor/client,... Then I can create relationships that will let
me look in two directions. For example, if I say entity1 has an
employee/employer relationship with entity 2, that means that entity1
is an employee of entity2 and entity2 is an employer of entity1.
Entities (This is just a view of the combined ind/org subtypes tables)
EntityID EntityName
1 Doe, John
2 MyCorp, Inc.
3 Smith, Jane
4 AnotherCorp, Inc.
RelationshipTypes
RelTypeID RelLValue RelRValue
1 Employee Employer
2 Customer Vendor
3 Client Contractor
Relationships
RelTypeID LEntityID REntityID
1 1 2
4 2 2
2 3 3
Then I can query for everyone that has a relationship with a specific
entity by using:
SELECT Entities.EntityName, RelationshipTypes.RelLValue AS
Relationship FROM Relationships
LEFT JOIN Entities ON Entities.EntityID = Relationships.LEntityID
LEFT JOIN RelationshipTypesON Relationships.RelTypeID =
RelationshipTypes.RelTypeID
WHERE REntityID = 2
UNION
SELECT Entities.EntityName, RelationshipTypes.RelRValue AS
Relationship FROM Relationships
LEFT JOIN Entities ON Entities.EntityID = Relationships.REntityID
LEFT JOIN RelationshipTypesON Relationships.RelTypeID =
RelationshipTypes.RelTypeID
WHERE LEntityID = 2
Returns:
EntityName Relationship (to EntityID = 2)
Doe, John Employee
Smith, Jane Contractor
AnotherCorp, Inc. Customer