Ian Klassen <[EMAIL PROTECTED]> wrote on 01/18/2006 01:09:55 PM:

> Hi all,
> 
> I'm trying to figure out a solution to the following problem.
> 
> Let's say I have a store with various products.  I take inventory of 
these 
> products on different days.  At any given time I want to view what the 
> inventory is for the entire store.  I also want to know whether the 
> inventory in the result was taken on that day or was carried forward 
from a 
> previous date.  I may also have to make changes to the inventories 
> previously recorded.  I have a few solutions, none of which I'm really 
> happy with.
> 
> The first is to create rows that contain the inventory for each product 
on 
> a given day.  If no inventory was taken for a given product then leave 
the 
> column null.
> 
> date_of_inventory   | product a   | product b   | product c
> 2006-01-02      | 100      | 50      | 25
> 2006-01-03      | NULL      | 45      | NULL
> 2006-01-05      | 78      | NULL      | 22
> 
> To obtain the inventory on any given day I would have to query each 
product 
> and find the most recent time that it was updated.  With this solution 
> there is a lot of wasted space caused by the NULL's.
> 
> Another solution would be to have a start and end date for when the 
> inventory is valid such as:
> 
> start_date   | end_date   | product a   | pa_up      | product b   |
> pb_up      | product c 
> | pc_up
> 2006-01-02   | 2006-01-03   | 100      | TRUE      | 50      | TRUE 
> | 25           | TRUE
> 2006-01-03   | 2006-01-05   | 100      | FALSE   | 45      | TRUE 
> | 25      | FALSE
> 2006-01-05   | 2006-01-05   | 78      | TRUE      | 45      | FALSE 
> | 22      | TRUE
> 
> With this solution I can quickly retrieve the inventory on any given day 

> and see what inventory was taken on that day (which product update 
columns 
> are set to TRUE).  However, I see the update side of this as a nightmare 

> (especially considering I'm duplicating data).
> 
> A third solution could be breaking each product into its own table. This 

> would eliminate the issues with the first two solutions but I would end 
up 
> with hundreds of tables which I would like to avoid.
> 
> Any help on the direction that I should go would be greatly appreciated.
> 
> Ian
> 
> 
Something you didn't think of:

CREATE TABLE physical_inventory (
  date_of_inventory datetime, 
  product_id int unsigned,
  quantity int,
  PRIMARY KEY(product_id, date_of_inventory)
)

Then determinining the current inventory is a simple two-step process 
(also known as finding the groupwise maximum):

A) find the latest date_of_inventory for each product:

CREATE TABLE tmpInv(KEY(product_id, date_of_inventory)) SELECT
  product_id,
  max(date_of_inventory) date_of_inventory
FROM inventory
GROUP BY product_id;

B) rejoin to your original table to get the quantity

SELECT ti.product_id, ti.date_of_inventory, i.quantity
FROM tmpInv ti
INNER JOIN inventory i
  ON ti.product_ID = i.product_id
  AND ti.date_of_inventory = i.date_of_inventory;

DROP TABLE tmpInv;

With this design, you won't have an inventory table of several hundred 
columns and you won't need to change your database design every time a 
product is added or removed from inventory.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Reply via email to