March 22, 2006 at 3:25 am
Hi All,
Bit of a basic question I suspect but what I want to do is roughly this: -
Record layout.
Key1, Key2, Key3, Val1, Val2, Val3
Lets assume we have contents of
Record1 A, A, A, 1, 2, 3
Record2 A, A, B, 4, 5, 6
Record3 A, B, B, 7, 8, 9
Summary records at each of the levels must be updated automatically
A, A, Z must store 5, 7, 9 (sum of records 1 and 2)
A, B, Z must store 7, 8, 9 (sum of record 3)
A, Z, Z must store 12, 15, 18 (sum of records 1, 2 and 3)
Any changes to the records needs to be 'rippled' through the summary records. Are triggers the best approach for this?
Any pointers / advice would be most appreciated.
Cheers . . . . .Richard
March 22, 2006 at 3:44 am
Triggers would work, but they'd be very resource intensive.
Why do you need to store summary information when it can be recalculated easily? If it's for a report than it can be calculated on the fly when the report is generated.
It violates one of the rules of data normalisation - never store redundant data.
How often do you expect the base records to be updated/inserted? What happens if someone updates one of the summary records?
I would recomend at least seperating the summary information into another table, so that you don't get a recursive firing of triggers when you update one row.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2006 at 3:59 am
Thanks for the quick reply.
We control the updates to the DB so only records at the 'lowest'level can be updated. We do, however, extract summary level data (display only) quite a lot and the overhead of doing calculations ALL the time will be too much I suspect. We are delivering the content via web pages so speed is important.
Good point about separating the data into other tables. I will give it some thought.
We do currently keep the records updated using multiple SQL statements and I was looking for a quicker way / less program intensive way.
March 22, 2006 at 10:42 pm
What I would recomend, if you're using stored procdures (and if you're not, why not?) is to do the summary calc in the sp before or after inserting the detail then put the summary into one or more rolled-up tables in a ready-to-present form.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply