A category tree with no way back to the root

Difficulty 4/5Subscribers

New to this? Learn it first: Trees: a table that points at itself

A catalogue organises products into nested categories: Electronics contains Audio contains Headphones.

The rules:

A category has at most one parent. A category with no parent is a root.
The structure is a tree, which means no cycles. A category cannot be its own parent, its own grandparent, or reachable from itself by any number of steps. A cycle makes the breadcrumb query run forever.
Names are unique among siblings. Two categories called "Accessories" are fine as long as they sit under different parents.
Deleting a category with children is refused. Losing a subtree by accident is worse than an error message.

The tests walk the tree with a recursive query, so the structure has to be usable as well as valid.

The contract

1 relation

The tests reference these names. Everything else is yours, and is what is being assessed: extra tables, extra columns, types, constraints, indexes.

categories
idparent_idname

CREATE TABLE ...  (
  id          BIGINT GENERATED ALWAYS AS IDENTITY,
  tenant_id   BIGINT NOT NULL REFERENCES ...,
  ...         TEXT NOT NULL,
  valid_from  TIMESTAMPTZ NOT NULL,
  valid_until TIMESTAMPTZ
);

CREATE UNIQUE INDEX ... ON ... (...)
  WHERE ... IS NULL;

LOCKED

This one is for subscribers

The problem above is the whole problem, and nothing is hidden from it. What a subscription adds is the part that tells you whether your answer holds: a real Postgres runs your schema, a battery of hidden tests decides, and a design review reads what you wrote.

See the plans Or try one of the free ones

Checking your subscription…