I'm not sure how best to phrase the question, but essentially I have a table of contacts, and instead of doing the typical -- a contact has a reference to a table with spouse information, and a table with children, I want each of those people to be a contact, but then define a relationship between those contacts (brother, sister, child, spouse, etc.). So the contacts would exist in a single table, but I'm having trouble determining how best to define the relationship based upon their contact id and the relationship type. Any advice would be appreciated.
views:
83answers:
3
A:
This is called a self join, it is pretty common and fairly easy to provide the functionallity you mention above. Take a look at this article.
Rohan West
2009-11-23 04:57:39
A:
Just implement an intersect table with four columns - key, contactid #1, contact id#2, and relationship.
Why do it this way? Because a contact can have several relationships.
slugster
2009-11-23 04:57:51
KenFar
2009-12-08 03:08:23
+5
A:
CONTACTS table
contact_id, pk
CONTACT_RELATIONSHIP_TYPE_CODE table
contact_relationship_type_code, pkdescription
CONTACTS_RELATIONS table
parent_contact_id, pk, foreign key toCONTACTStablechild_contact_id, pk, foreign key toCONTACTStablecontact_relationship_type_code, foreign key toCONTACT_RELATIONSHIP_TYPE_CODEtable
If you see the need to support multiple relationship types to a pair of people, add the CONTACTS_RELATIONS.contact_relationship_type_code column to the composite primary key
OMG Ponies
2009-11-23 05:30:09
Careful not to over-normalize this data. You could just have a contact_relations table with a relationship_code field.
Chris Kaminski
2009-11-23 05:32:54
I would have made the PK of the contacts table compound: parent_contact_id, contact_id, relationship_type_code. These three ought to be unique. Also, there's a little wrinkle in the table. For symmetric relationships such as siblings, there have to be two entries in the table, or else your queries have to be more complicated than necessary.
Walter Mitty
2009-11-23 12:51:12
@OMG Ponies: If I understand the CONTACTS_RELATIONS table correctly, the parent_contact_id is the contact_id of one individual in the relationship, while the contact_id is the contact_id (in CONTACTS) of the second individual in the relationship. Is that correct? Also, why make those primary keys? Why not a contacts_relations_id as a PK and AI, and then index all three? Just curious.
hal10001
2009-11-23 14:25:43
My mistake. The PK in CONTACTS_RELATIONSHIP is a compound key. I misread the solution.@hal10001, the primary reason for declaring a primary key is to ensure that each primary key is unique and that no part of a primary key is missing. You get these benefits if you declare a compound primary key for a relationship. A lot of designers always add a simple PK as the first column of every table. My experience says that this pattern usually costs more than it's worth, except for entity tables. But other people evidently have other opinions.
Walter Mitty
2009-11-23 16:08:46