CODEWITHSUNDEEP
mysql
asitis
asitis

Reputation: 11

parent and child tables

I am sorry if this topic has already been brought up.

The requirement is this: I am creating a family tree system, I need to keep the details of a person which can be a parent and/or a child to other persons. For example personA is parent of personB, personB is parent of personC (personA->personB->personC). My version of mysql to the above requirement is

person

|id |person_name|
 1 | personA
 2 | personB
 3 | personC

my_parent

|id | person_id | parent|
1  |  3 |  2
2  |  2 |   1

But I don't feel right about this. Can any mysql guru please give me some better advise and also an sql statement to iterate through to retrieve the person hierarchy. Thanks

Upvotes: 1

Views: 373

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270607

You don't actually need two tables for this. Instead, it is usually done with a single table with a column for the parent that references another person_id in the same table:

Table people

person_id | person_name | parent_id
1         | JSmith Jr   | 2
2         | JSmith Sr   | 3
3         | Grandpa     | NULL

To query it, you use JOIN against itself:

SELECT 
  child.person_name AS childname,
  child.person_id,
  parent.person_name AS parentname
FROM 
  /* First use of the table for the child */
  people AS child
  /* JOIN against the same table to get the parent's name */
  /* LEFT JOIN is used in case the parent_id is NULL and the parent is unknown */
  LEFT JOIN people AS parent ON child.parent_id = parent.person_id

Produces:

childname  | id | parentname
-----------|----|-------------
JSmith Jr  | 1  | JSmith Sr
JSmith Sr  | 2  | Grandpa
Grandpa    | 3  | NULL

Update To allow for multiple parents, use 2 tables:

Table people:

person_id | person_name
1         | JSmith Jr
2         | JSmith Sr
3         | Grandpa
4         | Grandma
5         | MomSmith

Table relationships:

person_id | parent_id | relationship
1         | 2         | father
1         | 5         | mother
2         | 3         | father
2         | 4         | mother

To query for mother & father:

SELECT
  child.person_id,
  child.person_name AS childname,
  mother.person_name AS mothername,
  father.person_name AS fathername
FROM 
  people AS child
  JOIN relationships AS rc ON child.person_id = rc.person_id
  LEFT JOIN people AS mother ON rc.parent_id = mother.person_id AND relationship = 'mother'
  LEFT JOIN people AS father ON rc.parent_id = father.person_id AND relationship = 'father'

Untested, but should produce:

person_id | childname | mothername | fathername
1         | JSmith Jr | MomSmith   | JSmith Sr
2         | JSmith Sr | Grandma    | Grandpa
3         | Grandpa   | NULL       | NULL
4         | Grandma   | NULL       | NULL
5         | MomSmith  | NULL       | NULL

Upvotes: 3

Related Questions