21 Database Modeling and Normalization

The lesson on designing database tables discussed the process of designing database tables. This lesson extends that conversation to describe the process of normalization. Normalization means designing database tables in such a way as to reduce or eliminate errors in the data stored in the tables. First, the lesson describes three common types of errors. It then discusses normalization and how normalization can reduce errors in data.

21.1 Insertion, Update, and Deletion Anomalies

You learned in the lesson on designing database tables that data is stored in separate tables according to the theme of the data being stored. In the museum database used throughout this book, data about artists, works of art, and museums are all stored on separate tables (artist, work, and museum). This is actually the first step in organizing data to reduce errors.

Suppose that all of the data known about artists, works of art, and museums were stored in a single table instead. The table below shows what this table might look like with a sample of the museum data currently stored in the database:

At first, this may seem like a reasonable and efficient way to store the data. At a minimum, it is easy to see how the artist, works of art, and museums are related. On a single row, you can easily identify the artist and the exhibiting museum for each work of art.

One of the main problems with the single table approach is that it often leads to errors or inconsistencies in the data. Three common types of errors, or anomalies, that occur when a database is not designed correctly are insertion anomalies, update anomalies, and deletion anomalies. Each is described below.

21.1.1 Insertion Anomalies

An insertion anomaly refers to an error or inconsistency in data that happens when new data is added to a database. There are at least two ways that these anomalies happen:

1. The inserted data is incomplete.
2. The inserted data is incorrect.

Incomplete data can be problematic, particularly if the missing data is never added to the database. So problematic, in fact, that PostgreSQL will refuse to insert a row at all if values for columns marked NOT NULL are missing. This creates a dilemma if a needed row cannot be added because of that missing data.

In either case, storing all data in a single table increases the likelihood of this type of insertion anomaly. Consider that you need to add the artist Leonardo Da Vinci to the data, but you don't yet have details about his works of art or the museums that exhibit them. Adding just Da Vinci's data to the single wide table creates a number of missing, or NULL, values:

If the table allows these NULL values, there will be a lot of missing values on the table. If, on the other hand, important columns (such as work_name or museum_name) are defined as NOT NULL, the INSERT will fail and you won't be able to add Da Vinci to the database at all. Neither outcome is desirable.

Insertion anomalies also occur when data is inserted incorrectly. A common example of this is a misspelled word. There is always the possibility that a user can incorrectly add a value to a database. However, storing all data in a single table increases the likelihood of this type of insertion anomaly as well. Consider the number of times that the names of individual museums appear in the sample data above. The Museum of Modern Art appears in three of the seven rows in the table.

Each time a value is added to the table, there is a small possibility that the value will be inserted incorrectly. The more times a value is added, the more likely it is that the value will be entered incorrectly at least once, particularly for a value that contains a lot of words or numbers. Inserting the same value repeatedly in a database unnecessarily increases the risk that it will be entered incorrectly at least once. Ideally, a value is entered only once in the database. This minimizes the possibility that it will ever be entered incorrectly.

21.1.2 Update Anomalies

Update anomalies occur when errors in data result from changing the existing values of a table. This most likely happens when data is incorrectly changed. As with insertion anomalies, it is impossible to eliminate the risk that a value will be incorrectly changed, but storing all data in a single table does increase the likelihood of update anomalies.

Recall that The Museum of Modern Art appears in three of the seven rows in the sample data above. Suppose you would like to update the name of The Museum of Modern Art to the shorter and more commonly used name "MoMA". If all of this data were stored in a single table, this would require an UPDATE statement like the following:

UPDATE single_wide_table
SET museum_name = 'MoMA'
WHERE museum_name = 'The Museum of Modern Art';

Even though this particular UPDATE uses a WHERE clause that correctly matches every affected row at once, the underlying problem remains: the same museum name is duplicated across every row that mentions it. If even one of those rows had been misspelled ("The Musem of Modern Art," for instance), that row would silently fail to match the WHERE clause, and the table would be left with two different names for the very same museum. As the number of places that a value is repeated increases, so does the likelihood that at least one of those copies will end up out of sync with the rest.

21.1.3 Deletion Anomalies

The last error discussed in this lesson is a deletion anomaly. A deletion anomaly is an error in the data that occurs when a row is deleted from the database. This results in data being removed from the database that was not intended to be removed.

As with the other anomalies discussed in this lesson, deletion anomalies are much more likely to occur if the database is not organized correctly. If all of the data is stored on a single table, deletion anomalies are very likely to occur. Assume you want to remove the work named "The Child's Bath":

DELETE FROM single_wide_table
WHERE work_name = 'The Child''s Bath';

Notice that this particular work of art is the only work exhibited at The Art Institute of Chicago in this sample data. It is also the only work of art by Mary Cassatt. If you were to delete "The Child's Bath," all of the information about The Art Institute of Chicago and Mary Cassatt would also be deleted. If later you wanted to add a different work of art by Cassatt, or one exhibited at The Art Institute of Chicago, you would have lost the information about Cassatt and the Institute and would have to re-enter it from scratch.

21.1.4 Organizing Data to Reduce Anomalies

Insertion, update, and deletion anomalies can be reduced or even eliminated if the data is properly organized. The first step of this process is to store data in separate tables according to theme. In this case, you store the data about artists, works of art, and museums on separate tables — exactly what the artist, work, and museum tables already do. These tables are connected using primary and foreign keys, as described in the lesson on table relationships and cardinality.

With the data organized this way, the three example tasks work out very differently.

Add the artist Leonardo Da Vinci to the database. When the data was stored on a single table, inserting Da Vinci without work or museum data resulted in a number of NULL values. With the data split across tables, adding Da Vinci to the artist table creates no NULL values on the other two tables at all:

INSERT INTO artist (artist_id, full_name, nationality, style, birth, death)
VALUES (507, 'Leonardo Da Vinci', 'Italian', 'High Renaissance', 1452, 1519);

If one of Da Vinci's works is later added to the database, it is added to the work table and linked to him by storing his artist_id as a foreign key — no other row needs to be touched.

Update the name of The Museum of Modern Art to "MoMA." When the data was stored in a single table, the name had to be updated in every row that mentioned it. With museum data stored on its own table, the update only ever needs to touch one row:

UPDATE museum
SET name = 'MoMA'
WHERE museum_id = 512;

Delete the artwork named "The Child's Bath." With the data organized on a single table, deleting this work of art resulted in the loss of all of the data about Mary Cassatt and The Art Institute of Chicago at the same time. With the data organized into separate tables, deleting "The Child's Bath" from the work table leaves the artist and museum rows untouched:

DELETE FROM work
WHERE work_id = 210;

Mary Cassatt's row in the artist table and The Art Institute of Chicago's row in the museum table are unaffected, since neither of them is stored in the work table at all.

21.2 Database Normalization

Organizing data properly involves following specific rules. The process of organizing data according to these rules is called normalization. When a database follows these rules, it is said to be normalized.

Normalization is a staged process. This means that data is organized to comply with the normalization rules one at a time. When a database complies with the first rule, it is said to be in first normal form. Once it also conforms to the second rule, it is in second normal form.

Since this is a staged process, you would not apply the second normalization rule without first ensuring that the data is in first normal form. Because of this, when a database is in second normal form, it is also in first normal form (and so on for each higher normal form).

There are quite a few normalization rules. The more rules a database complies with, the less likely it is that the data will contain anomalies. However, as a database becomes more normalized, it becomes harder to use, and each additional rule brings marginally less reduction in the possibility of anomalies. For both of these reasons, there is general consensus that third normal form (complying with the first three rules of normalization) is the most practical target for most databases. The remainder of this lesson describes the first three normal forms.

21.2.1 First Normal Form

The first normalization rule prescribes that data stored in a database table should be in its simplest form. In practice, this means that there can't be a table of data within a table. A table can violate first normal form in different ways. Here are two examples.

Suppose you wanted to list the works of art created by each artist directly on the artist table. This would be easy if each artist had created only one work of art, but most artists in the database have created multiple works. One way to store these multiple values would be to place them all in the same column:

Notice the four works of art for Renoir. Organizing them into a single column is problematic. First, it would be difficult to sort the column based on the works of art — which of the many values should be used for sorting? Second, it would be similarly difficult to use the values in a WHERE clause — which of the values should be tested? Finally, it would be difficult to use the values to JOIN the artist table to the work table — which of the values would be used in the join? For these reasons, multiple values should not be stored in the same column of a table.

The second example involves separating the values into multiple columns instead:

Although this approach does store a single piece of information in each cell of the table, it is still difficult to work with. For example, which column should be used to sort the table, which column should be used in the criteria of a WHERE clause, and which column should be used to JOIN the artist and work tables? Additionally, it is difficult to know how many work columns to include on the table in the first place — what happens to the artist who has a fifth work? This approach is also unworkable.

For the data in a table to follow the first rule of normalization, it must be atomic, or in its simplest form. This means that each element of the table must be stored as a single value for each row in a single column of the table.

If the data cannot be fit into an atomic value, it must be stored in a separate table. Since many artists have created multiple works of art, it would be impossible to store the works of art as a single atomic value in a single column of the artist table. This is exactly why works of art are instead stored on the separate work table in the museum database, linked back to artist through the artist_id foreign key.

21.2.2 Second Normal Form

To define what it means to be in second normal form, you must first understand the concept of functional dependency. Functional dependency involves how the columns in a database table are related to each other. A column is said to be functionally dependent on another column if the values of the first column determine the values of the second.

For example, an artist's full_name is functionally dependent on their artist_id. If a specific artist_id is known, you can unambiguously determine the full_name of that artist. However, birth is not functionally dependent on nationality. If you know a specific nationality, you cannot determine the value of birth — artists of the same nationality are born in different years.

The second rule of normalization specifies that all of the non-key attributes of a table must be functionally dependent on the primary key of the table. Consider a museum table that has been modified to also store an artist_name column:

All of the columns on this table are functionally dependent on the primary key, museum_id — except artist_name. If you know the museum_id, you can unambiguously determine the values of every other column (except artist_name). Multiple artists' works are exhibited in most museums, so knowing the museum_id does not tell you precisely which artist to list on the table. Therefore, artist_name does not belong on the museum table. Since an artist's name is functionally determined by artist_id rather than museum_id, it makes the most sense to keep artist names on the artist table, exactly as the museum database already does.

21.2.3 Third Normal Form

Once you have determined that a table is in second normal form (that it complies with the rules for both first and second normal form), it is time to check whether it is also in third normal form. To be in third normal form, all of the non-key columns of a table must be functionally dependent on only the primary key (or a column that could be the primary key) — not on some other, non-key column.

Examine a work table that has been modified to also store a museum_name column:

All of the non-key columns on this table are functionally dependent on the primary key, work_id. This includes museum_name — since a work of art can only be exhibited at a single museum, knowing the work_id tells you exactly which museum is exhibiting it. This table is in second normal form.

Notice, though, that museum_name is also functionally dependent on museum_id. If you know the museum_id, you can determine exactly the name of the museum. Although museum_name is functionally dependent on the primary key (work_id), it is also functionally dependent on a different, non-key column of the table (museum_id). This does not automatically disqualify the table from third normal form — that depends on whether museum_id itself could be the primary key of the work table. For a column to be a primary key candidate, all of its values on the table must be unique. Notice that the value 512 for museum_id repeats on the table above (rows 211 and 212). Because of this, museum_id cannot be the primary key of the work table, which means museum_name is functionally dependent on a non-key column and therefore does not comply with the third normalization rule.

To fix this, the column that breaks the rule should be moved to a different table — one where the column it depends on (museum_id) actually is the primary key. In this case, museum_name belongs on the museum table, exactly as it already sits in the museum database. The work table only needs to store museum_id as a foreign key; JOINing to museum whenever the museum's name is needed keeps that name recorded in exactly one place.

21.3 Summary

This lesson described how to organize a database to reduce or eliminate the likelihood of errors. Three types of errors — insertion anomalies, update anomalies, and deletion anomalies — were presented, along with ways to reduce the likelihood of each. The specific rules for organizing, or normalizing, data were also presented. Specifically, the lesson described the rules for organizing a database up through third normal form.