CODEWITHSUNDEEP
database-designbusiness-logicbillingapplication-designweb-application-design
tbicr
tbicr

Reputation: 26070

How implement billing system

I have two collection in database income_funds and expenditure_funds:

income_funds
    timestamp
    amount
    status (not confirmed, confirmed, refunded)

expenditure_funds
    timestamp
    amount

I need get current account balance and get reports about using. How better implement billing logic with this collections?

The simplest way (without additional data):

  1. to get account balance sum(confirmed income_funds) - sum(expenditure_funds)
  2. to get report get income and expenditure funds by period

But I don't like that I must all time calculate account balance.

Add account collection:

I will add account collection:

account
    balance
  1. to get account balance just get account.balance
  2. get report as previous method

But I don't like that I must change balance any time when any funds changed (added or changed status). To verified account balance I always must calculate all funds as in first method. If I get mistake I will don't know how and when it happened.

Add period collection:

I will add period collection:

period
    is_closed (yes, no)
    start_timestamp
    end_timestamp
    start_balance
    end_balance
  1. to get account balance return last period.end_balance
  2. get report as previous method but easy get timestamps by period

I must change end_balance any time when any funds changed in period. But I can verify balance by period and if will have mistake I will know that it's happened on this period.

Last method likes me most of all, but maybe somebody can give me advice in my conclusions or know other solution.

Upvotes: 1

Views: 2477

Answers (2)

Chris Travers
Chris Travers

Reputation: 26464

I think your design is somewhat problematic. I would suggest something a bit different (very simplified here, note I build accounting software professionally):

account (id, account_number, description, account_type)
journal (id, name, description)
journal_entry (id, journal_id, reference, description, transaction_date, memo)
journal_line (journal_entry_id, account_id, amount)
invoice (id, journal_entry_id, customer_vendor_id, duedate, terms)
invoice_line (id, invoice_id, goods_service_id, qty, sellprice, discount)
account_checkpoint (id, account_id, debits, credits, balance, end_date)

Then what you do is have a trigger on journal_entry such that any entry here transaction_date is earlier than max(account_checkpoint.end_date) is denied. This way you roll forward from the last closed period and through open periods. This makes reporting a lot easier and minimizes what you actually have to aggregate.

Other checkpoint tables can be added as well for periods, so you can close AR more frequently than AP or vice versa, if a business wants to. In this way the checkpoints serve both as closing points and as points where you can roll forward to generate reports from.

Upvotes: 2

Riho
Riho

Reputation: 4593

I think I have used all 3 options in my projects over the time. It is really up to you to decide which way to go, depending on your project's scope and possibilities.

If your database supports triggers then you could use separate Balance table and have update/insert/delete triggers in your *_funds table that update the data in Balance table. This ensures that no matter how the data changes, your balance is always correct. I would recommend to use this option.

If you don't have huge amount of data and you don't need to calculate the balance very often then you could just recalculate the sums. IMHO it would make things little bit easier to store both incoming and outgoing funds in one table. Add extra field (transaction) indicating the transaction direction (1 for incoming and -1 for expenses). Then you can just get the balance by doing

SELECT SUM(amount*transaction) from funds

And thirdly I have used the option to have periodic balances in old times when I stored the data in monthly files. Whenever new files was created then first record was beginning balance, calculated on the spot from previous month data. I also provided the possibility to recalculate the balance in rare cases when previous month data had changed. This workes OK when user is quite aware what he is doing - for example in accounting systems where accountant knows that he needs to recalculate the balances.

Upvotes: 1

Related Questions