What is the data structure of the three-level rebate system? Modify and query issues

I need to make a three-level rebate project. The requirement is to be able to view someone's first-level, second-level and third-level users and change the person's superior. The data structure was originally designed like this:
uid 1 2 3
User id belongs to the superior belongs to the superior belongs to the superior superior
1 0 0 0
2 1 0 0
3 2 1 0
4 3 2 1
5 4 3 2
6 3 2 1

User 1’s superior is platform, and the three superior levels are all 0. User 6’s superior is user 3, its superior is 2, and its superior is 1. This structure is convenient for query, but if you modify the default superior, if This person has 100,000 subordinates, and the superiors of these 100,000 people also need to make modifications. The amount of modification will be too large.
If changed to:
uid 1
User id belongs to the superior
1 0
2 1
3 2
4 3
5 4
6 3
Then to query all users in the third level from this user, you need to query the user first All the second-level users, and then query all the subordinates of all the second-level users, the query volume is also very large.
I would like to ask if you have any compromise solutions?

Reply content:

I need to make a three-level rebate project. The requirement is to be able to view someone's first-level, second-level and third-level users and change the person's superior. The data structure was originally designed like this:
uid 1 2 3
User id belongs to the superior belongs to the superior belongs to the superior superior
1 0 0 0
2 1 0 0
3 2 1 0
4 3 2 1
5 4 3 2
6 3 2 1

User 1’s superior is platform, and the three superior levels are all 0. User 6’s superior is user 3, its superior is 2, and its superior is 1. This structure is convenient for query, but if you modify the default superior, if This person has 100,000 subordinates, and the superiors of these 100,000 people also need to make modifications, and the amount of modification will be too large.
If it is changed to:
uid 1
The superior the user id belongs to
1 0
2 1
3 2
4 3
5 4
6 3
Then to query all the users at the third level from this user, you need to query the user first All the second-level users, and then query all the subordinates of all the second-level users, the query volume is also very large.
I would like to ask if you have any compromise solutions?

mysql foreign key + recursion

It is recommended to adopt the second solution, which only retains the uid and pid fields. This design supports any level of relationships, and if there is an update, the number of records modified will be relatively small.
Multi-level query can be simplified through the recursive query syntax of each database, such as the CONNECT BY statement in Oracle, or the CTE of SQLSQLER, etc.