Keeping track of people's relationships using a people relation table I am trying to track relationships among people. I came up with my own solution, but I'm wondering if there might be another way or better way of doing this. To keep it simplified, I'll post just the bare bones.

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:


varchar(32) for name could be too small.
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&gt; select * FROM person_relation;
Empty set (0.00 sec)

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

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

mysql&gt; 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&gt; DELETE FROM person_relation;
Query OK, 2 rows affected (0.00 sec)

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

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

mysql&gt; 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&gt; SELECT * FROM person_relation;
+----+-----------+----------+-------------+
| id | parent_id | child_id | relation_id |
+----+-----------+----------+-------------+
| 25 |         1 |        2 |           1 |
+----+-----------+----------+-------------+
1 row in set (0.00 sec)

people_relation.relation might not be NULL.
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.