December 14, 2004 at 5:44 pm
Hi,
I am a bit confused about how to model a particular data structure, and am hoping someone out there can help!
I have a table of Contacts (each entry could either be a person or an organisation). I want to create a table to track the relationships between these contacts.
For example, Contact 1 is an employee of Contact 2, Contact 3 is a Customer of Contact 4 etc.
The relationship table could be modelled as follows...
tblRelationships
---------------
RelationshipID int IDENTITY (1,1)
Contact1ID int,
Contact2ID int,
RelationshipTypeID int
I guess this effectively creates a many-to-many, self-join on the Contacts table.
The trouble is, if Contact1 is an employee of Contact2 - Contact 2 is also the employer of Contact1 (if you get my meaning!).
So - to find all of the relationships for a given contact, you would either have to have 2 records for each relashionship, or do a UNION of 2 queries (one on the Contact1ID field and one on the Contact2ID field).
Neither of these solutions seem particularly 'nice'. Maintaining 2 records could be a nightmare - if I delete one, I need to make sure I delete the corresponding entry correctly etc. The UNION option seems like it could be problematic too.
Any other suggestions?
Thanks,
- Chris
December 15, 2004 at 1:43 am
Think of it as a parent/child relationship if that isn't too Freudian.
An employer is a parent the child is the employee.
The customer is the child of contact 4.
Your tbl_relationship table is correct but I would rename the fields parentId and childId. This has no technical benefit but does add clarity.
If you want to get all relationships for a particular contact then you do a simple union query
SELECT 'Parent' AS Relationship , C.*
FROM dbo.tblRelationship AS T INNER JOIN tblContact AS C
ON T.ParentId = C.ContactId
WHERE T.childId = @lContactId
UNION ALL
SELECT 'Child' AS Relationship , C.*
FROM dbo.tblRelationship AS T INNER JOIN tblContact AS C
ON T.ChildId = C.ContactId
WHERE T.parentId = @lContactId
If a pair of contacts can only have one relationship then the primary key will be parentId,ChildId but the key should be non-clustered.
You need a second index on childId.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply