0

I have the following table that holds the purchase order

po_id
supplier_id
transaction_type  -- either cash or credit

and other table that holds the items

po_id 
product_id
quantity
unit_price

Now, how should I handle payment transactions, as payments can either be cash, or credit. If credit, the payment can be made in multiple transactions. and if Cash, there should not be any outstanding payments for this particular purchase order. How can I design a schema that identifies outstanding amounts per purchase order and reflect that in a double entry accounting schema.

fareed
  • 127
  • 1
  • 9

2 Answers2

1

You can create the table - Transactions: po_id, client_id, transaction_type, transaction_purpose_id (you can create the table transaction purposes (products, services, taxex and so forth) amount_paid, transaction_status (processing = 1, proccessed = 2, failed = 3, unknown = -1 and so forth).

Now, a clien can pay for an order however he wants. Taxex can be paid as well.

Vadzim Nemchenko
  • 88
  • 5
  • 1
    This solution falls down when someone wants to pay $50 on an order of $229.57. You aren't always going to have items that add up to exactly $50. Plus what do you do about tax? Typically you want that separated from the item cost for accounting purposes. You don't want to risk confusing having paid the government with the customer paid you. – Erik Mar 31 '17 at 15:50
  • 1
    Oh, a client can pay any amount on an order even when an order item is not equaled to that amount. I get it. I will edit my comment. – Vadzim Nemchenko Apr 01 '17 at 05:17
  • But payments does not depend on each item. Payments can be part of the whole purchase item, or sum of multiple purchase items regardless of the items in the purchase item. – fareed Apr 03 '17 at 17:51
  • it does not matter. By using my solution, you can handle any payments. You have one order which consists of a lot of items (or not). Every transaction will have po_id. The final sum will be stored in the table you described as the second (which contains po_id , product_id, quantity and unit_price). – Vadzim Nemchenko Apr 04 '17 at 07:06
1

Consider handling all money related transactions using a double entry accounting model. Any real world application will eventually need to handle the complexity that this model supports and it is a well understood design pattern.

See this question on this site and a similar question on Stack Overflow for more insights into this design.

Community
  • 1
Joel Brown
  • 12,529
  • 2
  • 31
  • 45