At 06:27 PM 1/18/2006 +0000, Marco Neves wrote:
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
Hi,
The problem I run into then is that it's not just the inventory that
changes. Each product has it's own set of custom fields that change with
time. With this scenario, the stock movements table would require columns
for each value that can change. Or I could create a table for each field
that changes with time but that could get unwieldily very quickly.
Ian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
