How I Store and Read a Versioned Organisation Chart in a Relational Database

Introduction

A. An organisation chart that is also master data

A city government is made of agencies, and every agency is a tree: a head at the top, divisions below, sections below those, and service units such as schools or clinics at the leaves. A personnel system for that government has to hold this tree, because almost everything else points into it. An employee works in a unit. A position — head of a section, a teacher, an administrative officer — belongs to a unit. A history of appointments says which position someone held and therefore which unit they worked in. Even access rights are scoped by it: an administrator may manage the employees of one agency, or of one division inside it.

The tree does not stay still. Each agency's structure is set by a mayoral regulation, and when a new one is issued, units are added, merged, renamed, moved under a different parent or abolished. Agencies do not change together: one adopts its new structure in January, another in July, a third keeps its structure for years. Meanwhile the past must stay readable. An appointment made five years ago refers to a unit as it existed five years ago, and a report for that year must show the organisation as it was then, not as it is today.

The challenge is to store and read a versioned organisation chart in a relational database: a hierarchy that changes by regulation, readable as it stood on any date, while every record that points into it keeps pointing at the right unit.

There are three parts in that sentence. "Store" is about representing a tree in tables. "Read" is about getting a nested structure back out, fast enough to draw a chart of a whole government. "Versioned" is about time: the same question — what does the organisation look like? — must have a different answer for each date, and the answers must connect, so that a unit in this year's structure can be traced back to its predecessor in last year's.

B. Why a hierarchy is still relational

Hierarchical data looks, at first sight, like the natural fit for a document database or a graph database. A tree is a nested document; an organisation chart is a set of nodes and edges. I tried both, and I came back to SQL, because in this system the hierarchy is not an isolated object. It is master data for everything else that is already relational.

Employees, positions, appointment histories and access scopes all live in a relational database, and each of them needs a foreign key into the organisation tree. That foreign key is what guarantees that an employee cannot belong to a unit that does not exist, that deleting a unit cannot silently orphan the people in it, and that a report can join employees to units in one query. Once the tree lives somewhere else, every one of those guarantees has to be rebuilt by hand.

C. The approaches I rejected

The first approach I tried was a document database, MongoDB, holding each agency's structure as one nested document. It was pleasant to read and write as a whole tree, but every other table still needed to refer to units by identifier, and nothing enforced that those identifiers existed. Keeping the two stores in step became a job of its own: synchronising after each change, checking for units that had disappeared from one side, and explaining reports whose numbers disagreed. The overhead was constant and the inconsistencies kept appearing.

The second approach was a graph database, Neo4j, holding units and positions as nodes and their relationships as edges. Traversals were easy to express, but I came to see the arrangement as an anti-pattern for this system. The main thing the graph gave me was a picture of how nodes connect — and drawing that picture is the job of the user interface, not the database. I had moved presentation into storage, and paid for it with a second database, a second consistency problem and a second query language, to obtain something a Vue component can draw from a nested list.

There are also well-known relational encodings of trees that I did not choose: nested sets, which store left and right boundaries for each node; materialised paths, which store the chain of ancestors as a string; and closure tables, which store every ancestor–descendant pair. Each makes some reads cheaper at the price of more complicated writes. In this system the trees are small enough per agency, and edited often enough while a new structure is being drafted, that the simplest encoding — each row pointing at its parent — was the better trade.

So the approach I chose has three layers, each doing one job. The database stores the tree as rows that point at their parents, with a version and an effective date. The API reads those rows and returns a nested structure. The user interface draws it.

D. Sixteen obstacles in the way

Easy to state, awkward to build. Sixteen things stood between "a table that points at itself" and an organisation chart that can be read on any date.

The first is that a hierarchy invites a non-relational database, even though it is master data for relational records. A document or graph database is the rejected alternative.

The second is representing a tree in tables at all. Nested sets, materialised paths and closure tables are the alternatives I did not choose.

The third is reading a nested tree back out efficiently. One query per level, or per node, is the rejected alternative.

The fourth is answering "everything above" and "everything below" a unit. Walking the tree in application code, one query per step, is the rejected alternative.

The fifth is that a tree must stay a tree. Trusting every edit not to create a loop is the rejected alternative.

The sixth is that the structure changes by regulation. Editing the current tree in place is the rejected alternative, because it erases the structure that applied yesterday.

The seventh is that agencies change on their own dates. One version for the whole government is the rejected alternative.

The eighth is reading the whole government as it stood on a given date. Storing a snapshot of the chart for every date anyone might ask about is the rejected alternative.

The ninth is knowing which unit in a new version continues which unit in the old one. Matching units by name is the rejected alternative.

The tenth is that positions form a second hierarchy on top of the units. Treating a position as a label on a unit is the rejected alternative.

The eleventh is how many people fill each position on a date. Storing that number is the rejected alternative.

The twelfth is that records pointing into the tree must follow it to the current version. Re-pointing them by hand after each reorganisation is the rejected alternative.

The thirteenth is seeing what a new version changes before it takes effect. Reading two trees side by side is the rejected alternative.

The fourteenth is drawing the chart. Making the database produce the picture is the rejected alternative.

The fifteenth is knowing who changed the structure, and how. Relying on database backups to reconstruct edits is the rejected alternative.

The sixteenth is turning a new regulation into a draft version. Typing every unit of every agency again by hand is the rejected alternative.

The order moves from the table outward. The first five are about holding one tree correctly; the sixth to the ninth add time; the tenth to the twelfth are the records that depend on the tree; the last four are how people review, see, audit and feed it. A tree that is not stored correctly cannot be versioned correctly, which is why storage comes first.

Method

A. Staying in SQL

This is the answer to the first obstacle: the organisation tree lives in the same relational database as everything that refers to it. Employees, positions, appointment histories and access scopes reference units through ordinary foreign keys, so the database itself refuses an employee in a unit that does not exist, and a report joins people to units in a single query. There is one store, one transaction boundary and one query language.

B. A table that points at itself

Each unit is a row with a foreign key to its parent row in the same table — an adjacency list. A root has no parent. A position number keeps siblings in the order the regulation lists them, and every unit belongs to exactly one version.

CREATE TABLE org_unit (
  id         BIGSERIAL PRIMARY KEY,
  version_id BIGINT NOT NULL REFERENCES org_version(id) ON DELETE CASCADE,
  parent_id  BIGINT REFERENCES org_unit(id) ON DELETE CASCADE,
  name       TEXT   NOT NULL,
  position   INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX ON org_unit (version_id, parent_id);

Moving a unit is a change of one column. Adding a unit is one insert. That simplicity matters most while a new structure is being drafted, when units are moved and renamed many times before the version is final.

C. One query, then nesting in one pass

Reading a tree does not need a query per level. I fetch every unit of a version in one flat query, ordered by position, and nest them in the API in a single pass: group the rows by parent, then build each node's children from its group. The work grows linearly with the number of units, and the database is asked once.

$byParent = $nodes->groupBy(fn ($n) => $ids->has($n->parent_id) ? $n->parent_id : 0);

$build = fn (int $parent) => $byParent->get($parent, collect())
    ->map(fn ($n) => present($n) + ['children' => $build($n->id)])
    ->values()->all();

return $build(0);

One detail is deliberate. A row whose parent is missing from the result is placed at the root rather than dropped, so a damaged branch shows up in the chart instead of disappearing without a trace.

D. Recursive queries for ancestors and descendants

Some questions are about a path rather than a whole tree: the full name of a unit from the agency down, or every unit below a branch that is about to be copied or deleted. For those I use a recursive common table expression, which lets SQL walk the parent links itself and return the whole chain in one round trip.

WITH RECURSIVE up (id, parent_id, depth) AS (
  SELECT id, parent_id, 0 FROM org_unit WHERE id = :unit
  UNION
  SELECT u.id, u.parent_id, up.depth + 1
  FROM org_unit u JOIN up ON u.id = up.parent_id
)
SELECT id, depth FROM up WHERE depth > 0;

The descending version is the same query with the join reversed. Both are short, both stay inside the database, and neither needs a special encoding of the tree.

E. Rules that keep the tree a tree

An adjacency list will happily store a loop if nothing stops it, so the API stops it. A unit cannot be moved under itself or under any of its own descendants — the descendants come from the recursive query above. A parent and its child must belong to the same version. A branch is deleted in one statement, so the self-referencing foreign key is checked once the whole branch is gone, not row by row halfway through.

if ($newParent->id === $node->id
    || in_array($newParent->id, $this->descendantIds($node), true)) {
    throw new CycleException('A unit cannot move under itself or its descendants.');
}

F. A version per regulation, frozen once it applies

Each structure belongs to a version, and each version belongs to the regulation that set it. A version has three states. A draft has no effective date and can be edited freely. An adopted version has a start date and is in force from then on. An ended version has an end date as well. Once a version is in force its units are not edited, because they are a fact of the regulation; a change to the structure is a new draft, adopted on its own date.

CREATE TABLE org_version (
  id             BIGSERIAL PRIMARY KEY,
  agency_id      BIGINT REFERENCES agency(id),       -- null: the mayor's office
  regulation_id  BIGINT NOT NULL REFERENCES regulation(id),
  effective_from DATE,                               -- null: still a draft
  effective_to   DATE,
  previous_id    BIGINT REFERENCES org_version(id)
);

G. Versions per agency, each on its own date

I keep versions per agency, not one version for the whole government. Adopting a draft on a date ends that agency's previous version on the same date and leaves every other agency untouched. On any date each agency has at most one version in force. Adoption refuses a date before the regulation itself takes effect, an empty draft, and a date that would overlap a version already adopted later.

A single government-wide version would have forced a new copy of every agency each time any one of them changed, and it would have made "the structure on this date" depend on unrelated agencies' reorganisations.

H. The same query, a different date

With effective dates on versions, reading the past needs no snapshots. A version is in force on a date when it started on or before that date and has not ended by it. Selecting those versions, then fetching all their units in one more query and nesting them per version, produces the whole government as it stood on that date.

SELECT * FROM org_version
WHERE effective_from IS NOT NULL
  AND effective_from <= :on
  AND (effective_to IS NULL OR effective_to > :on);

The interface simply sends a different date. Yesterday, last year and next month — for a version adopted in advance — are the same query with a different parameter.

I. Lineage links between versions

A new draft starts as a copy of the agency's current version, and every copied unit records the unit it came from. That link, not the unit's name, is how the system knows that a division in the new structure continues a division in the old one, even after it has been renamed or moved. Units are copied parents first, so every copy can point at its copied parent.

The older system I replaced copied the whole tree for each period with fresh identifiers and no link between copies. Names were the only way to connect them, and names change exactly when structures do. Matching by name was therefore the alternative I refused to rebuild.

J. Positions, a second hierarchy

Positions sit on units, and they have a hierarchy of their own: a section head reports to a division head, who reports to the head of the agency. A position is a slot with a capacity — how many people it needs — rather than a label. Within a version in force, a revision of the position map closes the old row on a date and opens a new one that records its predecessor. Predecessor links are many-to-many, because a reorganisation often merges several positions into one or splits one into several. To find a position's superior on a given date, the system follows the recorded superior forward through its successors until it reaches the one in force on that date.

K. Staffing is calculated, not stored

How many people fill a position on a date is never typed in. It is derived from appointment histories: for each active employee, the latest appointment on or before the date, counted against the position it points at or against the position that succeeded it. A count derived this way cannot drift from the appointments, and it can be asked for any date, including dates before a reorganisation.

L. Records follow the tree forward

An employee's current unit is derived from their current appointment and then followed forward along the lineage links to the version in force today. When a new version is adopted, or when an appointment changes, the derived unit is refreshed; a daily job catches versions whose start date arrives later. Nobody re-points employees by hand after a reorganisation, and the historical appointment still points at the unit as it was.

M. A diff before a draft takes effect

Before a draft is adopted, the interface compares it with the version it came from, using the same lineage links. Each unit is reported as added, removed, renamed, moved to a different parent or given a different unit type, and positions are compared field by field. A reviewer reads a list of changes instead of scanning two large charts for differences.

N. The database returns data, the interface draws

The API returns the tree as nested data, each unit with its children. Drawing it is the interface's job. In Vue, a chart node is a component that renders its own box and then renders itself again for each child, and the connecting lines of the chart are plain CSS. Branches can be collapsed, a long list of leaf units is stacked vertically so the chart stays readable, and the chart can be printed.

<!-- ChartNode.vue: one box, then itself again for each child -->
<li>
  <div class="chart-box">{{ node.name }}</div>
  <ul v-if="open && node.children.length">
    <ChartNode v-for="child in node.children" :key="child.id" :node="child" />
  </ul>
</li>

Next to the chart, a timeline lists the agency's versions with their regulations and periods, and a date field asks the API for the structure on any day. This is exactly the work I had once pushed into a graph database: in the interface it is one recursive component.

O. Every change is logged

Every edit to a version or a unit is written to a change log in the same transaction: created, updated, moved, reordered, copied, deleted, adopted, ended — with the actor and the before and after values. The log stays when a draft or a branch is deleted. When someone asks why a unit sits where it does, the answer is a query, not an investigation through backups.

P. Drafts built from the regulation

When a new regulation arrives, its list of units is curated into a simple file per agency — each line a path from the agency root and a unit type — and turned into a draft version. Each unit in the file is linked to its counterpart in the current version when the path matches, or through a small table of known renames, so the lineage is in place from the start. Units the regulation does not cover, such as service units governed by their own regulations, are carried over from the current version. The draft then goes through the same diff and adoption as any other.

Results and Discussion

A. What the design makes possible

The organisation chart of the whole government can be drawn for any date with two queries and one pass of nesting. Every record that points into the tree is protected by a foreign key. A reorganisation is a draft that can be reviewed as a list of changes, adopted on its own date by one agency, and traced afterwards through its log. None of this needed a second database.

B. What it costs

The adjacency list keeps writes simple and puts the cost on reads of whole paths, which the recursive query handles well at this scale. Versions copy units rather than share them, so storage grows with every reorganisation; I accepted that, because a frozen copy is what makes the past readable without reconstruction. Lineage links have to be maintained carefully when drafts are built, and that is why drafts are generated with links and reviewed through a diff rather than typed.

C. Where this approach fits

The approach fits hierarchies that are master data for other relational records and that change at known dates: organisation charts, chart-of-accounts structures, product categories under regulation. It is less suited to very deep, very large graphs queried mainly by traversal, such as social networks, where a graph database earns its place because the traversal itself is the product.

Conclusion

An organisation chart that changes by regulation is still relational data. I store it as an adjacency list inside versions with effective dates, read it with one flat query nested in one pass and with recursive queries for paths, connect versions through lineage links rather than names, derive staffing and current units instead of storing them, and leave the drawing to a recursive Vue component.

The two databases I tried before were not wrong tools in general. They were the wrong place for this data, because the tree was never on its own: everything else in the system points into it, and the database that holds everything else is the one that can keep those pointers honest.

Terms

Adjacency list

A way of storing a tree in a table in which each row holds a foreign key to its parent row.

Recursive common table expression

A SQL query that refers to itself, used to walk parent links and return a whole chain of ancestors or descendants in one round trip.

Version

One agency's structure under one regulation, with a start date once adopted and an end date once replaced.

Lineage link

A reference from a unit or position to its predecessor in an earlier version, used instead of matching by name.

Position

A slot on a unit with a required number of holders and a superior position of its own.

Agency

A department of the city government whose internal structure is set by its own regulation.