Help with design of a sales database design

Question

1.00/5 (3 votes)

See more:

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?

Posted 16-Feb-17 10:37am

Sabyasachi Mukherjee

Updated 16-Feb-17 11:29am

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Member 12556999 · Answer 1 · 2017-02-16T11:22:00

Solution 1

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.

Posted 16-Feb-17 11:22am

Member 12556999

Comments

Sabyasachi Mukherjee 16-Feb-17 17:29pm

Would I use triggers for that? That was my first option, actually.