Denormalized Database Inquiry

[estark]

Hello,

Does anyone know the advantages and disadvantages to denormalized databases? And perhaps, why a denormalized database would be chosen over a normalized database?

Thank you!

Elizabeth

[pkreemer]

This sounds like a great interview question. :-) So my simple practice has been (for general, mixed OLTP and reporting databases) that you start your design out pretty well normalized - 3rd normal form or close to it.

Then for purposes of reporting, or possibly high volume transactions, you can allow some denormalization to creep in. One common example would be for totals in an order, where the order consists of a master Order row and multiple LineItem rows. Each line item of an order has a quantity and unit price. To calculate the order totals you need to join with each LineItem row. In some cases this might be too slow, so you would add some TotalPrice and maybe TotalQuantity fields to the master Order row.

But then you'd take on the responsibility of keeping those total fields updated while editing LineItems. To keep everything database-centric, maybe you'd do that with a trigger on the LineItem table.

So, I have usually been able to avoid denormalization but I've often been lucky to work with lightly loaded hardware and/or light application loads.


Paul