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
