5 Replies - 651 Views - Last Post: 14 February 2019 - 12:49 PM

#1 toronado455   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 18-January 09

accounting

Posted 30 December 2018 - 06:57 PM

For accounting, such as a deposit account, would it be better to have separate tables for deposits and withdrawals, or have both in the same table?
Is This A Good Question/Topic? 0
  • +

Replies To: accounting

#2 Martyr2   User is offline

  • Programming Theoretician
  • member icon

Reputation: 5417
  • View blog
  • Posts: 14,327
  • Joined: 18-April 07

Re: accounting

Posted 30 December 2018 - 07:19 PM

Personally I would put them together and call the table "transaction" because that is what it is. The only difference between a deposit and a withdrawl is the positive/negative of the amount. But the act of moving money is a transaction on an account.

But again, all depends on your program and what you want to achieve.
Was This Post Helpful? 4
  • +
  • -

#3 benanamen   User is offline

  • D.I.C Head

Reputation: 36
  • View blog
  • Posts: 239
  • Joined: 28-March 15

Re: accounting

Posted 31 December 2018 - 03:17 PM

What @Martyr2 said.
Was This Post Helpful? 0
  • +
  • -

#4 toronado455   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 18-January 09

Re: accounting

Posted 01 January 2019 - 12:28 AM

Cool. I like that approach. In fact, that's what I've been doing. I have a table called "transactions". And I have a column in that table of data type enum with values 'Credit', 'Debit' so that I can label the transaction accordingly. Do you think that is an appropriate use of enum?
Was This Post Helpful? 0
  • +
  • -

#5 benanamen   User is offline

  • D.I.C Head

Reputation: 36
  • View blog
  • Posts: 239
  • Joined: 28-March 15

Re: accounting

Posted 01 January 2019 - 10:54 AM

View Posttoronado455, on 01 January 2019 - 12:28 AM, said:

Do you think that is an appropriate use of enum?


No. Dont use enum. A positive or negative value will tell whether it is a credit or debit. 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 |
+----------------+------+------------+---------+---------+

This post has been edited by benanamen: 01 January 2019 - 11:37 AM

Was This Post Helpful? 1
  • +
  • -

#6 toronado455   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 18-January 09

Re: accounting

Posted 14 February 2019 - 12:49 PM

Thank you benanamen!

This works perfectly for me. The code you use to generate the balance is over my head. I need to study up on that. But magically it works. :smile:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1