Thanks Ed. That's another good idea. The consensus I'm getting is to
create one table that stores unchanging data about the product and another
that stores transaction details. The
problem I'm still having is how to efficiently handle more than one
changing value.
As an example, let's say I want to keep track of not only the quantity of a
product but who the sales rep for that product is. While the quantity
would change much more frequently than the sales rep I could put both in
the same transaction table, but then I'll end up with duplicated data. For
example,
date | product_id | quantity | rep
2006-01-01 | 1 | 100 | rep 1
2006-02-01 | 1 | 98 | rep 1
2006-03-01 | 1 | 98 | rep 2
2006-04-01 | 1 | 50 | rep 2
Alternatively, I could create one table for the quantity and another for
the sales rep.
date | product_id | quantity
2006-01-01 | 1 | 100
2006-02-01 | 1 | 98
2006-04-01 | 1 | 50
date | product_id | rep
2006-01-01 | 1 | rep 1
2006-03-01 | 1 | rep 2
This seems to be the cleanest solution, other than requiring a table for
every field that I want to track.
Ian
At 02:36 PM 1/18/2006 -0800, Ed Reed wrote:
I built my inventory system like this,
I have a products table that contains all the information specific to each
part, less the quantity, i.e. Part Number, Description, Vendor, Color,
Weight, SKU number, etc...
Then I have another table that is my Inventory Tranactions Log that is
just the following
Date, ProductID, Qty, TypeOfTranacstion, Comment
The inventory for each part may adjust daily or not. When parts are
removed/sold the transaction log gets a record for that product and the
number of parts that were sold and the type of transaction that occurred.
When parts are received another transaction is entered for that part with
the quantity received and the type of transaction that occurred. When we
close the store and want to take a full inventory we first run a report
that get the sums of all the transactions for each product and that tells
us what should be on the shelf according to the database. Then we verify
or adjust the qty for each product on the shelf by adding a record to the
transaction log indicating the quantity and the type of transaction that
occurred.
When we want to see the values in the inventory its a very simple report
to get the sums for each product.
- Hope that helps.
>>> Ian Klassen <[EMAIL PROTECTED]> 1/18/06 10:09:55 AM >>>
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
