Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Zim327
Yak Posting Veteran
62 Posts |
 Posted - 2009-10-06 : 10:31:46
|
| a few years ago I used a proprietary DB that had a massive audit table (millions of records) and I was always puzzled by the fact that there were duplicate columns in the audit table and the records table. specifically, [created by], [creation date], [modified by] and [modified date] were in both tables. I'm guessing they did that for a reason. Either that or it was just "legacy code" that they kept using.So basically, was this denormalization a good idea or is this simply poor design? Does denormalization really lead to performance gains? Is denormalization EVER a good idea?I've done some investigation on this issue and I can't seem to find a clear answer. Can someone set the record straight?I'm asking because I'm currently designing my own audit tables and I'm tempted to copy the schema above.(Full disclosure: I always try to normalize a db as much as I can.)Thanks, |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-10-06 : 10:40:44
|
It depends.For example in a datawarehouse denormalization is often THE WAY because it is speeding up some queries with no need to join a lot of other tables.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Zim327
Yak Posting Veteran
62 Posts |
Posted - 2009-10-06 : 11:38:46
|
| Thanks for the replies.Brett: Can you provide a link to this particular best practice? I would like to read up on it. I confess I've never heard of this...(I'm confused because this looks an awful lot like denormalization and/or duplication of effort)And does it matter how these 2 tables get updated? For example, can I put a trigger on the record table to put a record into the audit table on insert, update? Or should it be the other way around? Thanks, |
|
|
|
|
|
|
denormalize for performance?