Normalization Pros and Cons

What is Database Normalization

Data normalization is a method in which data properties within a data model are organized to enhance the coherence of entity types. Or in simple words, you can define as Database normalization is the method of planning data within a database in the most proficient manner plausible.

Advantages of Normalization

There are two primary advantages of having a highly normalized data schema:

Increased consistency: Storage of information at one place reduces the probability of inconsistent data.
Comfortable data mapping: Highly-normalized data schemas, in general, are closer conceptually to object-oriented schemas because the object-oriented goals of improving high coherence and loose coupling between classes result in similar solutions (at least from a data point of view).

The Levels of Data Normalization

Following table reviews the three common traditional forms of normalization (First (1NF), Second (2NF), and Third (3NF)) explaining how to set entity types into a sequence of growing levels of normalization.

LevelRule
Level 1The 1NF entity doesn’t contain repeating groups of data.
Level 2When an entity is in 1NF and all of its non-key characteristics are fully dependent on its primary key. It is called 2NF.
Level 3When an entity is 2NF and when all of its characteristics are straight dependent on the primary key.

First Normal Form (1NF)

An entity type is in first normal form (1NF) when it includes no duplicating groups of data. For example, In the following diagram, you can recognize that there are various duplicating characteristics in the data Order0NF table – the ordered item data repeats nine times and the contact information is repeated double, one for shipping details and another time for billing information. Although this introductory version of orders can manage, what occurs when an order has more than nine order items? Do you create extra order records for them? What about the huge bulk of orders that only have one or two items? Do we really need to misuse all that storage space in the database for the empty fields? Likely not. Moreover, do you want to write the code needed to process the nine copies of item information?

An Initial Data Schema for Order

Following image displays a reworked data schema where the order schema is placed in the first normal form. The presentation of the OrderItem1NF table allows us to have as many, or as few, order items linked with an order, extending the flexibility of our schema while reducing storage obligations for small orders (the majority of our business). The Contact Information1NF table offers a similar advantage when an order is shipped and billed to the same person. we can use the same contact information record in the database to reduce data repetition. OrderPayment1NF was inserted to allow customers to perform several payments against an order- Order0NF can receive up to two payments, the type meaning something like “MC” and the description “MasterCard Payment”, although, with the new style, more than two payments could be maintained, possibly one per payment type. Multiple payments are accepted only when the total of order is large enough that a customer need pay via more than one way, possibly paying some by check and some by debit card.

An essential thing to notice is the use of primary and foreign keys in the new solution. Order1NF has held OrderID, the original key of Order0NF, as its primary key. To manage the relationship back to Order1NF, the OrderItem1NF table holds the OrderID column in its schema. When a new table is added into a schema, in this case, OrderItem1NF, as the result of first normalization efforts it is simple to use the primary key of the original table (Order0NF) as a component of the primary key of the latest table. Because OrderID is not different for order items, you can have various order items on an order, the column ItemNumber was utilized to form a composite primary key for the OrderItem1NF table. An unusual approach to keys was exercised with the ContactInformation1NF table. The column Contact ID, a surrogate key that has no business reason, was given the primary key.

Second Normal Form (2NF)

Although the solution shown in 1NF is improved over that of initial data schema, it can be normalized more. The following image presents the data schema of the image in the second normal form (2NF). When an entity is in 1NF and all of its non-key characteristics are fully dependent on its primary key. It is called 2NF. This was clearly not the problem with the OrderItem1NF table, therefore we need to add the new table Item2NF. The difficulty with OrderItem1NF is that item information, such as the name and price of an item, not based on the order for that item. For example, if Harold orders three widgets and Carter orders five widgets, the matter is that the item is called a “widget” and that the unit price is $19.95 is constant. This data depends on the concept of an item, not the concept of an order for an item, and hence should not be saved in the order items table – therefore the Item2NF table was introduced. OrderItem2NF retained the Whole Price Extended column. The value of the subtotal before tax column within the Order2NF table is the total of the values of the total price increase for each of its order items.

Conclusion

That is the complete process. Having begun with an unnormalized table we ended with two normalized tables in 2NF and this can be extended to 3NF. You will see that duplication has been excluded. There are advanced normalization flow techniques for organizing the various types of data in the database.

Hope this post has helped you. But if you want any more help on Database Normalization, then click here.

Related Posts:

Powered by Contextual Related Posts