Storing deep hierarchies in a database

Introducing nested sets: part one

Photo by Blake Weyland on Unsplash

The problem we’re trying to solve

Relational databases are great for storing a classic parent/child relationship. Take the example of continents:

continent
id    name
1     Europe
2     America

and countries:

country
id    name              continent_id
1     France            1
2     United Kingdom    1
3     Sweden            1
4     Canada            2

Each country belongs within a specified continent (let’s just pretend it’s that simple for this example!), specified by its id in the continent_id field. The query to fetch details for a given country is straightforward:

SELECT
    continent.name AS continent,
    country.name AS country
FROM
    continent,
    country
WHERE
        country.continent_id = continent.id
    AND country.id = '2';

and produces the output:

+-----------+----------------+
| continent | country        |
+-----------+----------------+
| Europe    | United Kingdom |
+-----------+----------------+
1 row in set (0.00 sec)

But what if you want to store other location granularities, or other hierarchical data such as generic subject categories, with a…