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
--
*
* Rob Brown-Bayliss
*