Mysql : La gestione delle relazioni nei database - metodo della lista di adiacenza

Iniziamo con questo articolo una serie di tutorial nei quali vedremo come organizzare le gerarchie "padre-figlio" in un database relazionale come Mysql, che non offre funzioni avanzate come la "WITH" supportata, ad esempio, da PostegreSQL.
Il metodo più immediato per la gestione di una gerarchia si basa sul cosidetto "albero con lista di adiacenza". Ipotizziamo di dover gestire un elenco di argomenti di un sito sportivo. La nostra tabella "categorie" avrà tre campi:
categoria_id (classico campo chiave auto-increment)
categoria_parent_id (contiene il valore del campo "categoria_id" dell'argomento "padre")
categoria_nome (varchar con il nome della categoria)

La tabella avrà un insieme di dati di questo tipo:

in cui abbiamo l'elenco degli sport, in particolare il calcio, e quindi i campionati. Come potete facilmente intuire, il parent_id è pari a zero quando abbiamo una categoria principale (calcio o basket). Altrimenti, fa riferimento all'id della categoria padre (ad esempio il campionato di serie B ha come padre la categoria con id 3, "campionati italiani", che  ha a sua volta come categoria padre il macro argomento con id 1 ("calcio").
Il semplice illustrare l'immagine ci fà capire pregi e limiti di questa soluzione.
Pregi: la tabella è semplicissima da creare e popolare. L'inserimento di una nuova categoria chiede alla nostra applicazione un'unica informazione fondamentale, l'id della categoria principale. Se vogliamo ad esempio inserire il campionato "Lega A" di basket, dobbiamo prima estrarre l'id dello sport "Basket" per compilare in maniera adeguata la colonna "categoria_parent_id".
Difetti: un albero di questo tipo non è gestibile da unica query. Se vogliamo recuperare l'intera struttura dobbiamo ricorrere ad una funzione ricorsiva, qualcosa di questo tipo:

function get_categoria($categoria_parent_id){
    $return = array();
    $sql = 'select * from categorie where categoria_parent_id='.$categoria_parent_id;
    $result = mysql_query($sql) or die(mysql_error());
    if(mysql_num_rows($result) > 0){
        while($row = mysql_fetch_assoc($result)){
            $figli= get_categoria($row['categoria_id']);
            $return[] = array('categoria_nome'=>$row['categoria_nome'],'figli'=>$figli);
        }
    }
    return $return;
}

$albero = get_categoria(0);
echo '<pre>';
print_r($albero);
echo '</pre>';
   
Messa così funziona e anche bene, i problemi nascono quando il "get_categoria()" serve per utilizzi più complessi rispetto una semplice stampa con print_r. Classico esempio, generare un menù di selezione per un form con un pò di indentazione per far capire all'utente i rapporti di parentela tra le varie voci. Ma si tratta di problemi superabili con poche righe di codice. I veri guai vengono fuori quando il cliente avanza richieste quali "conoscere tutte le categorie che parlano di calcio": le funzioni di aggregazione non sono praticabili.

Altro difetto: se è semplice aggiungere una categoria, può risultare complicato eliminarla. Se andiamo ad eliminare ad esempio la categoria "Campionati italiani", togliamo alla nostra funzione ricorsiva get_categoria() tutti i campionati di calcio visto che Serie A, B e LegaPro fanno riferimento all'id 3 che a questo punto non esiste più.
Di contro, questo difetto ci fà capire un pregio delle liste di adiacenza: se vogliamo spostare l'intera Lega Pro in una macro categoria principale, dobbiamo solo aggiornare il campo "categoria_parent_id" a zero: le sottocategorie (girone A e girone B) rimangono sue figlie e non necessitano di ulteriori aggiornamenti.

In conclusione, la soluzione a liste di adiacenza è limitata ma non per questo da scartare a priori. Se non ci sono particolari esigenze di update (cosa non infrequente, le categorie di un sito ad esempio sono spesso fisse per anni) e non sono richieste frequenti funzioni di aggregazione è una delle soluzioni che preferisco, anche perché è conosciuta da tutti e favorisce lo sviluppo in team in progetti in cui sono coinvolti programmatori junior.
Nei successivi articoli ci occuperemo comunque degli altri metodi di gestione delle relazioni nei database.