Inventory system best method to do stock in and stock out

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

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.

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.

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).

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.

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
               

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

how to store stock cal.  is it new table ? pls provide some table sample