Denormalization is the process of attempting to optimize the performance of a database system by adding redundant data.
Examples of denormalization that I've found in E2 include storing the number of cools that a writeup has received in the writeup table, when a simple SQL
select count on the coolwriteups table can retrieve that information. E2 also stores the number of writeups that a user has created in the user variables when you can do a
select count on node join writeup to calculate that.
Database designers often justify denormalization on performance issues, but they should note that logical denormalization can easily break the consistency of the database, one of the all-important ACID properties. However, a designer can achieve the performance benefits while retaining consistency by performing denormalization at a physical level: create an indexed view on the tables in which you are interested, and the DBMS will physically denormalize the data into the index for faster queries on the view, but every time a user
deletes something in the table, the DBMS will automatically update all views and indexes.
The biggest remaining causes of harmful denormalization nowadays:
- newbie DB designers
- MySQL, or any other DBMS that can't index a view