Deduct "money" from two columns in rows and split if you don't have enough in each

Hi, I have two columns [MAIN_BALANCE] + [DEPOSIT_BALANCE].
image

What i’m attempting is so it can deduct from both columns if it needs to. I basically need it to be so that if something costs £2.00 & you have £1.00 in main balance and another £1.00 in deposit balance it will then be able to check that and deduct the £1.00 from each so they both go to 0.

My current validation to check the funds are available is below and is basically main + dep added together:

            if(!$marketplace['getUserBalance'] >= $pprice) {
                $data['formData']['global_err'] = 'Insufficent Funds! <a href="#">Get more credit!</a>';
            }

My issue is being able to deduct from two columns at the same time.

Currently I came up with this but it does not accomodate the example mentioned above.

                    if($getConstantUser[0]['dep_balance'] >= $pprice) {
                        echo' enough in depost deduct from deposit';
                        die();
                    } elseif($getConstantUser[0]['main_balance'] >= $pprice) {
                        echo 'enough in main deduct from main';
                        die();
                    } else {
                        die('somehow deduct from both');
                      // deduct from both
                    }

Thanks very much.

Well you’ve already defined your test:

Check to see if the sum of the balances is more than the price.

The trick comes in your logic for the subtraction:
Do you want to prioritize one balance over another?
Do you want to equalize the balances as much as possible?
Do you want to equalize the subtraction as much as possible?

Let me give you some examples to ponder over.

Your item(s) cost… 5$.

Your main balance has 16$ in it; your deposit balance has 20$ in it.

You could… prioritize one balance over the other. Subtract 5$ from one or the other.

Lets say instead your balances are 4$ and 2$.
You could still prioritize - say that you prioritize deposit balance. that would make your Deposit balance 0, and your main balance 1$.

You could equalize the balance - we know that after you’re done, you’ll have 1$ in total left. You could do it so that each balance has 0.50$ in it.

You could equalize the subtraction - in this case, it would result in the same as the Prioritze case, and end up with 1$ in the main deposit. But consider an alternative example:

Your balances originally are 4$ and 3.50$. You want to spend 5$. Balancing the subtractions, you’d subtract 2.50 from both, and end up with 1.50 in your main balance, and 1$ in your deposit balance.

1 Like

I would prioritise dep balance over main balance.

Please can you do some sample code for:

Your balances originally are 4$ and 3.50$. You want to spend 5$. Balancing the subtractions, you’d subtract 2.50 from both, and end up with 1.50 in your main balance, and 1$ in your deposit balance.

Thanks

Well you’ve given me two answers. You want to prioritize dep balance, but balance the subtractions, which is the opposite of a prioritization.

The pseudocode for prioritization (my version) is:

If you can pay for the item using the sum:
   Subtract the price from the prioritized balance.
   If the prioritized balance is negative:
     Add the prioritized balance to the non-prioritized balance.
     Set the prioritized balance to 0.
   Endif
Endif
2 Likes

The pseudocode for a balanced subtraction is roughly:

If you can pay for the item using the sum:
   Subtract half of the price from both balances.
   If the dep balance is negative:
     Add the dep balance to the main balance.
     Set the dep balance to 0.
   Else If the main balance is negative:
     Add the main balance to the dep balance.
     Set the main balance to 0. 
   Endif
Endif

(Note that it is impossible for both balances to be negative, because the outer if condition prevents that.)

1 Like

@jack55, The problem here is in your two column balance approach.

With the correct DB structure and proper query’s you can avoid code gymnastics.

Your DB should have a single column with debits and credits. You can then query the DB to get the info you want.

Here is an example transaction table.

mysql> Select * FROM transactions;
+----------------+------------+--------+------+
| transaction_id | transdate  | amount | type |
+----------------+------------+--------+------+
|              1 | 2018-10-26 | 10.00  |    1 |
|              2 | 2018-10-26 | -5.00  |    1 |
|              3 | 2018-10-27 | 20.00  |    1 |
|              4 | 2018-10-27 | 50.00  |    2 |
|              5 | 2018-10-27 | 10.00  |    2 |
+----------------+------------+--------+------+

The type column designates whether you made the transaction (type 1) or someone else (type 2) which I will get into.

This query will give you a breakdown of the Deposits, Debits, and the running balance.

mysql> SELECT transaction_id
     , type
     , transdate
     , CASE 
        WHEN amount >= 0 THEN amount ELSE ''
       END as Deposit 
     , CASE 
        WHEN amount < 0 THEN -amount ELSE ''
       END as Debit 
     , @bal := @bal + amount as balance
FROM transactions
     JOIN ( SELECT @bal:=0) openbal;
+----------------+------+------------+---------+-------+---------+
| transaction_id | type | transdate  | Deposit | Debit | balance |
+----------------+------+------------+---------+-------+---------+
|              1 |    1 | 2018-10-26 | 10.00   |       |      10 |
|              2 |    1 | 2018-10-26 |         | 5.00  |       5 |
|              3 |    1 | 2018-10-27 | 20.00   |       |      25 |
|              4 |    2 | 2018-10-27 | 50.00   |       |      75 |
|              5 |    2 | 2018-10-27 | 10.00   |       |      85 |
+----------------+------+------------+---------+-------+---------+

This query will give you the running balance of deposits not made by you.

 SELECT transaction_id
     , type
     , transdate
     , CASE 
        WHEN amount >= 0 THEN amount ELSE ''
       END as Deposit 
     , @bal := @bal + amount as balance
FROM transactions
     JOIN ( SELECT @bal:=0) openbal
      WHERE type = 2;

+----------------+------+------------+---------+---------+
| transaction_id | type | transdate  | Deposit | balance |
+----------------+------+------------+---------+---------+
|              4 |    2 | 2018-10-27 | 50.00   |      50 |
|              5 |    2 | 2018-10-27 | 10.00   |      60 |
+----------------+------+------------+---------+---------+
2 Likes

In addition, with such a setup you can explain why an account has a certain balance, other than “because the database said so”. In other words, you get audit-ability for free!

1 Like

Worked perfectly!

1 Like

Thankyou! When i have time to restructure my experiment i will use your method.

This was my fix for the time being:

                    if($getConstantUser[0]['dep_balance'] >= $pprice) {
                        //purchase item with deposit balance
                        $this->m->decreaseDepBalance($options[0]['user_pin'], $pprice);
                        //set transaction to 1
                        $this->m->updateTransaction($formData['creditId']);
                    } elseif($getConstantUser[0]['dep_balance'] <= $pprice) {
                        //move deposit money to main
                        $this->m->increaseWithBalance($options[0]['user_pin'], $getConstantUser[0]['dep_balance']);
                        //set deposit to 0
                        $this->m->decreaseDepBalance($options[0]['user_pin'], $getConstantUser[0]['dep_balance']);
                        //buy with main balance
                        $this->m->decreaseWithBalance($options[0]['user_pin'], $pprice);
                        //set transaction to 1
                        $this->m->updateTransaction($formData['creditId']);
                    }

Thanks again

1 Like

@benanamen. I like your sollution a lot, but I don’t see the problem with the two column balance approach? I recently saw a lot of companies (crypto curenncy companies specially) using a main balance and a deposit balance. So my question is why can’t or better should OP not take that approach?

1 Like

Depends on what you’re modelling. If you’re modelling a single account, then the transaction type is great and preferred, especially if you need to track things like… if your account goes into debt. If you’re modelling two separate accounts (say, a savings and checking), then you’d have to double up the transaction type, and you’d end up right back at the same issue the OP described if your question was “Can I afford this between my two accounts”.

1 Like

Though, as we’re dealing with a fixed point system, that does remind me - if you do go with the equalized system, OP… beware of rounding errors when dividing.

(If my item costs 10.25$, and i divide the cost between two balances, i charge… 5.125 to each. But it’s a decimal system. So I round the 5.125, which makes it 5.13… but if i charge that to both accounts, the person actually paid 10.26$…i’ve overcharged them.)

Better? Added the number format

                    if($getConstantUser[0]['dep_balance'] >= $pprice) {
                        //purchase item with deposit balance
                        $this->m->decreaseDepBalance($options[0]['user_pin'], number_format($pprice, 2));
                        //set transaction to 1
                        $this->m->updateTransaction($formData['creditId']);
                    } elseif($getConstantUser[0]['dep_balance'] <= $pprice) {
                        //move deposit money to main
                        $this->m->increaseWithBalance($options[0]['user_pin'], number_format($getConstantUser[0]['dep_balance'], 2));
                        //set deposit to 0
                        $this->m->decreaseDepBalance($options[0]['user_pin'], number_format($getConstantUser[0]['dep_balance'], 2));
                        //buy with main balance
                        $this->m->decreaseWithBalance($options[0]['user_pin'], number_format($pprice, 2));
                        //set transaction to 1
                        $this->m->updateTransaction($formData['creditId']);
                    }

Thanks and an issue raised by @benanamen was race conditions. How do I fix this but as a reminder it will not be going in a live environment. Just an experiment.

Thanks again everyone!

1 Like

What the OP should have first posted…

I want to create a marketplace clone of codester.com that has a business rule that you can only withdraw money from sales and not your own deposits. What would be the best approach?

Hopefully someone here has already worked on a marketplace script. I have not, so I will have to do some research.

2 Likes

@m_hutley, Maybe i don’t understand it right, but i think this is going in a wrong direction. The actual question was/is

So only when there is not enough money in the main balance take the remaining from the other account, otherwise only the main balance should be deducted. The way I read the question has nothing to do with a prioritized or equalized deduction.

Maybe I’m wrong but that is what I read

1 Like

Keep in mind, what the OP posted is how he thinks he should solve his real problem which is how does he create a clone of the marketplace script codester.com. (Per his PM).

1 Like

Okay, fair enough. I could only refer to the actual question and some of the answers given

Sure, I get it. I just happen to have a keen sense for spotting XY Problems so I PM’d the OP to find out what we are really dealing with. I am not one for spending time trying to solve peoples X problems.

Now that we are all clear what the OP is really trying to do we can provide better answers. I have not worked on a marketplace script before, but my initial thoughts are that the accounts module is no different than a banking application. We are dealing with real money so it is imperative that this portion is done correctly. The only difference so far that I know of is there is a business rule that you can only withdraw money from sales and not from any deposits you made to make purchases. The previous SQL example takes care of this as it designates the source of a deposit.

Since we are dealing with real money, processing should be handled by the DB using transactions with rollback. Code is not the place to attempt money operations. As best as I can tell for now, the DB structure for this should be a single account per user with a single amount column as I showed previously and as @m_hutley so wisely stated…

If you’re modelling a single account, then the transaction type is great and preferred

1 Like

Thanks again! All help much appreciated! I would love to know how to do it properly! :slight_smile:

Since your current skill level does not allow you to just start building it I would suggest you do what I was going to do, download several free marketplace scripts and see how people are doing it. You are likely to see a pattern after looking at a few. You can also search Github for markeplace and read code.

1 Like