Data Relationships in PHP

Suppose we are faced with the task of storing fathers and sons. Let each father have only one son, and the son in turn can also have one son.

We need to figure out how we will 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 idea is not very good - because the same person can be simultaneously both a father and a son - and we 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: let's create a table users, in it we will store all users and give each one a field son_id, which will store the id of the son from this same table:

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

Queries

Now let's write a query that will fetch a user along with his son.

First, let's just get the users:

SELECT
	*
FROM
	users

Now let's join their sons to the users. We will join the table to itself, so we need to perform its renaming:

LEFT JOIN users as sons

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

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

Now let's 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

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

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

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

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