Database Normalization: 5th Normal Form and Beyond

This article follows on from the 4th normal form article.

There are normal forms beyond 4th that are mainly of academic interest, as the problems they exist to solve rarely appear in practice. This series won't discuss then in detail, but for those interested, the following example provides a taste.

The sales rep example

Sales repCompanyProduct
Felicia PowersExclusiveBooks
Afzal IgnesundWordsworthMagazines
Felicia PowersExclusiveMagazines

Usually you would store this data in one table, as you need all three records to see which combinations are valid. Afzal Ignesund sells magazines for Wordsworth, but not necessarily books. Felicia Powers happens to sell both books and magazines for Exclusive. However, let's add another condition. If a sales rep sells a certain product, and they sell it for a particular company, then they must sell that product for that company.

Let's look at a larger data set adhering to this condition:

Looking at a larger set of data

Sales repCompanyProduct
Felicia PowersExclusiveBooks
Felicia PowersExclusiveMagazines
Afzal IgnesundWordsworthBooks
Felicia PowersWordsworthBooks
Felicia PowersWordsworthMagazines

Now, with this extra dependency, you could normalize the table above into three separate tables without losing any facts, as shown below:

Creating a table with Sales rep and Product

Sales repProduct
Felicia PowersBooks
Felicia PowersMagazines
Afzal IgnesundBooks

Creating a table with Sales rep and Company

Sales repCompany
Felicia PowersExclusive
Felicia PowersWordsworth
Afzal IgnesundWordsworth

Creating a table with Company and Product

CompanyProduct
ExclusiveBooks
ExclusiveMagazines
WordsworthBooks
WordsworthMagazines

Basically, a table is in 5th normal form if it cannot be made into any smaller tables with different keys (most tables can obviously be made into smaller tables with the same key!).

Beyond 5th normal form you enter the heady realms of domain key normal form, a kind of theoretical ideal. Its practical use to a database designer os similar to that of infinity to a bookkeeper - i.e. it exists in theory but is not going to be used in practice. Even the most demanding owner is not going to expect that of the bookkeeper!

For those interested in pursuing this academic and highly theoretical topic further, I suggest obtaining a copy of An Introduction to Database Systems by C.J. Date, at the time of writing in its 8th edition, or Relational Theory for Computer Professionals by the same author.

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.