Inventory system best method to do stock in and stock out

aarontham
aarontham used Ask the Experts™
on
i design a inventory system. what is the best method to do stock in and stock out in database.

like i should use only 1 StockTotal field.

or

i should use 2 field, TotalStockIn and TotalStockOut

or

is there any better advice on DB design.

 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
its not a good idea to store a total field,

you can always generate a total by calculation.
--

storing a total field means you have to update it in every insert or update commands, you will have to use triggers or you will have to have a logic in every stored procedure and script you use.
--

so i guess leave the Total fields to be generated while displaying only.

Regards

Commented:
You can have the TotalStockIn and TotalStockOut

and the calculated StockTotal field

StockTotal AS (TotalStockIn - TotalStockOut) PERSISTED

The best of 2 options

"its not a good idea to store a total field" - I don't agree. It depends on implementation and user needs.

Standalone total field is not necessary up to certain amount of data only. Once your data increase over some limit then the total calculation is so time consuming that it is better to keep it stored separately and have it ready for immediate reporting. It, of course, means all the disadvantages with duplicate updates, triggers etc.

Some implementations are calculating the totals just daily for fast DW reporting purposes. On-line web shops obviously need immediate and accurate values.

Several total fields is obvious solution. Not only Stock-in / Stock-out but Total Ordered qty (confirmed orders) and Total Reserved Qty (goods ready for dispatching) also.
OWASP: Forgery and Phishing

Learn the techniques to avoid forgery and phishing attacks and the types of attacks an application or network may face.

Author

Commented:
Hi guy,

what is the recomanded amount of data to need a standalone total field.
we are planing to integrate our inventory syatem to an on-line web store.



Top Expert 2010

Commented:
Dear pcelba:

i agree with your point if it was a datawarehouse as you said, because in a DW scenario you will want fast reporting and you will have to develop a star scheme and you might even have to denormalize the whole database for it.

but in case of aarontham:, he is developing an inventory system (an operational system), adding a total field will add overhead in the operation.

in my opinion i still suggest not having it as a field,

Regards
aarontham, how many transactions (goods movements) do you have in your live system?

The number of transactions where the calculated total could slow the system down depends on hardware and on the number of concurrent users.

In general if you have thousands of transactions every year then the total can be calculated without bad impact to performance. If you have thousands of transactions per day then you'll see the impact in a few months. You can solve it by monthly archiving and data consolodation, of course.

Also, I would say the overhead in online total field updates isn't so critical from CPU point of view. You just have to code it properly...
I recommend for TotalStockIn, TotalAmountIn, and TotalStockOut fields for each product.

Reason:
1. You can query for searching Cost of Good Sold (COGS) both FIFO or AVERAGE method. For example: By sum TotalAmountIn divided by TotalStockIn you can get cogs for average method for each product.
2. You can calculate your current stock for each product by SUM(TotalStockIn) - SUM(TotalStockOut).

Author

Commented:
Hi tikus,


your propose sound interesting. can you explaint how to calculate FIFO and LIFO.
and we should do this COGS on another table right.




 
FIFO and LIFO is a little bit complex. You have to code in your application and need another table like Purchasing Table.

Author

Commented:
Hi Guy,

if we choose to design a inventory table without total field. then how the table should look like.
 is it like below and where to do the stockin and stockOut.

ProductDesc   ProductPartNumber  
TV-LCD                   TV001  


pls advice
               
Top Expert 2010

Commented:
store all fields that cant be derived

e.g
ProductId
ProductName
ProductDesc  
ProductPartNumber  
ProductPrice
etc

and while displaying like in your Reports
you will need to derive your totalstockin and any other derived field

add the business logic for your Totalstockin field and display it

Author

Commented:
how to store stock cal.  is it new table ? pls provide some table sample
Top Expert 2010
Commented:
check out this old sample inventory database

http://www.taltech.com/TALtech_web/support/dde_sw/ddeaccess5.htm

it will help you

or check this site

they have lots of free samples (search for inventory) and see samples
http://www.planetsourcecode.com/vb/scripts/BrowseCategoryOrSearchResults.asp?txtCriteria=inventory+&lngWId=10

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial