CODEWITHSUNDEEP
database-design
user194076
user194076

Reputation: 9027

Database design with recursion

I have table Person. One person can have many siblings from the same table. I have a hard time understanding on how to do this design, because If I do recursive relationship I can only add one sibling.

Table: PersonId, LastName,FirstName.

help please!

Upvotes: 0

Views: 150

Answers (1)

Ray Toal
Ray Toal

Reputation: 88458

Because the sibling relationship is many to many, you need a second table called SIBLING, with two columns, the person id of each sibling in the relationship.

PERSON
+----------+--------------+--------------+
| PersonId | Last Name    | First Name   |
+----------+--------------+--------------+
| 1        | Abc          | Def          |
| 2        | Ghi          | Def          |
| 3        | Jkl          | Stu          |
| 4        | Mno          | Def          |
| 5        | Pqr          | vwx          |
+----------+--------------+--------------+

SIBLING
+-----+-----+
| Id1 | Id2 |
+-----+-----+
| 1   | 2   |
| 1   | 4   |
| 2   | 4   |
| 3   | 5   |
+-----+-----+

Here SIBLING is sometimes called a "join table" or "association table." Its PK is the whole table (a composite PK) and each column is a FK to PERSON.PersonId.

Upvotes: 3

Related Questions