0

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.

  • There's something unclear about the basic process. I don't understand how HeadReseller can have sold 7000 SMS credits, if he's only bought 1000. I would also expect that if Head Reseller sold 8000 SMS credits to Leaf 1, and Leaf 1 resold those 8000 credits to End User 2, then even if Head and Leaf 1 both had a current balance of 0, End User 2 would have a balance of 8000, and could proceed to send that many SMSs. This leaves me concerned about some of the fundamentals of the approach. Of course, that may simply be a lack of understanding of how this particular marketplace works. – RDFozz Jun 29 '17 at 20:19
  • With my previous comment in mind: I have worked with a system where each users' current balance could be calculated from the transaction table (actually, from a payment/adjustment table, plus a work done table); however, the current balance was maintained in the users' records. The is (at least technically) a denormalization of the data; however, given how often the balance information was referenced, vs. how often it changed, it simply made sense. It might for you, too (keeping in mind that very important "given how often" part). – RDFozz Jun 29 '17 at 20:24
  • @RDFozz please check updated question. and fiddle – Manjesh V Jun 30 '17 at 12:06
2
  1. This should be doable with a SUM in a recursive query. See: https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive
  2. That is tough when it can roll all the way up. You would need a lock on all the parents and it may be quicker to lock the whole table. This is a downfall of the design of having an unknown number of parent records affecting the balance.
  3. How would this work in practice if I sent only one message? Take a fraction of an SMS from each parent?

Do you have to follow this architecture? What if instead of unlimited layers of resellers, you had buckets that resellers were assigned to and could transfer to/from?

  • share me if you have any example links of similar implementation??. 1. I tried recursive query. 2. Minor inconsistency in data is allowed. 3. some people send one message in a fly, some people send millions in a fly. last: no. I can follow any optimized implementation, can you explain me bit more about bucketing – Manjesh V Jun 30 '17 at 11:59
  • I have never heard of a similar implementation because there are several logical (not technical) problems with this like I outlined in #2 and #3. By "buckets", I really just meant traditional accounts. Why can't you think of this like a traditional banking application where everyone has an account that value gets transferred into and out of? I don't grasp logically how the system could work with debiting the parent records. – CalZ Jun 30 '17 at 12:43
  • Here's one problem: say there are two people that send messages at the same time and they are both customers of resellers of resellers of resellers in a very big forest. If you need to know the balance of ALL the parents each time a message is sent, you necessarily need to freeze all the parents or what you thought was there won't be at the time you debit. In a traditional bank application, you only have to lock and the source and target accounts during a transfer which is bearable. In your scheme, you might lock half the organization. – CalZ Jun 30 '17 at 12:45
  • I'll check more about banking application, In simple words whatever sold by a reseller is a credit stored in the account. Always reseller need to keep minimum balance(==bought credits from one level up) so that childrens not run out balance. In more simple words the Head Reseller has to always try to keep balance higher than daily turnover. This feature is because resellers sells credits without any precondition, but for sending the sms he needs balance, balance calculated against the sms purchased from telecom operator at the top – Manjesh V Jun 30 '17 at 13:27
  • Why does the reseller need to have the credit? Shouldn't a customer have a balance before they can send a message? – CalZ Jun 30 '17 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.