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:

Queries

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

First, let's just get the users:

sqlSELECT
  *
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:

sqlLEFT JOIN users as sons

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

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

Now let's specify the fields:

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

Let's put it all together and get the following query:

sqlSELECT
  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