How will you represent a hierarchical structure shown below in a relational database? or How will you store a tree data structure into DB tables?

The hierarchical  data is an example of the composite design pattern. The entity relationship diagrams (aka ERdiagram) are used to represent logical and physical relationships between the database tables. The diagram below shows how the table can be designed to store tree data by maintaining the adjacency information via superior_emp_id.

 

As you can see the “superior_emp_id” is a foreign key that points to the emp_id in the same table. So, Peter has null as he has no superiors. John and Amanda points to  Peter who is their manager or superior and so on.

The above table can be created using SQL DDL (Data Definition Language) as shown below.

CREATE TABLE employee

(

emp_id          NUMBER (4) CONSTRAINT emp_pk PRIMARY KEY,

emp_name        VARCHAR2 (40) NOT NULL,

title           VARCHAR2 (40),

dept_id         NUMBER (2) NOT NULL,

superior_emp_id NUMBER (4) CONSTRAINT emp_fk REFERENCES employee(emp_id)

CONSTRAINT emp_pk

PRIMARY KEY NONCLUSTERED (emp_id)

)

This can be represented as an object model  to map relational data as shown below

public class Employee

{

private Long id;

private String name;

private String title;

private Employee superior;

private Set subordinates;

//getters and setters are omitted

}