Risposta.

Lavorare con strutture gerarchiche è un classico dei database relazionali. Esempi: cataloghi, menu ad albero, strutture organizzative.

Storia della questione: I database sono un modello tabellare. Per i dati ad albero esistono diversi approcci, ognuno con i propri pro e contro.

Problema: Il modello standard parent_id porta a SELECT ricorsivi lenti. Le reali esigenze (ricerca di tutti i discendenti, percorsi, conteggio dei sottoalberi) richiedono ottimizzazione.

Soluzione:

Adjacency List — semplice riferimento a parent_id.
Materialized Path — stringa che riflette il percorso completo.
Nested Sets — memorizzazione dei limiti sinistri/destri (left/right), che consente di estrarre facilmente i sottoalberi.
Closure Table — tabella separata delle relazioni (from->to) che riflette tutte le relazioni nell’albero.

Esempio di codice per Materialized Path (PostgreSQL):

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name TEXT,
    path TEXT
);

-- Esempio di memorizzazione del path: '1/2/5/' (radice/sottocategoria/corrente)
SELECT * FROM categories WHERE path LIKE '1/2/%'; -- tutti i discendenti di 2

Esempio di codice per Nested Sets:

CREATE TABLE nested_categories (
    id SERIAL PRIMARY KEY,
    name TEXT,
    lft INT NOT NULL,
    rgt INT NOT NULL
);

-- Nodi figli
SELECT * FROM nested_categories WHERE lft > 2 AND rgt < 15;

Caratteristiche principali:

Adjacency List è comoda per strutture ad albero semplici (bassa nidificazione).
Materialized Path — rapida estrazione dei sottoalberi, facile da implementare.
Nested Sets — ottenimento immediato di tutti i discendenti, lettura veloce, ma modifica complessa.

Domande ingannevoli.

È possibile semplicemente usare un SELECT annidato con parent_id per cercare tutti i discendenti a profondità arbitraria?

Funziona solo per basse profondità. Per la ricerca ricorsiva è necessaria una CTE ricorsiva (WITH RECURSIVE) o schemi più complessi, poiché semplici JOIN portano a un numero enorme di accessi e scarsa performance.

Esempio:

WITH RECURSIVE cte AS (
    SELECT id, parent_id, name FROM categories WHERE id = 1
    UNION ALL
    SELECT c.id, c.parent_id, c.name
    FROM categories c
    INNER JOIN cte ON c.parent_id = cte.id
)
SELECT * FROM cte;

Perché l'albero molecolare (Nested Sets) estrae rapidamente il sottoalbero, ma aggiunge/rimuove nodi lentamente?

Quando si modifica l’albero, è necessario modificare l'lft/rgt immediatamente su molte righe (l'operazione di modifica — tutti i valori maggiori di quelli di inserimento/rimozione). Per la lettura, l'approccio è ideale, per modifiche frequenti usa altri metodi.

Quando utilizzare la Closure Table, anziché parent_id o materialized path?

La Closure Table funziona perfettamente per richieste multiple sui discendenti di qualsiasi livello e per il conteggio regolare delle relazioni. Contro — richiede più spazio.

Esempio:

CREATE TABLE closure (
    ancestor INT,
    descendant INT,
    depth INT
);

Errori comuni e anti-pattern

Memorizzazione della gerarchia solo tramite parent_id, quando è necessaria una rapida ricerca di strutture nidificate.
Ricalcolo manuale di percorsi o lft/rgt senza funzioni ausiliarie.
Mancanza di indicizzazione delle colonne chiave (parent_id/path/lft/rgt).

Esempio dalla vita reale

Caso negativo

In un negozio online, le categorie sono implementate tramite parent_id. Tutti i sotto-categorie sono impostati manualmente, ricerca di sotto-categorie — con SELECT annidati.

Pro:

Semplicità, non richiede supporto avanzato.

Contro:

Le prestazioni calano anche a 3-4 livelli di nidificazione.
Le operazioni di spostamento dei nodi — non ovvie, portano a errori logici.

Caso positivo

Si utilizza materialized path o closure table. Tutte le richieste di categorie nidificate sono istantanee, conteggio — con script di gruppo.