Hi,
Why don't you create two table:
* a product table, with the product discriptions, and other product
related
info (call it prod):
|ID|NAME|SOME|OTHER|FIELDS|
|1|ProdA|..|..|..|
|2|ProdB|..|..|..|
* a stock movements table, with moviments by product (call it pro_move):
|ID|PROD__ID|DAY|MOV|DESCRIPT|
|1|1|2006-01-01|10|Inventory at Jan 1st for Prod A|
|2|2|2006-01-01|25|Inventory at Jan 1st for Prod B|
|3|1|2006-01-02|-5|Selled 5 units of A at Jan 2nd|
Then to know the inventary to up-to-date of every product you can do:
SELECT p.id,p.name,sum(pm.mov),max(day)
FROM prod p LEFT JOIN prod_move pm
ON p.id=pm.prod__id
GROUP by p.id;
If you think your product or move table will grow too big you can add a
stock
column to the prod table and update that field when you add a movement to
your prod_move table, and verify that value from time to time (and if
possible just add movement in transaction, with both tables suporting them -
InnoDB ou DBD).
This is the way I would do it.
What you think?
mpneves
On Wednesday 18 January 2006 18:09, Ian Klassen wrote:
> 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
--
AvidMind, Consultadoria Informática, Unipessoal, Lda.
Especialistas em OpenSource
http://www.avidmind.net
OBC2BIP
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
