What are appropriate ways to represent relationships between people in a database table?

Question

I've got a table of people - an ID primary key and a name. In my application, people can have 0 or more real-world relationships with other people, so Jack might "work for" Jane and Tom might "replace" Tony and Bob might "be an employee of" Rob and Bob might also "be married to" Mary.

What's the best way to represent this in the database? A many to many intersect table? A series of self joins? A relationship table with one row per relationship pair and type, where I insert records for the relationship in both directions?

Answer 1

Create a separate many-to-many table for each type of relationship.

If you try to represent multiple types of relationships in a single many-to-many table, that's a violation of Fourth Normal Form.

---

Re comments:

Actually the violation of 4NF would be something like this:

Person1 Person2 Is_Employer Is_Teacher Is_Father
Tom     John     No          No         Yes

If you have a three-column table that lists two people and a relationship type, it's better, but you still have a problem with reciprocal relationships.

Person1 Person2  Rel_type
John     Ann     married

Some people get confused about whether to store two rows, or else store the two people in some kind of consistent order (e.g. lower ID value first). But then there are relationships that are directed, like "employer" where the order means something. And there are relationships with multiple people, like "siblings."

So another way to organize these relationships would be to create a table listing groups, one group per row, and then another table listing people in that group.

Group Rel_type    Group Person
123   siblings    123   Bobby
                  123   Peter
                  123   Greg
                  123   Cindy
                  123   Jan
                  123   Marsha

This works best for relationships that have variable numbers of members, and are reciprocal relationships. Members of a sports team is another example. It's essentially a many-to-many table between the group and the people.

You may need multiple ways to store relationships, to account for all the different types.

Answer 2

I know it's an old thread but still relevant.

Let's say Mary and John are married and have two children, Jane and Matt...

What about this table structure:

side1    | side1type    | side2type    | side2
----------------------------------------------------
Mary     | wife         | husband      | John
Jane     | child        | mother       | Mary
Jane     | child        | father       | John
Matt     | child        | mother       | Mary
Matt     | child        | father       | John 
Jane     | sister       | brother      | Matt

When we are interested to find one person relatives we could run 2 queries looking for that person in column side1 and then in column side2...

Or maybe one query looking for that person in one or another column, than we use logic in our application and:

If that person has been found in side1 column 
   we print side1, side1type, "of ", side2  

Mary is wife of John

If that person has been found in side2 column 
   we print side2, side2type, "of ", side1  

Mary is mother of Jane
Mary is mother of Matt

Or maybe more elegant...

If that person has been found in side1 column 
   we print side2 (side2type)  

John (husband)

If that person has been found in side2 column 
   we print side1 (side1type)  

Jane (child)
Matt (child)