Hello All:
I am looking for suggestions on how to optimize a relatively simple problem.
Say I have a group of shoe stores. The store report sales daily and daily I
calc stats off on the sales numbers. Ie rolling averages & volatility
numbers, with a couple of different look backs.
I am storing the raw sales figures in one table and the calc'ed stats in another.
Here is the interesting part, it is possible and it does happen that a store will not
report sales for a day or two or prior figures reported will get revised.
There is at max a five day window on this.
For the prior dates there are two possibilities, I will be updating
a record or inserting a new record in sales table. For the current date's sales it will be all inserts.
Currently what I do is loop through the data by store and shoe style attempt an update and if that fails
do an insert.
Same deal for the stat's table except there are multiple records for each Store/Shoe style pair.
So here is my question/issue: Is this there a better design pattern for doing this?
I could gather up the data for the prior dates and do one "bulk" update. But that would
fail for sales that have not been reported todate.
The current date's sales are of course an insert. And I could fix this to be one "bulk" insert.
Was just chewing this over in my heading wondering if there is a more efficient way to this.
Other than attempt update if fail insert. Seems to be a bit of churn to me.
But at the end of the day, it do believe Keep It Simple.
Many thanks for considering this problem.
KD