Many-to-many relationship between objects of the same type

Question

I want to make a many to many relationship between an object "Human" and its parents which are of the same type and I wanted to know if this kind of relationship exists in real-life projects or how do experienced developers approach a requirement like that? What will be the best design to accomplish this task? Thank you!

Answer 1

In a relational database, in general a many-to-many relationship needs a second table.

For example:

CREATE TABLE Parentage (
  child_id INT NOT NULL,
  parent_id INT NOT NULL,
  PRIMARY KEY (child_id, parent_id),
  FOREIGN KEY (child_id) REFERENCES Human (id),
  FOREIGN KEY (parent_id) REFERENCES Human (id)
);

This is the relational way of storing the data, which has advantages that it results in the least redundancy of data, and supports a wide variety of queries with reasonable efficiency.

It also supports a true many-to-many relationship, where a child can have many parents (likely only two biological parents, but with step-parents and guardians and fosters and so on, there could be an unlimited number of humans who count as parents), as well as each parent having many children.

If you want to optimize the table design for a specific query, that might be possible. The best design depends on that query you need to optimize, but you haven't described any specific query you will need to run.