Data Relationships in Python

Let us have the task of preserving fathers and sons. Let each father have only one son, and let the son in turn have only one son.

We need to figure out how we'll store the data. The first idea that might come to mind is to make two tables: parents for fathers and sons for sons. Then link these tables with some field: son_id or parent_id.

However, this is not a very good idea - after all, the same person can be both a father and a son at the same time - and you will have to store him in both tables, which is inconvenient, takes up more space and easily leads to errors.

A better option is to link the table to itself: make a table users, in it we will store all users and for each we will make a field son_id, in which id son from the same table will be stored:

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

Requests

Let's now write a query that will get the user along with his son.

First, let's just get the users:

SELECT
	*
FROM
	users

Now let's join the users of their sons. We will join the table to itself, so we need to rename it:

LEFT JOIN users as sons

Now we can specify the relationship between the base table and the renamed one:

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

Let's now specify the fields:

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

Let's put it all together and get the following 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

Practical tasks

Let's say we have categories. Each category can belong to a parent category, which in turn belongs to its parent, and so on. Describe the storage structure.

Write a query that will retrieve a category along with its parent category.

Write a query that will retrieve a category along with its parent and grandparent.

Write a query that will retrieve a category along with its parent, grandparent, and great-grandparent.