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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
