De Normalisation (or De Normalization) of data allows table to store redundant data. De-Normalisation is exactly opposite to the Normalization of database. Normalization is the technique to remove redundancy from the database.

De-Normalisation applications:

Normalisation techniques are followed in all of the Online Transaction Processing systems, where bulk
DML commands are executed on the database. If we store redundant information in a single table, it would affect the data retrieval performance in the OLTP system. Hence data are normalized and master – detail relationships are created between the tables in the OLTP applications. Even though the OLTP database is highly tuned for better performance, due to the increasing number of joins and relations between the tables, users would experience a performance bottle neck. In such scenarios, DBA would opt for de-normalisation of specific tables to tune the performance of the database.

De-Normalization would be followed in designing Online Analytical Processing (OLAP) applications. The tables would be designed to allow redundant information storage of the same subject on which the table data is about. The redundant information storage in OLAP database would not affect the performance of the queries because of the reason that the OLAP applications are widely used for analytical purposes unlike the OLTP applications. Only ‘select’ queries hit the OLAP database most of the time.

While designing a Data warehouse, dimension modeling applies de normalization of tables.

De normalisation avoids certain situations that cause poor database performance like
• More Joins for Lookups – Ex: When we want to check the parent entity of each record from the hierarchy of tables, it creates more join statements to perform such lookups. It would be better to keep a key for parent entity or the parent entity name in each record of the table which needs such lookup.
• More Joins for Aggregations – Ex: Scanning through the transactional table each time using multiple join statement to show a year to date financial sum of each of the general ledger accounts. Instead we could add a redundant column of year to date sum of value for each general ledger account in a summary table.
• More Correlated sub queries - Storing start and end date of each time bound record in the same table to avoid correlated nested sub queries to find the duration is another form of de normalization that creates huge performance improvement.

De normalisation is applied in a OLTP application only when there exists tons of relations between the tables and it becomes difficult to manage the performance of the system.

Related posts:

1. OLAP vs OLTP If you are into databases and middleware and ever wodners...
2. Database Normalization Normalization is the process of creating and organizing data in...
3. Relational Database Relational Database, the data or information are gathered and arranged...
4. Data Dictionary Data Dictionary is an organized file of details about the...
5. Dimensional Modeling Dimensional Modeling is the data model design technique for data...