OK, you've re-discovered another rule of thumb:
If the queries are unpredictable you can't keep them
precomputed. So if you want to answer ad hoc queries
like "How many Brown, men's Trecks sold in the last two
weeks" and then ask "how many whites in 10 1/2"
Your way might work.
The more traditional method would be to model the physical
objects. and track how many white size 8s you have then
use something like sum(QuantityOnHand) ... where
size in (10, 10.5, 11)...;
Or sum(QuantityOnHand) ... where maker like 'Shoeworld' ... ;
--- Rob Brown-Bayliss <rob@zoism.org> wrote:
> On Tue, 2002-07-30 at 13:42, Chris Albertson wrote:
>
> > 2) The clasic design for an inventory system keeps a count
> > of the widgets of each type on hand so you don't actually need
> > to compute (count(widgets_bought)-count(widgets_sold)) to
> > know how many are left. I think it is OK to keep some infomation
> > pre-computed if it is needed frequently. Recovering the count
> > from a datestamped transaction log seems like a lot of work.
>
> The reason I thought this might be the way top go is the complaint
> from
> the customer about every other system he has looked at.
>
> The business is shoe retail, so he buys a shoe, say it's called
> "Trek"
> made by "Joes Shoe makers". When He buys 400 of these, they might be
> 200 white, 100 black, 100 red, but also spread accross 10 different
> sizes.
>
> On other systems he has had to have aproduct code for each possible
> combo, one for wihte size 35, one for white size 35.5 etc.
>
> Then he cant get a count of how many "treks" he has in stock. I
> fuggured this way I can give a simple "389 total treks in stock"
> answer,
> or a "137 white treks" or a "24 white size 35 treks".
>
> As you say it's quite fast, I have entered 25,000 random transactions
> and getting a query run in 0.21 seconds (from a python interface).
>
> It seems like a good plan, but I am not experienced in these
> things...
>
>
> --
>
> *
> * Rob Brown-Bayliss
> *
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
=====
Chris Albertson
Home: 310-376-1029 chrisalbertson90278@yahoo.com
Cell: 310-990-7550
Office: 310-336-5189 Christopher.J.Albertson@aero.org
__________________________________________________
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com