Database Design for Credit Purchase

database-design

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.

Best Answer

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.

Related Solutions

Advice needed regarding design of database schema keeping record of current stock

I have been working on same domain , and I appreciate the logic you trying to apply and schema also looks good just that material and item can be the same table as item will have no meaning without material and vice versa ,also add audit property to domain table as this will help to keep the track of changes as the system will grow multiple scenario will come in picture in production ex. Cancellation of order due to defect.

What I would recommend is create a event which will get fired on stock change / updated that will have old and new amount of stock so this event will be sufficient to defrentiate added / updated / reduced stock

And make purchases or all audit/stockhistory entity subscribe to this event so that for any reason the stock changes you don't need to make this calls and rest of the logic will be decoupled

Modeling a database for a printing company

This part in Item doesn't look right:

F.K = JID, OID
  JID REFERENCES PURCHASE (JID)
  OID REFERENCES PURCHASE (OID)

I suppose the design is only on paper, as no DBMS would allow that, when the primary key of PURCHASE is (JID, OID). You need one (composite) Foreign Key constraint, not two:

  (JID, OID) REFERENCES PURCHASE (JID, OID)

Now this foreign key belong to Puschase_Materials (or Purchace_Items) and not to Materials (or Item). With that in mind, the corrected schema would be:

PurchaseOrders (JID, OID, ODate)
PK = (JID, OID)
FK1 = (JID) REFERENCES Jobs (JID)

Items (IID, Stock, Description, Price)
PK = IID

Purchases_Items (IID, JID, OID)
PK = (IID, JID, OID)
FK1 = (IID) REFERENCES Items (IID)
FK2 = (JID, OID) REFERENCES PurchaseOrders (JID, OID)

The image shows an additional relationship, between Purchase_Materials and Jobs/PrintingJobs, which doesn't appear in the schema - and you don't really need it.

Since there is a foreign key from Purchase_Items to PurchaseOrders (or Purchase_Orders in the image) and a foreign key from PurchaseOrders to Jobs, you don't need a direct foreign key. Consistency is enforced through this indirect relationship.

There is inconsistencies in the naming, between the schema and the images. I assumed the following are renames:

Schema         - Image              - Code in this answer
---------------------------------------------------------
Jobs           - PrintingJobs       - Jobs
PurchaseOrders - Purchase           - PurchaseOrders
Item           - Materials          - Items
  (none)       - Purchase_Materials - Purchases_Items

Some table names are plural and others singular.
Some names have underscores between name parts, some not.

It would be best if you fixed those and decide on a naming convention (say all table names are plural, there are no underscores except in case X, etc).