What is the best way to implement a multi-level referral system for paying referrals?

Question

Database design

Now I imagine this:
in the database for each user, make a referal cell, i.e. store the id of the inviting user.
And run a function with recursion, which will take the referal value and pay it%, and until the branch with referrals ends.
Do you think this is a good solution? Can you suggest something?

Answer 1

Options:
1. Store as you described, you will have to rummage through the database and then build a graph for each payment.
2. Store immediately a list of referrers with a level (json field, for example), it will suit your requirements, but there are limitations with further expansion
3. Make M2M and store information about the level of referrals in an intermediate table. Then you can easily find all referrals and all referrers of the user.
Those. intermediate table in M2M will store: referral_id, referrer_id, referral_level.
Plus, with the evolution of the system, you most likely want to store some more information about referrals and then make a separate 1-to-1 plate with the user's referral profile. Well, M2M will then be between referral profiles.