Relazioni tra dati correlati in Python

Supponiamo di dover memorizzare padri e figli. Supponiamo che ogni padre possa avere solo un figlio, e il figlio a sua volta possa avere un solo figlio.

Dobbiamo ideare come memorizzare i dati. La prima idea che potrebbe venire in mente è creare due tabelle: parents per i padri e sons per i figli. Poi collegare queste tabelle con un qualche campo: son_id o parent_id.

Tuttavia, questa non è un'idea molto buona - dato che la stessa persona può essere contemporaneamente sia padre che figlio - e dovremmo memorizzarla in entrambe le tabelle, il che è scomodo, occupa più spazio e facilmente porta a errori.

Un'opzione migliore è collegare la tabella a sé stessa: creiamo una tabella users, in essa memorizzeremo tutti gli utenti e per ognuno creeremo un campo son_id, in cui sarà memorizzato l'id del figlio dalla stessa tabella:

users
id	name	son_id
1	user1	2
2	user2	3
3	user3	null

Query

Ora scriviamo una query che recuperi l'utente insieme a suo figlio.

Per iniziare, recuperiamo semplicemente gli utenti:

SELECT
	*
FROM
	users

Ora uniamo (JOIN) agli utenti i loro figli. Dobbiamo unire la tabella a sé stessa, quindi dobbiamo rinominarla:

LEFT JOIN users as sons

Ora possiamo specificare la relazione tra la tabella principale e quella rinominata:

LEFT JOIN users as sons ON sons.id=users.son_id

Specifichiamo ora i campi:

SELECT
	users.name as user_name, sons.name as son_name

Mettiamo tutto insieme e otteniamo la seguente query:

SELECT
	users.name as user_name, sons.name as son_name
FROM
	users
LEFT JOIN users as sons ON sons.id=users.son_id

Problemi pratici

Supponiamo di avere delle categorie. Ogni categoria può appartenere a una categoria genitore, che a sua volta appartiene al suo genitore e così via. Descrivete la struttura di memorizzazione.

Scrivete una query che recuperi una categoria insieme alla sua categoria genitore.

Scrivete una query che recuperi una categoria insieme al suo genitore e al suo nonno (genitore del genitore).

Scrivete una query che recuperi una categoria insieme al suo genitore, nonno e bisnonno.