Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
StackExchange 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. –  Richard Aug 14 '11 at 19:17
1  
possible duplicate of How far should you go with normalization? –  Jack Douglas Aug 14 '11 at 20:53
    
Possible duplicate/related information When to Denormalize Database Design –  John Sansom Aug 15 '11 at 7:37

5 Answers 5

up vote 17 down vote accepted

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.

share|improve this answer
1  
I denormalize when I am creating reports or analysis and I want speedy results. All the indexes in the world with multiple joins are never so fast as a denormalized table that represents cached data that is not going to change. –  kevinsky Aug 14 '11 at 21:41

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.

share|improve this answer
    
"I routinely denormalize so that I can enforce data integrity with constraints." Same here. It's a great trade-off: You denormalize a little bit but gain DRI. –  Nick Chammas Mar 29 '12 at 15:54
    
@NickChammas - this is very interesting. Can you share scenarios when you do such things? –  A-K Mar 29 '12 at 19:22
    
Certainly. We have a Fulfillment system that includes a queue of items to be fulfilled. There is a Fulfillable table with all the details on each Fulfillable item, and then there is a FulfillableQueue table that implements the queue in SQL Server. Only Fulfillables with a certain StateID may be in the queue. StateID is in the Fulfillable table, but I replicate it to FulfillableQueue and then enforce this constraint with FOREIGN KEY and CHECK constraints. –  Nick Chammas Mar 29 '12 at 20:20

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

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.