Sql Database design hierarchy

I have been tasked to create an app to track our contracts. I am planing to use Django and PostgreSQL.

Our existing data is in Excel with 52 rows.

I have found an hierarchy pattern in the file which contains as below

1. Holding Office
2. Registered Office
3. Operating Office

For example – Holding office (company) can contain one more Registered Office (company) and in-turn registered office (company) can contain one more operating office(Company).

1st I thought I can create 3 tables and map them using PK and FK. But what if in the future they wanted to change the hierarchy from 3 to 4 or 5, how to I design a database to achieve the same.

Answer

This is typically done using a pattern called the “adjacency list model”.

create table office
(
  office_id integer primary key, 
  office_name varchar(50) not null unique,
  parent_office_id integer references office
);

Note that parent_office_id is nullable to indicate “no parent”.

You can then build up the structure like this:

insert into office (office_id, office_name, parent_office_id)
values
(1, 'Holding Office', null),
(2, 'Registered Office One', 1),
(3, 'Registered Office Two', 1),
(4, 'Operating Office One', 2),
(5, 'Operating Office Two', 2),
(6, 'Operating Office Three', 3);

You can get all “child” offices using a recursive query. There are a lot of examples on Stackoverflow for that.