Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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

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.