Mysql – Recursive Seller Account with Balance

I need to build a database for managing Short Messaging Service (SMS).

In this I have 2 types of users

1. Reseller

   • Must have starting SMS balance for example: 1,000 – credit table
   • He can sell unlimited SMS to immediate child users or resellers – credit table

2. End User

   • get SMS balance recharged from reseller – credit table
   • can send SMS – which is captured in billing table

As noted above, there's a hierarchical relationship involved here. Each reseller has a set of other resellers and/or end users that they sell to.

I'll have a single users table that will hold both resellers and end users.

users:

    user_id  |  parent_id  |      Name       | type 
    ---------+-------------+-----------------+---------
        1    |    NULL     | Head Reseller   |  reseller
        2    |    1        | Leaf Reseller 1 |  reseller
        3    |    1        | Leaf Reseller 2 |  reseller
        4    |    2        | End User 1      |  user
        5    |    2        | End User 2      |  user

credits:

+----+---------+---------+--------+
| id | sold_by | sold_to | credit |
+----+---------+---------+--------+
|  1 |    NULL |       1 |   1000 |
|  2 |       1 |       2 |   2000 |
|  3 |       1 |       3 |   5000 |
|  4 |       2 |       4 |   2000 |
|  5 |       2 |       5 |   2000 |
|  6 |       2 |       4 |   8000 |

billing:

+----+---------+-----------+--------+
| id | sent_by | parent_id | credit |
+----+---------+-----------+--------+
|  1 |       4 |         2 |    500 |
+----+---------+-----------+--------+

-- telecom sold to HeadReseller bought 1000
INSERT INTO `reseller`.`credit` (`id`, `sold_by`, `sold_to`, `credit`) VALUES (NULL, NULL, '2', '1000');
-- HeadReseller sold to 2 and 3
INSERT INTO `reseller`.`credit` (`id`, `sold_by`, `sold_to`, `credit`) VALUES (NULL, 1, '2', '2000'),(NULL, 1, '3', '5000');

-- LeafReseller sold to EndUser and AnotherEndUser
INSERT INTO `reseller`.`credit` (`id`, `sold_by`, `sold_to`, `credit`) VALUES (NULL, 2, '4', '2000'),(NULL, 2, '5', '2000');
-- sold some more to EndUser
INSERT INTO `reseller`.`credit` (`id`, `sold_by`, `sold_to`, `credit`) VALUES (NULL, 2, '4', '8000');


-- check balances of individual
select sum(credit) from credit where sold_to= 4 
-- 10000 EndUser
select sum(credit) from credit where sold_to= 4
-- 2000 AnotherEndUser

-- virtual or total sold by LeafReseller
select sum(credit) from credit where sold_by = 2
-- 12000

-- EndUser tries to send 5000 SMS: fails
check balance of EndUser select sum(credit) from credit where sold_to= 4
check balance of LeafReseller select sum(credit) from credit where sold_to= 2
check balance of HeadReseller select sum(credit) from credit where sold_to= 1


-- EndUser tries to send 500 SMS: succeeds
check balance of EndUser select sum(credit) from credit where sold_to= 4
check balance of LeafReseller select sum(credit) from credit where sold_to= 2
check balance of HeadReseller select sum(credit) from credit where sold_to= 1

-- after success, enter to billing table
INSERT INTO `reseller`.`billing` (`id`, `sent_by`, `parent_id`, `credit`) VALUES (NULL, '4', '2', '500');

-- current balance of EndUser - 11500
select sum(credit) from credit where sold_to= 4
-- balance = 12000
select sum(credit) from billing where sent_by = 4  
-- sent = 5000


-- EndUser tries to send 600 SMS: fails because of less balance in HeadReseller(500)
check balance of EndUser select sum(credit) from credit where sold_to= 4 
-- x = 12000 
select sum(credit) from billing where sent_by=4 or parent_id = 4  
-- y = 500
-- x-y = 11500 success


check balance of LeafReseller select sum(credit) from credit where sold_to= 2 
-- x= 2000 success
select sum(credit) from billing where sent_by=2 or parent_id = 2  
-- sent = 500
-- 2000-500 = 1500 is > 600 success

--check balance of HeadReseller 
select sum(credit) from credit where sold_to= 1 
-- x = 1000
select sum(credit) from billing 
-- y = 500 -- checked entire table, if one level addition in hirerachy will not work. All childrens' billing needs to be considered as parent's count:
-- x 1000- y 500 = 500 is > required 600 fails

Note: sold should be the total balance of the reseller's immediate children. So, HeadReseller has 7,000 sold (balance from the two leaf resellers), not 19,000 (balance of all descendants).

To simplify I use below:

• Actual: sum of SMS purchased from immediate parent
• Virtual: sum of SMS sold to immediate child reseller or user

Challenges:

1. When an end user tries to send 10K SMS, I need to check actual balance of all the parents. In the above case it fails because no parent has 10k actual balance.
2. How to manage transaction table so calculation will be faster?
3. The number of SMS sent by the End User will be considered as the balance reduction of all parents.

Update: I know below method is not optimized. But i'm trying best of my knowledge

I Tried below query, but not able to use 'T2.id' to cound children_billing.

SELECT 
T2.id, 
T2.name, 
(select sum(credit) from credit where sold_to= T2.id) as credits, 
(select sum(credit) from billing where sent_by= T2.id) as my_billing,
(select sum(credit) as child_billing from billing where sent_by in (select Level FROM (
   SELECT @Ids := (
       SELECT GROUP_CONCAT(`ID` SEPARATOR ',')
       FROM `users`
       WHERE FIND_IN_SET(`parent_id`, @Ids)
   ) Level
   FROM `users`
   JOIN (SELECT @Ids := T2.id) temp1
   WHERE FIND_IN_SET(`parent_id`, @Ids)
) as x)) as child_billing
FROM (
    SELECT
        @r AS _id,
        (SELECT @r := parent_id FROM users WHERE id = _id) AS parent_id,

        @l := @l + 1 AS lvl
    FROM
        (SELECT @r := 4, @l := 0) vars,
        users m
    WHERE @r <> 0) T1
JOIN users T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

Fiddle

As I'm facing this challenge for the first time I don't have proper keywords in the explanation.