When should you denormalize?

Question

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?

Answer 1

Denormalize 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.

Answer 2

One potentially sensible reason to apply controlled denormalization is if it enables you to apply some integrity constraint to the data that wouldn't otherwise be possible. Most SQL DBMSs have extremely limited support for multi-table constraints. In SQL sometimes the only effective way to implement certain constraints is to ensure that the attributes involved in the constraint are all present in the same table - even when normalization would dictate that they belong in separate tables.

Controlled denormalization means that mechanisms are implemented to ensure that inconsistences can't arise due to redundant data. The cost of these extra controls and the risk of inconsistent data need to be considered when deciding whether denormalization is worthwhile.

Another common reason for denormalization is to permit some change in storage structures or allow some other physical optimization that the DBMS wouldn't otherwise permit. According to the principle of Physical Data Independence a DBMS ought to have the means to configure internal storage structures without needlessly altering the logical representation of data in the database. Unfortunately many DBMSs are very restrictive of the physical implementation options available for any given database schema. They tend to compromise physical database independence by only supporting a sub-optimal implementation of the desired logical model.

It ought to be obvious but it still needs to be said: in all cases it is only changes in physical implementation features that can dictate performance - features such as internal data structures, files, indexing, hardware and so forth. Normalization and denormalization have nothing to do with performance or storage optimization.

Answer 3

Normalize until it hurts, denormalize until it works (i.e.: performance becomes acceptable) :)

Answer 4

Denormalize if you are frequently accessing computed data, as is suggested in the answers to this question. The cost of storing and maintaining the computed data will often be less than the cost of re-computing it over and over if your load profile is read-heavy.

Answer 5

I routinely denormalize so that I can enforce data integrity with constraints. One example is a recent question on this site - I replicate a column in another table, so that I can use a CHECK constraint to compare it to another column. Another example of this technique is my blog post.

You cannot use CHECK constraints to compare columns in different rows or in different tables, unless you wrap such functionality in scalar UDFs invoked form a CHECK constraint. What if you actually need to compare columns in different rows or in different tables to enforce a business rule? For example, suppose that you know working hours of a doctor, and you want to make sure that all appointments fit within working hours? Of course, you can use a trigger or a stored procedure to implement this business rule, but neither a trigger nor a stored procedure can give you a 100% guarantee that all your data is clean – someone can disable or drop your trigger, enter some dirty data, and re-enable or recreate your trigger. Also someone can directly modify your table bypassing stored procedures. Either way you can end up with data violating your business rule without knowing about it.

Let me demonstrate how to implement this business rule using only FK and CHECK constraints – that will guarantee that all the data satisfies the business rule as long as all the constraints are trusted.

Yet another example is a way to enforce that periods of time have no gaps and no overlaps.