Inventory Q'ty in and out

aarontham
aarontham used Ask the Experts™
on
I'm designing a n inventory program with serial number. what is the best way to to keep track stock in and out data?

should it have a table with total q'ty like below
inventory table
InvAutoID   PartNumber        Desc                         TotalQty
   1               Hp04              HP presraio                       2

Serial number table
 InvAutoID   SerialNumber         DateIN       DateOut          StockIn      StockOut    
     1             101009001         10/10/09      11/10/09            1                   1
     1             101109001          10/11/09      00/00/00            1                   0
     1             121109001          12/11/09      00/00/00            1                   0

or without the TotalQty in the inventory table?
or any better design.
Comment
Watch Question

Do more with

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

Commented:
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.
Haroon Ur RashidSr. Software Engineer

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
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)

Author

Commented:
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

Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
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.

Author

Commented:
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.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
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
Haroon Ur RashidSr. Software Engineer

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

Author

Commented:
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
Sr. Software Engineer
Commented:
1) 3) Current Stock - IS THIS TOTAL STOCK Q'TY
yes its show total qty of a specific item(part) and i suggest you u place another fields for average price. whenever you want to calculate your total stock value just multiple current stock to avg price. :)

following are some example transactions for you understanding.

1) stock in   10 qty with price of 10 each then currentStock = 10 and avgPrice = 10
2) stock in   20 qty with price of 15 each then currentstock = 30 abd avgprice = 13.33
3) stock out 15 qty then currentstock = 15 (but avgprice  cannot be change *depending on ur bus).
-----------------------------------------------------------
2) Date - THIS IS FOR STOCK IN DATE OR STOCK OUT DATE?
yes, this date field will be used both cases stock in and out, the flag field will show that select date is stock in or our 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?
yes, it will be 1 or -1


i am also agree with yodercm, basically in database design we need to use summary fields(in your case its current qty) for quick response. its already calculated and sync with its detail.

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.
Most Valuable Expert 2018
Distinguished Expert 2018

Commented:
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

Open in new window

Haroon Ur RashidSr. Software Engineer

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



Author

Commented:
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.
 
Haroon Ur RashidSr. Software Engineer

Commented:
i totally agree with you.

Commented:
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.
Haroon Ur RashidSr. Software Engineer

Commented:
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.
 

Author

Commented:
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.

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