MySQL Forums
Forum List  »  PHP

Stock management: Keep ongoing totals, or calculate from audit trail
Posted by: Chris Reid
Date: April 18, 2012 06:59AM

Hi all, this is my first post.

I am developing a stock managment system for a furniture store and was wondering which of the following two practices to use to keep track of stock quantities?

We have a table for sales and one for stock purchased (or booked-in).

Should I:

1. Have a column for each stock variant with the number of stock held. This would be decremented on sales, and incremented on stock book-in. This is some duplication of data since as well as storing sales and booked-in stock, we are keeping a tally figure in the database. There may be discrepancies in the figures, but I guess there shouldn't unless if we use transactions on innoDB.

2. Use the tables for sales and one for stock purchase (booking in) and on each stock level query have the server calculate the total stock booked in since day one, and minus the total sold, or written off as damaged since day one.

Obviously 2 sounds more inefficient but would allow a proper audit trail. For example, you would know how it gets to it's figure of say 20 of a particular line in stock. After a few years, this option will be trawling through a lot of orders to get its figure.

I was at first worried that option 2 is more susceptable to data loss on failure but no more so than option 1 really - I need to ensure things are replicated and backed-up. Obviously the server is constantly going to be doing queries and maths with option 2 but only really trivial maths (SQL's sum function mainly).

Any thoughts or ideas? Particlarly those of you who have been involved in this king of project.

Chris

Options: ReplyQuote


Subject
Written By
Posted
Stock management: Keep ongoing totals, or calculate from audit trail
April 18, 2012 06:59AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.