Looking for tree organisation for database, based on one to many relationship both ways All the tree organisations for databases I've found documentation, papers or blogs about were built on a one parent node can have many children, but a child node can have only one parent, with a single original node - basis.

I am looking for a way to organise a database efficiently on a basis that a parent node can have many children, a child node can have many parents, and there are multiple original nodes.

The data is not going to change or move around much, but there will be insertions in the middle of the tree on a regular basis.

Any ideas or links to papers appreciated.
A tree is, by definition, one parent with many children, each of which can have many children. What you desire is a generalised network, not a tree.

There are many DBMS products which present a graph (i.e. generalised network) data model. Both items and the relationships between items are first-class objects. Typically both can have arbitrary labels and attributes. These products are also optimised for relationship-based processing such as friends-of-friends, shortest path or cluster detection. If navigating the network is more important for your use case than OLTP point updates or calculating bulk aggregates then one of these DBMS may suit.

From comment:


  Is there a way of having a direction to this network though ? It's for storing school programme data, and the prerequisite knowledge for a certain course for example. (And that course can have many prerequisites and can be prerequisite to others, but there is a direction to the structure.)


All graph DBMSs implemented directed graphs. This means there is a "from" and a "to" in each relationship. This is analogous to, but different from, the "parent" and "child" in a relational database foreign key. A relationship's direction is set when the relationship is written to the DB. Relationships can be read in either direction, however, much like a SQL query can follow a foreign key to find the children given the parent, or the parent given a child.

A hypothetical graph for your school prerequisites might look like:

Basic math   &lt;-- requires --  Advanced math  &lt;--- requires ---¬
        ^                                                     |
        └-------  requires -----------¬                    Medicine
                                      |                       |
Basic chemistry  &lt;-- requires --  Advanced chemistry  &lt;-- requires


Medicine has two prerequisites - advanced math &amp; advanced chemistry. Basic math is a prerequisite for two other subjects.