Database normalization in MySQL: Four quick and easy steps

Benefits of database normalization in MySQL

Reduced usage of storage space by intelligently categorizing data is one of the many benefits database normalization lends to MySQL.

It aids in better, faster, stronger searches as it entails fewer entities to scan in comparison with the earlier searches based on mixed entities. Data integrity is improved through database normalization as it splits all the data into individual entities yet also builds strong linkages with the related data.

Mike Hillyer, a technical writer in MySQL AB (now Oracle Corporation), said: “The goal of database normalization is to ensure that every non-key column in every table is directly dependent on the key: the whole key and nothing but the key. With this goal come the benefits in the form of reduced redundancies, fewer anomalies and improved efficiencies.”

Normalize your data easily

The following example will illustrate how database normalization helps achieve a good design in MySQL.

The table below presents data that needs to be captured in the database:

In the example shown above, a lot of storage space will be wasted if any one criterion (author or publisher) is considered as the identification key, therefore database normalization is essential.

It is a step-by-step process that cannot be carried out haphazardly. The following steps will help in attaining database normalization in MySQL.

Step 1: Create first normal form (1NF)

The database normalization process involves getting data to conform to progressive normal forms, and a higher level of database normalization cannot be achieved unless the previous levels have been satisfied. First normal form is the basic level of database normalization.

For 1NF, ensure that the values in each column of a table are atomic; which means they are unique, containing no sets of values. In our case, Author and Subject do not comply.

One method for bringing a table into 1NF is to separate the entities contained in the table into separate tables. In our case, this would result in Book, Author, Subject and Publisher tables.

Book’s table:

Author’s table:                                                                                         

Subject’s table:

Publisher’s table:

Step 2: Define relationships

Three types of relations can be established:

• One-to-(Zero or)-one (Example: marriage)
• One-to-(Zero or)-many (Example: kids)
• Many-to-many (Example: Facebook)

The Book’s table may have many to many relations with the Author’s table.

Author’s table may have many books and a book may have more than one author.

The Book’s table may have many to many relations with the Subject table.

The books may fit in many subjects and the subjects may have many books.

Many-to-many relations have to be presented by “link” tables

Book Author table:

Book_Subject table:

One-to-many in our example will be Books to Publisher. Each book has only one Publisher but one Publisher may have many books.

We can achieve one-to-many relationships with a foreign key. A foreign key is a mechanism in database management systems (DBMS) that defines relations and creates constraints between data segments. It is not possible to review what is not related to the specific book. It is not possible to have a book without an author or publisher.

When deleting a publisher, all the related books may need to be deleted along with the reviews of those books. The authors would not need to be deleted.

The foreign key is introduced in the table that represents the “many”, pointing to the primary key on the “one” table. Since the Book table represents the many portion of the one-to-many relationship, the primary key value of the Publisher as in a Publisher_ID column as a foreign key is added.

• ISBN	Title	Pages	Publisher_ID
  1590593324	Beginning MySQL Database Design and Optimization	520	1

Step 3: Make second normal form (2NF)

Second normal form (2NF) cuts down the tautological/superfluous data in a table by selecting it, putting it in new tables and by establishing relations among them.

In database normalization, 2NF is about the relations between the composite key columns and non-key columns. That means the non-key columns have to depend on the whole composite key.

Here, the primary key is composite to eliminate the possibility of the same person writing more than one review of the book. Reviewer URL is dependent only on the Reviewer ID which is only part of the whole composite key.

This table does not comply with the 2NF:

Step 4: Third Normal Form (3NF)

This requires that all columns depend directly on the primary key. Tables violate the 3NF when one column depends on another column which, in turn, depends on the primary key – a transitive dependency.

In the publisher table, the City and State are dependent on the zip code, not the Publisher_ID.

To comply with 3NF we have to move these outside the publisher’s table:

Through the process of database normalization, we bring our schema’s tables into conformance with progressive normal forms. As a result, the tables each represent a single entity – a book, an author or a subject, for example – and we benefit from decreased redundancy, fewer anomalies and improved efficiency.

Ronen Baram is a MySQL sales consultant at Oracle for Australia/New Zealand markets. As part of his job, he meets with clients all over Australia and New Zealand, as well as the rest of APAC. Baram has 15 years of IT experience and has a special interest in Linux OS.

(This tutorial is based on a presentation given by Ronen Baram at Oracle Develop Conference held in Hyderabad this year. Compiled by Sharon D’Souza.)