How to store family relationship information with 1 record

Question

Lets consider that we have Person A. Person F is his father, person M is his mother, person B is his brother and person S is his son. Each person may have many relations. Thats why, we have to create new relationship table like following:

persons

+----+------+
| id | name |
+----+------+
| 1  | A    |
| 2  | F    |
| 3  | M    |
| 4  | B    |
| 5  | S    |
+----+------+

relationship type

+----+---------+
| id | value   |
+----+---------+
| 1  | Father  |
| 2  | Mother  |
| 3  | Brother |
| 4  | Son     |
| 5  | Wife    |
| 6  | Husband |
+----+---------+

relationship

+----+----------+------------+------+
| id | PersonID | RelativeID | Type |
+----+----------+------------+------+
| 1  | 1        | 2          | 1    |
| 2  | 1        | 3          | 2    |
| 3  | 1        | 4          | 3    |
| 4  | 1        | 5          | 4    |
| 5  | 2        | 1          | 4    |
| 6  | 2        | 3          | 5    |
| 7  | 3        | 1          | 4    |
| ......                            |
+----+----------+------------+------+

In this case 1st row means that 2 is father of 1 (for IDs) and 5th row means that 1 is son of 2. In the real world these 2 rows are equivalent, but if I don't insert one of this rows, I cannot get missing rows meaning using existed row.

The question is: How to make structure, which contains these 2 meanings in 1 row?

Acetify

A bit offtopic (not answer to your question), but I recommend to keep existing design. If you start using PersonID and RelativeID 'symmetrically', then all your queries become much more complex (or slower). Also there can be some future extensions to your data (like events, related to many participiants), when you need three or more related people - in your current design this would not be problem. You can synchronise double records with triggers. – Baluster

guess relationship(id, personA, personB, AtoB_type,BtoA_type) can help? – Cowitch

Answer 1

In fact between 2 relatives we have 2 relation:

Person A is person B`s wife <=> Person B is person A`s husband
Person C is person D`s sister <=>Person D is person C`s brother (D is male)
Etc...

so if you provide a Reverse_Relation_Type column for your third table (relationship), then your problem will get solved and you will not have redundant data al all, you will have:

+----+----------+------------+---------------+-----------------------+
| id | PersonID | RelativeID | Relation_Type | Reverse_Relation_Type |
+----+----------+------------+---------------+-----------------------+
| 1  | 1        | 2          | 1             |4                      |
| 2  | 1        | 3          | 2             |4                      |
| 3  | 1        | 4          | 3             |3                      |
| 4  | 1        | 5          | 4             |6                      |
| 6  | 2        | 3          | 5             |1                      |
| ......                                                             |
+----+----------+------------+---------------+-----------------------+

Nightclub