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.

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.

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.

  • 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 1 '17 at 5: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 3 '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 4 '17 at 7:06

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.