473,329 Members | 1,261 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,329 software developers and data experts.

Entity Relationship Management Idea

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
0 1399

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Per | last post by:
Hi, I'm struggling with the DocBook XML DTD, which can be found at http://www.oasis-open.org/docbook/xml/4.2/docbookx.dtd. But this DTD seems to somehow include some other documents. E.g.: ...
2
by: Balaji | last post by:
Hi I have three database tables - Location, Part and Detail. Location and Part have a one-many relationship to detail. Location and Part need to be inserted into the database before inserting a...
2
by: Emily Jones | last post by:
That subject line will have Joe Celko apoplectic!! Let's say I have this thing. Oh, I don't know, a car. No, a vehicle. Can we agree that is an entity type? Some attributes might be VIN, Fuel,...
11
by: Arsen Vladimirskiy | last post by:
Hello, If I have a few simple classes to represent Entities such as Customers and Orders. What is the proper way to pass information to the Data Access Layer? 1) Pass the actual ENTITY to...
2
by: uarana | last post by:
Hi All, I've been plugging away at this problem for a while now and was wondering if anyone had an idea they could share. I'm working on a document management database that tracks revisions of...
12
by: blangela | last post by:
Someone has asked me what the relationship is, if any, between iostream and the STL. My suspicion is that iostream uses some of the functionality provided by the STL, but I have no actual kowledge...
18
by: R. P. | last post by:
I wonder how to indicate in a stylesheet that character entities in an element are not to be transformed as would be the case in XML-to-XML transforms. I want to keep those & " and other character...
3
by: chris.kennedy | last post by:
I am really struggling to create an entity which maps to 2 "base" entities. I have a customer and invoice entity which are based in tables in a one to many relationship. I have tried: 1....
4
by: csolomon | last post by:
Hello: I am building something to keep track of vacation time per department per employee. The way we keep track of it is a department will get 4 hours every 30 days they go with out an...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.