The most common and familiar is known as the adjacency model, and it usually works like this: It's called the "adjacency" model because the parent (boss) data is stored in the same row as the child (employee) data, in an adjacent column.
It's a pretty straightforward design that's easily understood by everyone..deep relational theory needed. Employee ID For each level, you'd need to join the table to itself..an attractive option if you have 5 or more levels!
Hierarchies may be expressed easily by switching to a graph database.
Alternatively, several resolutions exist for the relational model and are available as a workaround in some relational database management systems: When these solutions are not available or not feasible, another approach must be taken.
The technique is an answer to the problem that the standard relational algebra and relational calculus, and the SQL operations based on them, are unable to express all desirable operations on hierarchies directly.
This pattern may also provide a suitable solution for storing graphs where a node may have multiple parents.When the hierarchy is of fixed or bounded depth, the operations are possible, but expensive, due to the necessity of performing one relational join per level.This is often known as the bill of materials problem.Multiple data sources can be associated with a tree structure and can have well-defined relationships among them.
Using the example in Figure 19-1, the Accounts Receivable data source is a child of the Finance data source.While XML handles hierarchical data quite well, relational SQL doesn't.