I stumbled into a bit of a debate about this where several people were getting a bit heated about where the line is drawn in terms of when and what you denormalize for your systems.
Traditionally, when people talk about undoing all of that normalization work you’ve done, they answer with "it depends." It depends on the types of reporting, the types of data, the traffic, etc. But not so for all of the DBAs I spoke with.
Generically, it seems like many DBAs draw the line at reporting as a first candidate for denormalization. In fact, I talked with a few people that forbid reporting at all against the OLTP database, and only allow it against an almost completely denormalized data warehouse built specifically for supporting reporting. They essentially have thrown up their hands trying to get a single source tuned for both sides (transactions, reporting) of the performance equation.
Where do you draw the line? Perhaps more importantly, is it the same for all types of applications that you support?
I think there are a few different cut points –
– denormalization
– indexing
– building physically separate databases – reporting and transactions
It can add some significant complexity to your application development work – but the performance gains when things get dialed in correctly with the right combination of options, can be substantial.
So, what do you think? Where do you draw the line(s)?