CODEWITHSUNDEEP
sqldatabasedatabase-designdata-modeling
user4903
user4903

Reputation:

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 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.

Upvotes: 1

Views: 1136

Answers (3)

OMG Ponies
OMG Ponies

Reputation: 332521

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

Upvotes: 6

slugster
slugster

Reputation: 49974

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.

Upvotes: 0

Rohan West
Rohan West

Reputation: 9298

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.

Upvotes: 0

Related Questions