How can I set up a database to record data history?

[davidd31415]

I would like to set up a database that tracks inventory and prices and also allows me to view inventory/pricing information from the past. How might I go about setting this up?

I'm thinking maybe the query will be the difficult part, as it could examine the date field of a record to see if it is current or past? If this is the right path, I'll experiment in this direction.

Thanks,

Dave

 

[lespaul]

Have you looked at the sample database, Northwind? It shows the proper way to handle inventory.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[davidd31415]

I just took a look at the Northwind database but it does not seem to have a history built into it. I would like to be able to see what inventory levels were as a function of time or see a "snapshot" from an arbitrary point in time.

 

[JoeAtWork]

I just took a quick look at Northwind and I wouldn't use it as a model for an inventory database because it stores Quantity as a field in the Products table. This is a No-No for inventory datase design. It leads to arbitrary and unaccounted for Quantity values (how did we get this value - nonone knows).

What you want is a Transactions table that records everytime a product comes in or goes out of the warehouse. Structure would be something like:

Transactions
ProductID
TransDate
InOut ("IN" or "OUT")
Quantity

Using such a table you could find out what the quantity of a product was at any point in time simply by summing the Quantity field.

Example, what was the quantity on January 20, 2006?

SELECT ProductID, SUM(Quantity) AS TotalQuantity FROM Transactions WHERE TransDate <= #01/20/2006# AND ProductID = 123 GROUP BY ProductID

You could record other useful information in the Transactions table. A good one for accountability is adding an EmployeeID.

 

[SkipVought]

I'd go with put and take transactions for qty, as well as pricing history with date and status.

Skip,

_[red][/red]

 

[davidd31415]

Thanks JoeAtWork, that sounds like a perfect solution!

 

[lespaul]

I thought that's what the Northwind database did, track transactions.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[cehowski]

You should have a Transaction table with the "Date", "Item", "Quantity", and "Cost" fields (at a minimum). And a product table with "Item", "Description", and "Unit Price" info (at a minimum).

You could calculate historical costs, by maintaining a Product Price History table, and calculating all the costs (old & new) on the fly when reports are prepared, but it is easier (IMHO)to calculate the cost at the time the transaction is posted, and save that info with the Transaction table. We actually calculate and save the "Cost" (the total) as well as the "Unit Price" at the time of the transaction, to make things easier for end-users in the future (a little denormalization can help sometimes).

 

[JoeAtWork]

I thought that's what the Northwind database did, track transactions.

Nope. It has an orders form, which is really just for creating and printing an invoice. The only place a user can adjust UnitsInStock is on the Products screen, which is rather unpractical. A real inventory application would automatically adjust quantity when products are received from supplier, products are sold to the customer, etc. Hopefully it would be calculated from a transactions table.

 

[Golom]

This is a decidedly non-trivial problem.

There are multiple activities that can change stock levels.

Selling, receiving from an order, stock takes, disposing (other than selling), transfering to other locations, etc.

Each such activity needs to be recorded in a transaction table that holds the stock code, the transaction DateTime and the change in quantity (negative for a decrease in stock level).

I cheat somewhat and also record the quantity in stock after the transaction. Strictly this should be computed but the system impact for computing in-stock values for 20,000+ stock items, each of which may have hundreds or even thousands of transactions is unacceptable for most system users.

Just as an example ... here's one of the SQL statement I use to retrieve adjustments for a stock item.

SQL = "(SELECT H.TakeDateEnd As [Adjusted On], (L.[Count]-L.[InStock]) As [Chg Qty], L.[Count] As [In Stock], IIF(IsNull(H.StockTakeType),'ST',H.StockTakeType) as Reason " & _
      "FROM StockTakeHead H INNER JOIN StockTakeLine L  " & _
      "     ON H.StockTakeNumber = L.StockTakeNumber " & _
      "WHERE L.StockCode = '" & mvarStockCode & "' AND H.TakeDateEnd IS NOT NULL ) "

SQL = SQL & _
      " UNION ALL " & _
      "(SELECT H.ReceiptDateEnd As [Adjusted On], (L.[QuantityReceived]) As [Chg Qty], L.[InStock] As [In Stock], 'RC' as Reason " & _
      "FROM RecHead H INNER JOIN RecLine L  " & _
      "     ON H.ReceiptNumber = L.ReceiptNumber " & _
      "WHERE L.StockCode = '" & mvarStockCode & "' ) "

SQL = SQL & _
      " UNION ALL " & _
      "(SELECT H.TransferDate As [Adjusted On], (L.[Quantity]) As [Chg Qty], L.[InStock] As [In Stock], 'TR' as Reason " & _
      "FROM StockTransferHead H INNER JOIN StockTransferLines L  " & _
      "     ON H.TransferNumber = L.TransferNumber " & _
      "WHERE L.StockCode = '" & mvarStockCode & "' ) "

SQL = SQL & _
      " UNION ALL " & _
      "(Select H.SD ,(-1 *  L.Quantity), L.InStock ,iif(L.Quantity>0, 'SL','RT') " & _
      "From (SALEHEAD H INNER JOIN SALELINE L ON H.SALE_NO = L.SALE_NO) " & _
      "      INNER JOIN StockMst M ON L.StockCode = M.StockCode " & _
      "Where L.StockCode = '" & mvarStockCode & "' AND H.T_Type IN ('SALE','REFUND','EXCHANGE')  AND M.Stock_Type IN ('V','N') ) "

SQL = SQL & _
      " UNION ALL " & _
      "(Select H.SD ,(-1 *  L.Quantity), L.InStock, 'WO' " & _
      "From (SALEHEAD H INNER JOIN SALELINE L ON H.SALE_NO = L.SALE_NO) " & _
      "INNER JOIN StockMst M ON L.StockCode = M.StockCode " & _
      "Where L.StockCode = '" & mvarStockCode & "' AND H.T_Type IN ('MARKDOWN') ) "

SQL = SQL & _
      " UNION ALL " & _
      "(Select H.SD, (-1 * K.Quantity * L.Quantity), K.InStock, 'SK' " & _
      "From (SaleHead H INNER JOIN SaleLine L ON H.Sale_No = L.Sale_NO) " & _
      "INNER JOIN SaleKit K ON K.Sale_No = H.Sale_No AND K.Line_No = L.Line_Num " & _
      "WHERE Kit_Item = '" & mvarStockCode & "' ) " & _
      "ORDER BY 1, 3 "

 

[jsteph]

This is a decidedly non-trivial problem

So true. There is so much that can be done here, depending on what you need. But the bottom line is that transactions tables are a necessity.

And don't scrimp on what data you're saving. You want to 'De-Normalize' here--forget much of what you learned about normalizing for this part of the system.

Avoid at all costs trying to do 'point-in-time' record in the live tables. For instance--it's very bad design (in my opinion) to have a "live" products table with the format:

ProductID    ProdDescr    Price    BeginDate    EndDate

This type of table should be a transaction table--not live. Try writing sql to get the current product/price with that mess--especially if the system has that same mess in other live tables, such as the Customer table. I've seen this in very expensive systems and it is not pretty--especially since this is often used for 'future dating', you can't just depend on a blank enddate. You've got to do bizarre greater/less than date criteria on multiple fields on the joined tables, and when looking at a range--it's brutal.

Disk space is cheap. Use the separate transaction tables for this purpose--every time the price changes you could make a table entry for that, but even then you'd still want to store the price with the inventory transaction or order line-item transaction.
--Jim

 

[MichaelRed]

then, again, realize that these 'suggestions' amount to having the complete history of all transactions for all 'products' embedded in your data base. particularly with the larger inventory systems, this will -depending on the transaction rate- generate large (perhaps HUGE?) recordsets and cause dramatic decreases in overall performance.

many of these systems are refered to as "perpetual inventory" -and the term can be viewed as having a vengence!

If you decide to go in this direction, it is a good idea to include a 'transaction reason' in the trnsaction table, as this permits the tracing of the products moving in and out of the system.

In hte ones I use, I include a "job" identity in hte transaction reason, and include the purchase order and serial number of products, along with the various 'stations' of the material (Ordered. Received, Assigned (to a Job), and Off-Site (shipped out). Done carefully, simple aggregate queries can return the history for individual items, the aggregate history of the part number, the history of the job with regard to the products, etc.




MichaelRed

 

[jsteph]

depending on the transaction rate- generate large (perhaps HUGE?) recordsets and cause dramatic decreases in overall performance

I'm forgetting that this is an Access forum...and if it's the JET db engine then yes, this could affect performance, espescially on each insert if many indexes exist. Also the tables would probably need to be housed in a separate .mdb file--even separate from the 'main' backend .mdb.

The basic concepts, however, are generally sound, and if/when migrated to something like sql-server or Oracle, then the performance aspects of the system in general should not suffer by adopting this type of transactional inventory.
--Jim

 

[MichaelRed]

" ...and if it's the JET db engine then yes, this could affect performance, ... "

from my experience, the dbengine does have a bearing on the issue, but primarily, this impact is to accelerate or decelleraqte the speed issue becomming noticable, not wheather it occurs. In at least one instance, the 'perprtual inventory' process was abandonded - at least for on-line processing.

Of course this app was processing >> 100K transactions per day, so it is perhaps not to suprising?




MichaelRed