The disadvantages of database denormalization

Written by rebecca johnson
  • Share
  • Tweet
  • Share
  • Pin
  • Email
The disadvantages of database denormalization
Database Design ("database diagram for rollbook system" is Copyrighted by Flickr user: Glutnix (Brett Taylor) under the Creative Commons Attribution license.)

Relational databases must be normalised in order to be effective. Normalisation is an important step in designing relational databases. A relational database consists of different tables that talk, or relate, to one another. While denormalization can be used to reduce the number of join tables and may speed up processing, it leads to redundant data and an increase in overall database size.

What is Normalization?

Normalisation is how database designers decide on the appropriate fields, tables and table relationships that belong in the database. The benefits of a normalised database are reduced data redundancy and inconsistent dependency, as well as a more intuitive design for users. Redundant data leads to a user having to add, edit or delete the same data from more than one place in the system. Inconsistent dependency forces a user to look in a nonintuitive location for the data.

What is First Normal Form?

Ensuring database normalisation is accomplished by using First Normal Form. This eliminates repeating fields in individual tables. An example of a repeating field is Class 1, Class 2 and Class 3. This design will work until a student takes a fourth class. Another part of First Normal Form is to make sure that each field is in the smallest unit possible. For example, instead of having a field for ClientName, the table should have two fields, ClientFirstName and ClientLastName.

What is Second Normal Form?

Eliminating redundant data across tables and records is Second Normal Form. The developer must ensure that each field appears only in one table in order to stop repetitive data entry. The only time that a field can be in a secondary table is when it is used as a foreign key. An example of this is an instructor table that contains the primary key of InstructorID. InstructorID can show up again in a secondary table called Class to identify who is instructing the class.

What is Third Normal Form?

Third Normal Form makes sure that there is not Inconsistent Dependency. Inconsistency Dependency occurs when a field is in an unexpected table. For example, it would not be intuitive to look for a student's phone in the class table. The easiest way to make sure the field belongs in the appropriate table is to ask the question the <field name> of the <table name> is <data>. For example, the <last name> of the <instructor> is <Johnson>. This makes logical sense. An example of Inconsistent Dependency is the <title> of the <instructor > is <Database Design>.

What is Denormalization?

Sometimes it is appropriate for a designer to purposely denormalize parts of a database. A database designer must do this with forethought in order to negate a negative impact to performance and ease of use. By denormalizing the design, less join tables and foreign keys are required. Denormalization can be used on First and Second Normal Form; however, it should never be used on Third Normal Form.

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.