Here is the situation, I need to organize some users like an binary tree to explain organization workflow, but organization can change.
Here is how its look like:
1 / \ 2 3 / \ / \ 4 5 6 7
Its sounds easy:
Lets imagine this is the user table:
id | user | position | who_contracted 1 | ana | 1 | null 2 | bob | 2 | 1 3 | joe | 3 | 1 4 | Nik | 6 | 3 5 | Ahn | 4 | 1 6 | Rob | 5 | 2 7 | Chloe | 7 | 1
Ana contracted 4 people in organization, IDs [2,3,5,7] They are organized as they come to business.
But, when Ana was inviting they, Joe invited Awn to business. Its seat goes to be seat .
When Ana invite 1 more, the next opened seat is 4, because each invited people get first seat from left to right down the user who contracted him
It makes easy to build an strong sales team.
Now, I need to know how to find next seat to user in position 3.
I know how to do it with math, but do not know how to organize correctly the database.
For sample, if User id 3 invite one more, will get the seat 12, but if user id 6, invite first, seat will be 12 also.
I need to calculate just in time when insert into table.
ps. One more thing, I'm trying a way, that if I delete user 3, the users down come to up automatically, user on seat 6 will up to seat 3, because it is on low position compared with next user position, that is 7.