Click here to Skip to main content
15,797,984 members
Please Sign up or sign in to vote.
1.00/5 (3 votes)
Disclaimer: I am a beginner in the field of database.

I am trying to design a database in PostgresQL which will track the stock along with recording the sales and purchases of items. I intend to use this database as the model with EF and WPF using MVVM approach.

I have a table that records the current stock for each item. My problem is to adjust that stock every time a sale occurs or when we purchase the raw materials.

What I have tried:

I have thought of three basic ways to solve the problem.

The first one is trigger: every time a record is inserted in sales table or purchases table, the stock is adjusted accordingly.

The second one is to add up all the sales and then add up all our purchases and then find the difference between them. This poses a problem - if we delete older purchase records or sales records, the calculated stock would be wrong.

The third idea was to encapsulate the entry of each and every insertion of data in sales or purchases column through a stored procedure.

Could you please tell me which approach is the best?
Updated 16-Feb-17 12:29pm

1 solution

use a third table, a onHand table.

when you buy the item you add a record to the inventory table and add the amount bought to the onHand table for the item.
when you sell the item you add a record to the purchase table and deduct the amount from the onHand table for the item you sold.
Share this answer
Sabyasachi Mukherjee 16-Feb-17 17:29pm    
Would I use triggers for that? That was my first option, actually.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900