Code Review Stack Exchange is a question and answer site for peer programmer code reviews. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted

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.

share|improve this answer
    
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
1  
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

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.