I think we are all familiar with database normalization. My question is: What are some guidelines that you use when you want to denormalize the tables?
asked Aug 14, 2011 at 19:16StackExchange sites have a unique advantage over other sites on the internet in that 1) they allow for the best answers to become the most easily found and 2) the best answers are determined by the community. Because of that, I believe that this site and the internet would benefit from this question, despite that it kind of goes against the faq.
Commented Aug 14, 2011 at 19:17 possible duplicate of How far should you go with normalization? Commented Aug 14, 2011 at 20:53 Possible duplicate/related information When to Denormalize Database Design Commented Aug 15, 2011 at 7:37Denormalize when it's OLAP operations, normalize when OLTP (from linked article under section Denormalization)
Databases intended for online transaction processing (OLTP) are typically more normalized than databases intended for online analytical processing (OLAP). OLTP applications are characterized by a high volume of small transactions such as updating a sales record at a supermarket checkout counter. The expectation is that each transaction will leave the database in a consistent state. By contrast, databases intended for OLAP operations are primarily "read mostly" databases. OLAP applications tend to extract historical data that has accumulated over a long period of time. For such databases, redundant or "denormalized" data may facilitate business intelligence applications. Specifically, dimensional tables in a star schema often contain denormalized data. The denormalized or redundant data must be carefully controlled during extract, transform, load (ETL) processing, and users should not be permitted to see the data until it is in a consistent state. The normalized alternative to the star schema is the snowflake schema. In many cases, the need for denormalization has waned as computers and RDBMS software have become more powerful, but since data volumes have generally increased along with hardware and software performance, OLAP databases often still use denormalized schemas.
Denormalization is also used to improve performance on smaller computers as in computerized cash-registers and mobile devices, since these may use the data for look-up only (e.g. price lookups). Denormalization may also be used when no RDBMS exists for a platform (such as Palm), or no changes are to be made to the data and a swift response is crucial.