Minty
AWF VIP
- Local time
- Today, 23:05
- Joined
- Jul 26, 2013
- Messages
- 10,371
I have a basic stock system that has calculated balances based on transactions , and for some time I've been unhappy that I was creating and maintaining a Locations Table containing fields;
PartID (PK)/(FK) , Location(PK), Qty and StockCheckDate
to accommodate multiple possible locations for the same part.
In a moment of tidying up/improving things, I was going to remove this and simply calculate the current location balances, as I do the overall Part Qty values from my PartsMovement Table.
The problem is - how do I store the stock check date? If I put it against the top level Parts record I can't guarantee that all the locations where stock is held are checked, as different locations for the same part may be checked on different days? Is this one of those occasions where NOT storing the calculation actually works against me ?
I don't have a table listing just the locations, as it has never seemed necessary. Stock locations are created and removed on the hoof as it were, and can always be looked up from the current stock transaction records.
Any thoughts - am I missing something obvious. (I have Man-flu so it's quite possible I'm being very thick... )
PartID (PK)/(FK) , Location(PK), Qty and StockCheckDate
to accommodate multiple possible locations for the same part.
In a moment of tidying up/improving things, I was going to remove this and simply calculate the current location balances, as I do the overall Part Qty values from my PartsMovement Table.
The problem is - how do I store the stock check date? If I put it against the top level Parts record I can't guarantee that all the locations where stock is held are checked, as different locations for the same part may be checked on different days? Is this one of those occasions where NOT storing the calculation actually works against me ?
I don't have a table listing just the locations, as it has never seemed necessary. Stock locations are created and removed on the hoof as it were, and can always be looked up from the current stock transaction records.
Any thoughts - am I missing something obvious. (I have Man-flu so it's quite possible I'm being very thick... )