Denormalization is the reverse process of data normalization, which is sometimes required in order to speed up your query responses. In the web world, it is necessary to have your application respond to customer queries quickly and effectively to gain more results.
Denormalization plays a crucial in terms of the overall performance of a website or a customer-fronting application. As we already discussed denormalization and when to use it in the previous article, let us further explore this article’s denormalization techniques.
Various Database denormalization techniques
Storing the derivable data
If you are trying to execute a particular calculation repeatedly during the query execution, it is ideal for storing its results to avoid multiple query runs. If the calculation consists of some detailed records, you must store the derived calculation in the master table.
Whenever you have to store the derivable values, ensure that the system recalculates the system’s denormalized values. Some situations where you need to store the derivable values are:
- When you need the derivable values frequently.
- When you do not have to alter the source values frequently.
Advantages vs. disadvantages of this approach
Advantages:
- There is no need to look up the source values when the need is for a derivable value.
- No need to re-perform a particular calculation for each query or report.
Disadvantages:
- Operating data manipulation language (DML) statements against source data may need recalculation of derivable data
- It is possible to have data inconsistencies due to the duplication of data.
As an example of this model of denormalization, you can consider making a mail messaging application. Once on receiving a message, a user only gets a pointer to this message, which is stores in the messages table. This will prevent the messaging system from the need to store multiple copies of the mail message in case it is sent to many recipients simultaneously.
However, what will happen when a user deleted the message from a different account? In such a case, only respective entries in the table get removed. So, to delete the message completely, all the user message records need to be removed.
Usage of pre-joined tables
To pre-join the database tables, you have to add a non-key column to the given table, which bears no business value. In this way, you can easily dodge the joined tables and further speed up the query performance.
However, you must also ensure that the denormalized columns are updated when the master column is changed. Pre-joined tables are a denormalization technique, which can be effectively used where the need is to make a lot of queries against many tables and also as long as the stable data in it is acceptable.
When you are doubtful about your database administration, best practices, feel free to approach RemoteDBA.com who is offering best-in-class database services.
Advantages vs. disadvantages of pre-joined tables
Advantages:
- There is no need for multiple joins.
- You can put off the updates anytime as long as the stable data is acceptable.
Disadvantages:
- DML may be required for updating non-denormalized columns
- An extra column may need additional disk space and more effort to work on it.
As an example of denormalization via pre-joined tables, you can assume that the email messaging service users need to get access to messages of various categories, and then just keeping category names in the table of User_messages will save a lot of time by reducing the number of joins needs.
We may introduce a column as category_name in the denormalized table, which can store info about the category each record belongs to. With denormalization, only a single query is needed on the User_messages table with which the users can select all messages belonging to that category.
However, the major downside of this approach, as we discussed above, is that the extra column may need a lot of additional storage space.
Using the hardcoded values
If you find a reference table featuring some constant records, you can further hardcode those into your application. In this way, you do not have to join the tables to get the reference values. But, while using the hardcoded value method, you must initially create a check constraint to validate the values against the reference values.
This constraint needs to be rewritten whenever a new value in the A table is needed. The hardcoded value data denormalization method has to be effectively used when the values are static through the system’s lifecycle and as long as the number of such values is fairly small.
Advantages vs. disadvantages of this approach
Advantage:
- There is no need to implement any lookup table
Disadvantage:
- Recoding and restating may be required if the lookup values are altered.
Let us consider an example of hard-coded values. Suppose you need to find out some background information about the mail users mentioned above messaging services. For example, the particular type, kind, or category of a user.
For this, we create a User_kinds table that will store data about users’ kinds to recognize it easily. Values in this table may not be changed frequently. So, we can easily apply hardcoding to it. Just add the check constraint to columns or build the check constraint into field validation where the users may sign in to the mail messaging services.
Along with these, you may also consider the method of keeping the details with the master itself. When the number of records per the master remains fixed or when the detailed records are being queries with the master, this approach can be ideal.
However, this method is ideal when there are only a few records in the detail table. Unlike the above, there is no need to use the joins, but there could be an increased complexity in terms of DMI. However, keeping the details with the master itself can help save a lot of storage and disk space.
Now, you had an overview of different popular denormalization techniques, which you can further evaluate against your needs and choose the appropriate modalities. Hope you like this article about Database denormalization techniques for DB users.
Keep Visiting The Tech Diary for more informative articles.