3 Replies - 753 Views - Last Post: 29 September 2013 - 10:03 PM

#1 astonecipher  Icon User is offline

  • Major DIC Head
  • member icon

Reputation: 255
  • View blog
  • Posts: 1,295
  • Joined: 03-December 12

Application design assistance needed

Posted 29 September 2013 - 06:41 PM

I hope this is the correct area as it is not language specific. I am in the coding phase of an in-house inventory control system. The tables are designed as well as most of the forms for a a desktop application. Now however, I have been told they want the system to be able to store past records for future order forecasting. The question is, I have a table to hold the product information that ties to a table holding current counts and minimum stock requirements, how do I store past quantities by product && date without bloating the system? Obviously I am not going to add a new column to the qty table, I have thought about creating a table for each product to store the past amounts, but I am worried that the overhead may be to much to deal with given enough products added.

Is This A Good Question/Topic? 0
  • +

Replies To: Application design assistance needed

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 8367
  • View blog
  • Posts: 31,093
  • Joined: 12-June 08

Re: Application design assistance needed

Posted 29 September 2013 - 07:34 PM

Why not a simple log table.. every time you make a deduction from a product add to a log table for the:
product id
existing count
amount to change
date_entered.
Was This Post Helpful? 2
  • +
  • -

#3 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3161
  • View blog
  • Posts: 9,541
  • Joined: 05-May 12

Re: Application design assistance needed

Posted 29 September 2013 - 08:01 PM

Excellent suggestion! Just don't show that to an accountant otherwise they would suggest (demand?) something more rigorous.
Was This Post Helpful? 0
  • +
  • -

#4 GuageCage  Icon User is offline

  • New D.I.C Head
  • member icon

Reputation: 3
  • View blog
  • Posts: 5
  • Joined: 30-May 13

Re: Application design assistance needed

Posted 29 September 2013 - 10:03 PM

View Postastonecipher, on 30 September 2013 - 01:41 AM, said:

I hope this is the correct area as it is not language specific. I am in the coding phase of an in-house inventory control system. The tables are designed as well as most of the forms for a a desktop application. Now however, I have been told they want the system to be able to store past records for future order forecasting. The question is, I have a table to hold the product information that ties to a table holding current counts and minimum stock requirements, how do I store past quantities by product && date without bloating the system? Obviously I am not going to add a new column to the qty table, I have thought about creating a table for each product to store the past amounts, but I am worried that the overhead may be to much to deal with given enough products added.

The change in data has to be stored in order to report against it. Those bits will have to take up space somewhere to query against it in the future. However, seeing as your primary requirement seems to be forecasting for future use, you maybe be able to keep that additional storage to a minimum by overwriting previous data. Example: Add a table called forecasting with the following columns: productid,lstqtyday,daydate,weektodatetotal,lstqtyweek,weekdate,monthtodatetotal,lastqtymonth,monthdate,yeartodatetotal,lstqtyyear,yeardate
These columns could be overwritten as long as keeping record for extensive reporting is not an issue. This would allow for the least amount of data to be recorded for forecasting purposes. You could adjust the intervals that you store data and also report against it. When writing to it you could keep running totals in the "to date" fields. Your code could determine the right time to over write data. All in all each product would require only one row.
However, I would suggest to the powers that be to consider doing things the right way now and record all changes asap for future use. The way I have described would only allow for a forecasting application of minimal use.

This post has been edited by GuageCage: 29 September 2013 - 10:04 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1