By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,263 Members | 1,537 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,263 IT Pros & Developers. It's quick & easy.

Entity Relationship Management Idea

P: n/a
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
Jul 20 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.