What is the data structure of the third-level rebate system? Modification and query problems

Source: Internet
Author: User
A three-level rebate project is required to view a person's first-level, second-level, and third-level users and change the person's superiors. The data structure is designed as follows: uid123... A three-level rebate project is required to view a person's first-level, second-level, and third-level users and change the person's superiors. The data structure is designed as follows:
Uid 1 2 3
Upper-level
1 0 0 0
2 1 0 0
3 2 1 0
4 3 2 1
5 4 3 2
6 3 2 1

The upper-level user 1 is a platform, the upper-level user is 0, the upper-level user is 3, the upper-level user is 2, and the upper-level user is 1. this structure is convenient for query, however, if there are 0.1 million lower-level employees of this person, the upper-level employees of this person also need to modify the default number, which is too large.
If Changed:
Uid 1
Upper level of the user ID
1 0
2 1
3 2
4 3
5 4
6 3
To query all the users of Level 3, you need to first query all the users of level 2, and then query all the sub-levels of all level 2 users. the query volume is also large.
I would like to ask if you have any discounts?

Reply content:

A three-level rebate project is required to view a person's first-level, second-level, and third-level users and change the person's superiors. The data structure is designed as follows:
Uid 1 2 3
Upper-level
1 0 0 0
2 1 0 0
3 2 1 0
4 3 2 1
5 4 3 2
6 3 2 1

The upper-level user 1 is a platform, the upper-level user is 0, the upper-level user is 3, the upper-level user is 2, and the upper-level user is 1. this structure is convenient for query, however, if there are 0.1 million lower-level employees of this person, the upper-level employees of this person also need to modify the default number, which is too large.
If Changed:
Uid 1
Upper level of the user ID
1 0
2 1
3 2
4 3
5 4
6 3
To query all the users of Level 3, you need to first query all the users of level 2, and then query all the sub-levels of all level 2 users. the query volume is also large.
I would like to ask if you have any discounts?

Mysql foreign key + recursion

We recommend that you use the second method. only the uid and pid fields are retained. this design supports any level of relationship. if there is an update, the number of records will be less.
Multi-level query, which can be simplified BY recursive query syntax of each database, such as the connect by statement in oracle or the CTE of SQLSQLER.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.