Just a few comments
1) For your purposes a million or so rows is not "a lot" and
queries will still be fast.
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.
--- Rob Brown-Bayliss <rob@zoism.org> wrote:
>
> I am writing a stock system, and am looking for either a confirmation
> of
> my plan or a "No, don't do that!" before I get in too deep..
>
> I have a table, called stock_transactions.
>
> It has primary key and timestamp set on insert. It has these columns:
>
> Count, product_key, branch_key, size_key, colour_key and type_key.
>
> The keys obviously are used to link to other tables, for example the
> product table has a description, the type key is a transaction type
> etc.
>
> The idea is that when I want toknow how many items at a branch I
> select
> all rows matching product_key and branch_key then SUM(count) the
> count
> column.
>
> The problem as I see it is after a length of time the table will
> reach a
> large size and then getting counts of stock on hand will become quite
> slow, so I plan to have a stock take date in the system, and then
> limit
> it to all rows after the last stocktake.
>
> This is guess will require an index on teh timestamp column.
>
> Does any one see a problem here or does it all sound ok?
>
> Thanks
>
=====
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