Inventory Q'ty in and out

No matter how you design it, if you have the same data in two places, it will eventually get out of sync.  

How will you reconcile when  sum(StockIn) - sum(StockOut) doesn't equal TotalQty?

Hi,

Of course we try to avoid redundancy because it has to be maintained.
But in that case, if you need to know the current quantity, the only efficient way is to store it.
I cannot imagine an applications re-calculating stock movements from the beginning of the year each time it need to know the current quantity in stock.

Now, you have several solutions to maintain it: application, triggers, materialized views.
What I would use is to always call a stored procedure to record stock movement, and that procedure will be the only way where it has to update the quantity.
But materialized view may be a good solution as well.

Regards,
Franck.

Hi, i am also agree with franckpachot, basically we have two levels in most of the invertory systems, product defination and its transactional table(where in/ out records).
the best way to maintain such case is to calculate a summary field in product level table, such as CurrentQty. and the date wise detail avaiable into inverntory table(transactional table). its the simple way which i found most of the system.

Instead of having SerialNumber, DateIn, DateOut, StockIn and Stockout fields, could you not just have SerialNumber, Date and StockQty fields.

If stock is going out, record it as a negative, and if stock is coming in, record it as a positive. This reduces the record overhead, and to calculate the current quantity in stock for any given Serial Number you can simply run a query like:
     
SELECT SUM(StockQty) AS CurrentStockLevel FROM StockTable WHERE SerialNumber = xyz

If you want the current stock for all products, just add a GROUP clause

     SELECT SUM(StockQty) AS CurrentStockLevel, SerialNumber FROM StockTable GROUP BY SerialNumber

Just a different way of achieving what you want without the need to replicate data (which will cause you problems at some point in the future)

Hi yodercm,
i agree with you

i'm new in inventory system. pls help to provide more information or sample.

Hi ChrisStanyon,

it is you mean something like below.
SerialNumber      Date            StockQty
 121109001        12/11/09          -1
 121109002        12/11/09           1

the Date field should record the stock in date or stock out date?
how to track  the SerialNumber purchase date ?

and like franckpachot and haroon-rashid say it will be very slow if we re-calculating stock movements from the beginning of the year each time it need to know the current quantity in stock.

pls advice

aarontham,

The date field in my example would be used to record stock movement. Doesn't matter whether it's in or out. So to record a stock purchase, you would simply record a date, and a positive StockQty. i.e If you purchased 100 of item XYZ on the 12/11/09 then simply insert a record such as :

SerialNumber     Date             StockQty
XYZ                        12/11/09     100

The fact that it's a postive number will show that it's stock in. I'm guessing that stock only comes in when it's purchased.

With regard to the time taken to calculate the TotalQty, this is what databases are designed for. If you have several million records then there may be a slight performance issue. If this is the case, you may consider using Stored Procedures to calculate the TotalQty. That will work quicker than running queries. To run a SUM query on several thousand records should only take a fraction of a second. I guess testing this with fully populated tables is the only way to know for sure.

HI ChrisStanyon,

i don't think it will work if i follow your design. because i need to track serial number too.


actualy i have 2 table here. i table where i store the partnumber and Desc. the other where i store the serial number.

You can still do it that way. You'll just need an 'id' field in the Stock table that matches the id field in the Inventory table. You'll then use a JOIN in your queries. That way you can find out current stock levels for any given Serial Number or Part Number

ok aarontham, please review this design and i will hoping that i give u good performance. :D

Part Table:

1) Part_Number
2) Description
3) Current Stock

Stock Table:
1) Part_Serial
2) Date
4) Quantity
5) Flag (its an important field which indicate the transaction type like stock in and out and may be some other values like return and adjustment etc).

HI Haroon,

1) Part_Number - OK
2) Description -OK
3) Current Stock - IS THIS TOTAL STOCK Q'TY

Stock Table:
1) Part_Serial - OK
2) Date - THIS IS FOR STOCK IN DATE OR STOCK OUT DATE?
4) Quantity - BECAUSE THE SERIAL NUMBER IS ONLY FOR 1PCS OF STOCK. DOES THIS FIELD STORE 1 FOR STOCK IN AND -1 FOR STOCK OUT?
5) Flag (its an important field which indicate the transaction type like stock in and out and may be some other values like return and adjustment etc). DO I REALLY NEED THIS FIELD?

BUT LIKE yodercm SAY IF OUT OF SYNC THEN I WILL HAVE PROBLEM.

PLS ADVICE

aarontham:

With Haroon's method, you still have the problem of possible update and delete anomalies. If records are added, updated or deleted from your transaction table, there is no guarantee that your inventory table is updated as well. In effect, you're trying to store the same data in two different places - not a good idea in a relational database.

Haroon says he agrees with yodercm, but she says "No matter how you design it, if you have the same data in two places, it will eventually get out of sync" - I fully agree with this.

If you set your tables up as below, does this not achieve everything you need without replication.

As I said before, if you have millions of records, there may be a performance issue. But you have to weigh this up against the integrity of your data. You are designing an inventory system, but if your stock levels are incorrect because the duplicated data has gotten out of sync, then the whole system fails to be fit for purpose.

Do you have any ideas about the number of records you are likely to be working with. Remember databases are designed specifically to work with data, so it can easily handle tens, or hundreds of thousands of records.

It not really a great idea to hack the data to tweak performance. After all, a database is all about, well, Data!

Inventory Table
---------------
PartId
PartNumber
PartDescription
 
Transaction Table
-----------------
PartId
SerialNumber
Date
Quantity

Select allOpen in new window

hahah, ChrisStanyon if you are doing home work for normalization then its good to implement it normalization concept fully. basically for performance many time we do de-normalization in big and real time system.

in real time system there are not only single query which do every thing every time :), in simple word for stock in process there are more then one query, its look like store proc. but such system need record many other things like calculations and logs etc.

data entry not a gig deal in such system (update, delete, insert), but data fatching is most important for viewing data or reporting bez most of the time end user just see the data.

im also say "its totally programmer responsibility to takecare of such field if some transaction insert or update any date into detail, its must be modify summary field accordingly."

sync is not a issue, why programmer miss it ?? if he know about parent child relation. :)  

Hi Guys,

after i do some study. i don't think a program will out of sync. we can use SQL transaction to do update for 2 or more table right.
 

i totally agree with you.

You can use any queries you want to update 2 or more tables, but sooner or later there will be an error on one of them, and your data will be out of sync.

Tables get small errors -- that's why the query for REPAIR TABLE exists.  If you do use redundant data, then you definitely will need to also create a way to re-sync your tables from time to time.

yodercm, in good system most of the time we will do multiple action on update and insert operations, there are no simple or single line operation to record inventory.  the transaction use for surity, bez we were write multiple operations. i h ope you understand.
at the same time there must be some way to check it, like reports etc which show the transaction where the balance out etc. or a process for calculation current values.
 

HI yodercm,

i found ( SQL transaction ) is design to do multiple operation and if something happen it can rollback.
but i haven't use it. and this is new to me.
maybe you can advice.