How to create database tables for a multi-level referral system?

Question

PHP

Good day, dear developers!
We need a multi-level system of referrals.
The algorithm is as follows: user1 invited user2...user9 invited user10
user10 contributes money that is distributed to all 10 higher levels, that is, user10 pays user9, user8...user1
Accordingly, the scheme will branch

What is needed: I can not create tables for the database.
Help, please, point to the list of columns of tables and how to make a selection of 10 higher users from them.

Answer 1

If you want to select from a tree with one simple request, you need to look towards storing tree structures: Nested Set, Materialized Path, Adjacency List, Closure Table, etc.
They have their pros and cons - you need to choose the right one for your task, depending on what operations you will perform with the tree.
There is already an answer about them here . And here is the article .

Answer 2

nested set

Answer 3

within the framework of the table structure, they usually add parent_id and that's it ... , or if there is a many-to-many relationship, they make a separate cross-table of links ... although there may be other options, if you find it, write ...
and look at these links:
explainextended.com/2009/03/17/hierarchical-querie...
stackoverflow.com/questions/7631048/connect-by-pri...
Referral is divided into direct and indirect., according to ( https://ru.wikipedia .org/wiki/%D0%A0%D0%B5%D1%84%D.... Regarding
the money distribution rule, I'm afraid you will have to have a separate table structure, at least two: financial document and financial transactions. but this is probably beyond the scope of the task, although I think all the complexity will be in this part ...