0

I'm having 1000+ Products. I need to maintain the stock on the daily basis.

Product Table Schema

CREATE TABLE Product (
    ProductId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(30) NOT NULL,
)

Stock Table Schema

CREATE TABLE StockInfo (
    StockInfoId INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    StockDate Date NOT NULL,
    ProductId INT NOT NULL,
    OpeningStock INT NOT NULL,
    Purchase INT DEFAULT 0,
    Sales INT DEFAULT 0,
    ClosingStock INT DEFAULT 0
)

Each day the table StockInfo gets updated. The column OpeningStock is updated by cron jobs, the logic is:

last days ClosingStock = OpeningStock + Purchase - Sales

Todays OpeningStock = last day ClosingStock

This approach consumes larger rows and hence the performance is impacted.

Note: Every day each and every product has at least one sale and purchase

Kindly assist me to redesign this logic.

B.Balamanigandan
  • 4,713
  • 11
  • 68
  • 130
  • Each day you add a new "StcokInfo" for each product ? – Xire Aug 30 '17 at 15:52
  • 2
    1000 products are nearly nothing for mysql and shouldn't be any kind of issue. Having Purchase and Sales in the same table is not what you want to have, having one ProductId as varchar and the other one as int is an issue as well if you want to join the two tables. Now what's really unclear to me is why you let the stock be calculated once a day. It's much easier to do this live - every other field like openeningStock can be calculated very easy... – baao Aug 30 '17 at 15:53
  • @Xire - Every day I'm inserting a record for all records only if there is at-least a Purchase or Sales. Probably every day there is at-least one Purchase and Sales for every products. – B.Balamanigandan Aug 30 '17 at 15:54
  • @baao - I'm using a rental server from one of the host, within 3 months it grow upto approx 200 K records. In this scenario the hosted server brings a restriction otherwise we need to upgrade a dedicated server. So, I'm in the place to optimize the structure. – B.Balamanigandan Aug 30 '17 at 15:56
  • @baao - `[StockInfo].[ProductId]` is `INT`. I update the question, its a typo mistake. – B.Balamanigandan Aug 30 '17 at 16:00
  • Did you try to partinionning the table stockinfo ? Why do you have to keep the history? You can not create a purge to another table? – Xire Aug 30 '17 at 16:01
  • I'm having UI to see the past stock info. So, I'm maintaining all the records to fetch records based on date. – B.Balamanigandan Aug 30 '17 at 16:04
  • you could hash all the ids:purchases into a single column and the ids:sales into another in a new table, so there would only be one record per day. But, I think the more straight-forward approach is to upgrade the hosting. – i-man Aug 30 '17 at 16:10
  • How do you track returns? How do you track backorders? How do you know if the item has been purchased, but is still in stock and not delivered? There is a reason that these inventory systems cost thousands of dollars. Where are you keeping track of the transactions? If you want the simplest method, I would base your stock off the sum of the transaction line item quantities and calculated at run time. – Jacob H Aug 30 '17 at 16:14
  • This looks like a good attempt to me. `StockInfoId` seems to be redundant, and the `6` in parentheses just after it is, at best, meaningless, btu apart from that, carry on. – Strawberry Aug 30 '17 at 16:14
  • You have to differentiate your hot data from your cold data. If your cold data (here: the stocks of the past days) may not be necessarily useful for your daily life. It is better to put them in a separate table and to modify the requests of the UIs that need the past. After optimizing your table you can create partitions and check your indexes. – Xire Aug 30 '17 at 16:15

0 Answers0