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

You may design a table with the following structure,

person1, relation, person2

now when inserted values into it, for example, if john is husband of kelly, then

john, is husband of, kelly

and to apply same for kelly

kelly, is wife of, john

You will need to define relationship for both persons, but it will yield good result while fetching.

Answer 3

I ran into this situation recently and after trying out a couple different options ended up with something like this (pardon the pseudo-code model):

class Person {
    int Id;
    List<RelationshipMember> Relationships;
}

class RelationshipMember {
    int Id;
    Person RelatedPerson;
}

class Relationship {
    int Id;
    List<RelationShipMember> RelationshipMembers;
}

You can put properties on Relationship to model it's type and properties on the RelationshipMember to model the role within the relationship if that's required.

And of course, this allows for threesomes, too. :)

On this particular project, I'm using an ORM tool (nHibernate with Fluent Automapping), here's how the database tables are expressed:

TABLE Person (
   Id int NOT NULL
)

TABLE Relationship (
   Id int NOT NULL
)

TABLE RelationshipMember(
Id int NOT NULL,
Relationship_id int NOT NULL,
    Person_id int NOT NULL
)

Answer 4

make sure you include dates in the link table. since a relationship does not last forever...

**person**
person_id
name

**person_person**
person_id_1
person_id_2
relationship_type_id
begin_date
end_date

**relationship_type**
relationship_type_id
name

Answer 5

@bill K :

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

Does the solution you first suggested (one table per relationship type) NOT suffer from that very same problem ?

BTW your term ("reciprocal") is incorrect, imo. You are talking of relations (mathematical sense) that have the property of being symmetric. An area that theory leaves answered only very unsatisfactorily, as far as I know.

The three-column option is how it was done in my very first project, almost 30 years ago, and I believe it still is the best approach possible. Especially since "the possible/relevant set of inter-persons relationship types" are, eurhm, a rather volatile kind of thing in any business I can imagine.