databasedev.co.uk - database solutions and downloads for microsoft access

Denormalisation in Relational Database Design

The majority of the database community would agree that designing a database to 3NF is usually sufficient. Nevertheless, if the results of 3NF is the total, or almost total, elimination of data redundancy, it can also lead to poor performance.

Consider the relational model shown below:

Now, if you wish to show a calculation of the Total Turnover for a given Customer, the query that you will use needs to join all three tables, calculate the amount of every order detail and total all of the amounts.

The above query would probably consume quite a lot of CPU time.

Now we could consider adding the field TotalAmount to the Orders table as shown below:

With the above model, when we now want to calculate the total turnover for a given Customer, you will only need to join two tables and calculate the sum. You could even add a TotalTurnover field in the Customers table, if you needed frequent access to this specific information.

Whilst denormalisation has advantages, it also has drawbacks, the worst being the maintenance of redundant data.

In the previous example, each time an order detail is inserted, the total amount of the order has to be calculated and updated in the order table, or in the customer table if you decided to store the information there.

Data integrity is also endangered because you have to guarantee that the redundant data is kept up to date. For example, you may decide that the update is performed every night by a batch process, recalculating every value, or that the value should be calculated on the fly and cross-checked every night to correct possible inaccuracies. On the other hand, if you have to update the customers table every time you insert a new order, you slow your insert query. This then begs the question "Is the redundancy worth it?"

When to Use Denormalization

When you design a database, you have to ensure that the database performs all the important functions correctly and quickly. If a database has a solid database design, other performance issues can be resolved after the database is in production. If the database schema is designed poorly, the related performance issues can only be addressed by changing the design of the database. Normalization often leads down the path of efficient database design, but there are tradeoffs to normalization. A database that is used primarily for decision support (as opposed to update-intensive transaction processing) may not have redundant updates and may be more understandable and efficient for queries if the design is not fully normalized.

As with most database configurations, normalization provides benefits; however, if it is taken to the extreme, it may restrict performance. As normalization increases, so does the number and complexity of joins required to retrieve data. If your database requires too many complex relational joins between too many tables, you can hinder performance.