Click here to Skip to main content
14,488,989 members
Rate this:
Please Sign up or sign in to vote.
See more:
what is the best practice to process the items stocks ?

What I have tried:

i searched and i founded two method :

1- Is it better to create a single row for each item (depending on its properties) and increase the stock field in this row or decrease it depending on the process?

--------------------
- item --- stock ---
- x    --- 5    ---
--------------------


b>2-create a row (record) for each operation, ie, if a purchase occurs, a row is created and in the incoming field = 10, and if a sales occurs, outgoing(any other expression) = 5?

----------------------------------
- item --- income --- outgoing ---
- x    --- 10     --- 0       ---
- x    --- 0      --- 5       ---
----------------------------------


i noticed that method 1 cause problem with the stock when i used OnDuplicateKeyUpdate or INSERT IGNORE in MySqlBackup.NET - MySQL Backup Solution for C#, VB.NET, ASP.NET
Posted
Updated 9-Jul-19 12:08pm

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

The way I would do it would be to have an inventory table as well as an inventory adjustment table.
CREATE TABLE dbo.Inventory (
  StockNumber INT  PRIMARY KEY,
  Quantity    INT,
  -- other properties
)
CREATE TABLE dbo.InventoryAdjustment (
  AdjustmentID INT PRIMARY KEY,
  StockNumber  INT,        -- can be indexed or foreign key to Inv.StockNumber
  AdjDate      DATETIME,
  AdjAmount    INT,
  -- other properties
)


And when an adjustment was made, you would do an update on Inventory AND an Insert on InventoryAdjustment
UPDATE Inventory
SET    Quantity = Quantity + @Adjusment
WHERE  StockNumber = @StockNumber

INSERT InventoryAdjustment (StockNumber, AdjDate, AdjAmount)
VALUES (@StockNumber, GetDate(), @Adjustment)


This will allow you to see the Items and Current stock level with one table, as well as the log of individual adjustments on the item.
   
Comments
Golden Basim 11-Jul-19 5:34am
   
in sales operation ?
Quantity = Quantity - @Adjusment
MadMyche 11-Jul-19 9:46am
   
It should be @adjustment, and would be the value of the quantity sold. Both the UPDATE and INSERT scripts would be fired
Golden Basim 12-Jul-19 6:51am
   
i i have stock with two different expired date (same item) , i should to create two record in "Inventory" ?
MadMyche 12-Jul-19 10:10am
   
There are different trains of thoughts on this; a system I did for local ambulance service used a third table for tracking "Lot" information, and the report programming generated separate sheets for those items inventory counts

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100