0
Denormalization is the process of taking different data points from many different tables and combining it to larger, single table(s). Example:
Table: CustomerContact
Column: ContactID
Column: FirstName
Column: LastName
Table: CustomerAddresses
Column: AddressID
Column: House Num
Column: Street
Column: Suffix
Column: Prefix
Column: FK_ContactID
... could be combined into:
Table: Customers
Column: CustomerID
Column: FirstName
Column: LastName
Column: House Num
Column: Street
Column: Suffix
Column: Prefix
This is often done to increase the read efficiency that is sometimes lacking in a relational database. It is most common in reporting data warehouse environments where writes, deletes, updates and deadlocks aren't usually an issue but it is important to have reports which run quickly with less expensive join operations. The drawbacks to doing this are the inherent data redundancies. In the example above, customers with more than one address would have their name repeated in the Customers table once for every address. Where if it was normalized, their name would only appear once in the CustomerContact table and they would have multiple CustomerAddress records. On large scales, this kind of architecture can have large disk space impacts. Also, let's say in the example above that the customer's name was to change. In the denormalized version, this would involve changing their name on many Customer records instead of just changing the CustomerContact record once.
Note: This is a very simple explanation. Much more detail is needed to fully understand the pros and cons to normalization vs. denormalization and the reasons for adopting either architecture. It would be recommended to fully understand the following concepts first:
1. Relational Database Design
2. Foreign Keys, Primary Keys, Uniqueness
3. Normalization, Normal Forms (First Normal, Second Normal, Third Normal etc.)
4. Summing, Grouping, Aggregation
Wiki User
∙ 9y ago
Add your answer:
Earn +20 pts
What is denormalisation?
The process of taking a normalised schema and making it nonnormalised is called denormalisation
What is the difference between normalisation and denormalisation?
Hi, Normalisation is used to reduce the redundancy of database. So, we divide the the data into smaller tables. These tables are related to each other through a relationship. Denormalisation is the reverse process of normalisation. In this we add redundancy of data and grouping of data to optimize the performance.
What is de-normalisation in dbms?
Hi, Denormalisation is the process to read the performance by adding redundancy of data or by grouping of data.
Techniques of denormalization and its advantages and disadvanteges?
When you design a database, you first want to normalize it. Main purpose is to avoid data duplication, because duplicate data takes up unnecessary space and is harder to maintain. Suppose you want to store information about your customers. You want to store their address to send them promotional material. You also want to store what products they bought so far. If you'd put that in one table, you'd be repeating the customer's address for each article they bought. When one of them changes address, you need to remember to change all the records to update the address to avoid data inconsistency. So you normalize this bit, and create a table with e.g. customer number + customer name + customer street + customer zip code/postal code, a second table with zip code + city, a third table with customer number + product number, a fourth table with product number + product description + vendor number, etc. Now look at the I/O involved in getting at that data. When you put all the data in one table, accessing all the data will normally involve fewer I/O transactions and therefore be faster than accessing the data spread over multiple tables, which requires jumping back and forth from indexes to data records, as it . And despite the fact that I/O performance has improved tremendously since early days, it still is the slowest component in a computer. Computers with slow I/O subsystems may also benefit from denormalisation. Denormalisation basically is the process of finding the balance between avoiding data duplication and ensuring database performance.
What is denormalization?
Denormalization is a database optimization technique that involves intentionally adding redundant data to a database schema to improve data retrieval performance. By duplicating data across tables, denormalization reduces the need for complex joins and improves query performance. However, it can lead to data redundancy and potential data integrity issues if not managed properly.
What are disadvantages of database approach?
The advantages of a relational database are that they contribute to sound logical design, are easy to understand and program, and should be amenable to change as business requirements change. The main earlier systems include file-based systems and Codasyl databases. File-based systems were complex and tended to be inherently restrictive and quickly reached the point where a complete rewrite became necessary if business requirements changed. Codasyl databases were a vast improvement on the old file-based systems, but required a high level of expertise to design properly and, once again, tended to be unable to support changes to business requirements. Because a properly designed relational database reflects business entities and the relationships among those entities, they should be more logical and easier to understand. If the business structure or requirements change in any logical way, the database can be changed in a parallel way, to support those requirements. Relational databases are designed at two levels. First, there is a logical design, normalised at third normal form - where there should not be duplicate data values. Then, there is a physical design, where any necessary denormalisation is carried out.
