Menu
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?
422
To define a one-to-many relationship between two tables, the child table has to reference a row on the parent table. The steps required to define it are: Add a column to the child table that will store the value of the primary identifier.
A one-to-one relationship is a link between the information in two tables, where each record in each table only appears once.
There are three types of relationships between the data you are likely to encounter at this stage in the design: one-to-one, one-to-many, and many-to-many. To be able to identify these relationships, you need to examine the data and have an understanding of what business rules apply to the data and tables.
When you need to establish a many-to-many relationship between two or more tables, the simplest way is to use a Junction Table. A Junction table in a database, also referred to as a Bridge table or Associative Table, bridges the tables together by referencing the primary keys of each data table.
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 |
| ...... |
+----+----------+------------+---------------+-----------------------+
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
