We are planning to do an e-commerce shopping site with PHP-MYSQL combination.
I read in almost every link related to Normalization & Denormalization : Normalize until it hurts, denormalize until it works.
One thing I understood is Normalization is must for good performance , am i right ?
We have to do Denormalization only when we face problems of Performance [ the very low page speed ] , otherwise Denormalization is not required.
Is for 100% it will hurt – Normalization? can we do anything to prevent [not to hurt.] can we take some steps for performance boost other than Denormalizing & Refactoring complete database again.
Can we do Denormalization without doing Normalization before ?
Best Answer
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 "Indexes, query tuning, hardware upgrades, server configuration and etc"....