How to organize binary tree in database?
I think this question is principally, "how do you incentivize salesmen in a pyramid scheme?" That's cute, so yeah let's do it. Let's assume


We have 7 people in our scheme.
We allocate bonuses based on the tier of pyramid.


You don't have to place them in a visual pyramid to figure this out..

CREATE TABLE my_pyramid_scheme(empid, name, is_active)
AS
  SELECT id::int, (id+64)::int::"char", true
  FROM generate_series(1,7) AS gs(id);


Now you've got

 empid | name | is_active 
-------+------+-----------
     1 | A    | t
     2 | B    | t
     3 | C    | t
     4 | D    | t
     5 | E    | t
     6 | F    | t
     7 | G    | t


So first we need to know what tier they're on... For that we use 2n-1. For example, if your number is 1.

2n-1 = 1
2n = 2
n = 1 -- you're on tier one


Now if your number is 6

2n-1 = 6
2n = 7
n = 3.5 -- trunc(3.5) you're on tier three.


So after you know what tier they're on. You need to find how much of a bonus they get.. That's a function of the tier they're on, and the maximum amount of tiers, to find that take the largest number and run it through the same process. With those two things, you typically have some geometric function that determines "incentive."

And, now you can run illicit drug cartel or compete with Herbalife.

Disabling members

If in the above you ever set is_active = false run these super fast operations on the row_number..

SELECT *, row_number() OVER (ORDER BY empid) AS newid
FROM my_pyramid_scheme;