How to design a circular reference to a single database table with an added relationship?

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 con开发者_运维百科tact, 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.

CONTACTS table

• contact_id, pk

CONTACT_RELATIONSHIP_TYPE_CODE table

• contact_relationship_type_code, pk
• description

CONTACTS_RELATIONS table

• parent_contact_id, pk, foreign key to CONTACTS table
• child_contact_id, pk, foreign key to CONTACTS table
• contact_relationship_type_code, foreign key to CONTACT_RELATIONSHIP_TYPE_CODE table

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

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.

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.