# Keeping track of people's relationships in MySQL using a people relation table?

I am trying to track relationships among people. I came up with my own solution, but wondering if there might be another way or better way of doing this. To keep it simplified, I'll post just the bare bones. So let's say I have created the tables: person, person_relation, and people_relation for a MYSQL database using the following code:

DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS person_relation;
DROP TABLE IF EXISTS people_relation;

CREATE TABLE person (
id            int(10) NOT NULL auto_increment primary key,
name          varchar(32) NOT NULL,
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE people_relation (
id            int(2) NOT NULL auto_increment primary key,
relation      varchar(32)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE person_relation (
id            int(10) NOT NULL auto_increment primary key,
parent_id     int(10) NOT NULL,
child_id      int(10) NOT NULL,
relation_id   int(10) NOT NULL,
FOREIGN KEY (parent_id) REFERENCES person(id),
FOREIGN KEY (child_id) REFERENCES person(id),
FOREIGN KEY (relation_id) REFERENCES people_relations(id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO person (id, name)
VALUES
(1, 'John Jr'), (2, 'Jane'), (3, 'Mark'), (4, 'John'), (5, 'Betty');

INSERT INTO people_relation (id, relation)
VALUES
(1, 'HUSBAND'), (2, 'WIFE'), (3, 'FATHER'), (4, 'MOTHER'), (5, 'SON'), (6, 'DAUGHTER'), (7, 'GRANDFATHER'),
(8, 'GRANDMOTHER'), (9, 'GRANDSON'), (10, 'GRANDDAUGHTER'), (11, 'GREAT GRANDFATHER'),
(12, 'GREAT GRANDMOTHER'), (13, 'GREAT GRANDSON'), (14, 'GREAT GRANDDAUGHTER'), (15, 'GODFATHER'),
(16, 'GODMOTHER'), (17, 'GODSON'), (18, 'GODDAUGHTER'), (19, 'BROTHER'), (20, 'SISTER'),
(21, 'MOTHER-IN-LAW'), (22, 'FATHER-IN-LAW'), (23, 'DAUGHTER-IN-LAW'), (24, 'SON-IN-LAW');

INSERT INTO person_relation (id, parent_id, child_id, relation_id)
VALUES
(1, 1, 2, 1), (2, 1, 3, 3), (3, 1, 4, 5), (4, 1, 5, 5), (5, 2, 1, 2), (6, 2, 3, 4), (7, 2, 4, 23),
(8, 2, 5, 23), (9, 3, 1, 5), (10, 3, 2, 5), (11, 3, 4, 9), (12, 3, 5, 9), (13, 4, 1, 3), (14, 4, 2, 22),
(15, 4, 3, 7), (16, 4, 5, 1), (17, 5, 1, 4), (18, 5, 2, 21), (19, 5, 3, 8), (20, 5, 4, 2);


So, in my example, I am capturing 5 people, all family related, where I make connections to each of them with a unique relation.. spoken plainly, a John Jr. has listed: a wife, a son, a mother, and a father; and Jane has a husband, a son, a father-in-law, and a mother-in-law, etc..

The records with 5 people end up being 20 records in the person_relation table. If I add, let's say a new daughter for John Jr. and Jane.. The table will then have presumably 30 records to account all the relations.

Does this sound about right? This problem seems like it could explode to be a very large table.. Do you know maybe a better way to do this? Or do you think I am on the right track?

-

It seems fine for me for generic cases. Later you might need some denormalization or caching if it's too slow but it really depends on the usage. (If you have any special requirement you should share it, edit the question, please.)

Four things to consider:

1. varchar(32) for name could be too small.

2. Consider unique indexes for the person_relation(parent_id, child_id, relation_id) triplet and the people_relation.relation attribute. For example:

Currently, you can insert the same relation twice:

mysql> select * FROM person_relation;
Empty set (0.00 sec)

mysql> INSERT INTO person_relation (parent_id, child_id, relation_id)
VALUES (1, 2, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO person_relation (parent_id, child_id, relation_id)
VALUES (1, 2, 1);
Query OK, 1 row affected (0.00 sec)

mysql> select * FROM person_relation;
+----+-----------+----------+-------------+
| id | parent_id | child_id | relation_id |
+----+-----------+----------+-------------+
| 23 |         1 |        2 |           1 |
| 24 |         1 |        2 |           1 |
+----+-----------+----------+-------------+
2 rows in set (0.00 sec)


An unique index could prevent that:

CREATE UNIQUE INDEX person_relation_uniq_idx
ON person_relation
(parent_id, child_id, relation_id);


For example:

mysql> DELETE FROM person_relation;
Query OK, 2 rows affected (0.00 sec)

mysql>         CREATE UNIQUE INDEX person_relation_uniq_idx
->             ON person_relation
->             (parent_id, child_id, relation_id);
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> INSERT INTO person_relation (parent_id, child_id, relation_id)
VALUES (1, 2, 1);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO person_relation (parent_id, child_id, relation_id)
VALUES (1, 2, 1);
ERROR 1062 (23000): Duplicate entry '1-2-1' for key 'person_relation_uniq_idx'

mysql> SELECT * FROM person_relation;
+----+-----------+----------+-------------+
| id | parent_id | child_id | relation_id |
+----+-----------+----------+-------------+
| 25 |         1 |        2 |           1 |
+----+-----------+----------+-------------+
1 row in set (0.00 sec)

3. people_relation.relation might not be NULL.

4. people_relation and person_relation are easy to mix up, these names are too similar to each other. I'd rename people_relation to relation_type.

-
thanks, im a newb, so i am not very confident with my work thus far. but good points and catches with number 1, 3, and 4. regarding number 2, could you go into a little more detail there? i am not understanding. how would this help to have a unique index for the triplet. and wouldn't by nature they already be unique? –  ArmYourselves Mar 7 '13 at 16:28
@ArmYourselves: I've updated the answer, check it, please. I hope it's a little bit clearer. Feel free to ask if you have any further questions. –  palacsint Mar 7 '13 at 16:57
@ArmYourselves: Thank you for accepting the answer but I think you should not do that so fast :) Waiting a few days with it you are likely to get more feedback from others. (You can unaccept it by clicking on the green tick icon.) –  palacsint Mar 7 '13 at 16:59
oh - yes.. :) thanks for the tips and extra explanation, your feedback has been great, its very tempting to accept your answer! i suppose yes.. i should wait. :) –  ArmYourselves Mar 7 '13 at 17:08