Data structure of Level three rebate system? Modify and query problems

A three-level rebate project needs to be able to view someone's first, two, and three users and change the person's superiors, and the data structure is initially designed:
UID 1 2 3
User ID belongs to upper 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 of the superior platform, on the three level are 0, the user 6 of the superior is the user 3, the superior is 2, the upper level is 1, the structure is convenient to query, but modify the default superior, if the person's subordinates have 100,000 people, then this 100,000 people on the superior also need to modify, the amount of modification is too big.
If you change to:
UID 1
User ID belongs to superior
1 0
2 1
3 2
4 3
5 4
6 3
That query the user down the third level of all users, you need to query all the user's second-level users, and then query all secondary users all subordinate, such a large number of queries.
Would you like to consult us on a compromise approach?

Reply content:

A three-level rebate project needs to be able to view someone's first, two, and three users and change the person's superiors, and the data structure is initially designed:
UID 1 2 3
User ID belongs to upper 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 of the superior platform, on the three level are 0, the user 6 of the superior is the user 3, the superior is 2, the upper level is 1, the structure is convenient to query, but modify the default superior, if the person's subordinates have 100,000 people, then this 100,000 people on the superior also need to modify, the amount of modification is too big.
If you change to:
UID 1
User ID belongs to superior
1 0
2 1
3 2
4 3
5 4
6 3
That query the user down the third level of all users, you need to query all the user's second-level users, and then query all secondary users all subordinate, such a large number of queries.
Would you like to consult us on a compromise approach?

MySQL foreign key + recursion

The second option is to retain only the UID and PID two fields, so that any level of relationship is supported from the design, and if there is an update, the number of records modified will be relatively small.
Multilevel queries, which can be simplified by recursive query syntax for each database, such as the Connect by statement in Oracle, or the Sqlsqler CTE.

