How to prevent Performance issue & going for Denormalization
credits : Vladimir Oselsky
Normalization is never for a performance of reads it is for data integrity and performance of writes. You normalize to avoid redundant data and creating problems by having same data exists in different locations. If you want most amazing performance, you denormalize by throwing everything into a single table and create a lot of indexes. That is why for reporting (DW) it is a practice to denormalize to increase the performance of reads. Save yourself a lot of headache and do a lot of reading on how other people solve same problem.
I strongly believe in 3NF, but going beyond that is absurd in my book, I have yet to see a valid business case for doing anything above that. Sooner or later you will find a need to denormalize for a specific process, usually it is done for reporting, data warehouse need. A good rule is OLTP = Normalize, OLAP = Denormalize.
It is a nature of database design and request from the business. Whenever you are designing a system, you are designing for a specific purpose, and later someone else comes along and request information that you might not have been prepared to give, in that case, you are required to denormalize to provide a best performance.
To increase the performance go for &quot;Indexes, query tuning, hardware upgrades, server configuration and etc&quot;....