Multiple Relations Parent/child With Multiple Levels

I have a MySQL table named companies like this: +---------+-----------+-----------+ | id_comp | comp_name | id_parent | +---------+-----------+-----------+ | 1 | comp1 |

Solution 1:

Mysql isn't the best choice if you need to work with hierarchical data (getting all ancestors/descendants can be tricky). But if all you care about is finding direct parents/children, your table should be fine (although I might break it out into separate Company and CompanyParent tables so that the company name isn't entered multiple times).

This would give you brothers:

select name
from companies 
where id_parent in (select id_parent from companies where id_comp = @company_id)
and id_comp <> @company_id
groupby name;

This would give you direct parents:

select p.name
from companies p
join companies c on p.id = c.id_parent
where c.id_comp = @company_id
groupby c.name;

This would give you direct children:

select c.name
from companies p
join companies c on p.id = c.id_parent
where p.id_comp = @company_id
groupby c.name;

Solution 2:

You have a simple "many:many" relationship. However, you have a restriction that is not really relevant (nor checkable) in that there are no loops.

CREATETABLE Relations (
    id_comp ...,
    id_parent ...,
    PRIMARY KEY(id_comp, id_parent),  -- for reaching "up"
    INDEX(id_parent, id_comp)         -- for reaching "down"
) ENGINE=InnoDB;

This will scale to millions, probably billions, of relations. Since a PRIMARY KEY is, by definition, UNIQUE and an INDEX, it prevents duplicate relations (1 is a parent of 2 only once) and provides an efficient way to traverse one direction.

Use DISTINCT instead of GROUP BY when necessary. Do not use IN ( SELECT ...), it tends to be slow.

My Siblings:

SELECTDISTINCT their_kids.*
    FROM Relations ASmeJOIN Relations AS my_parents  ON my_parents.id_comp = me.id_parent
    JOIN Relations AS their_kids  ON their_kids.id_parent = parents.id_comp
    WHEREme.id_comp = @meAND their_kids.id_comp != @me;

My (immediate) Parents:

SELECT my_parents.*
    FROM Relations ASmeJOIN Relations AS my_parents  ON my_parents.id_comp = me.id_parent
    WHEREme.id_comp = @me;

My (immediate) Children:

SELECT my_kids.*
    FROM Relations ASmeJOIN Relations AS my_kids  ON my_kids.id_parent = me.id_comp
    WHEREme.id_comp = @me;

Aunts, uncles, first cousins would be a bit messier. All ancestors or descendants would be much messier, and should be done with a loop in application code or a Stored Procedure.