Database normalization
lastmod 2023-11-07created 2023-11-071 edits
In the design of relational databases, the process of structuring data to minimize redundancy is called normalization. The goal of database normalization is to restructure relations that have anomalies in order to produce small, well-organized relations. In general, normalization involves dividing large, poorly organized tables and relations into small, well-organized tables and relations. The objective of normalization is to make insertions, deletions, and modifications of data in a single table propagate to the rest of the database through the defined relations.
Edgar F. Codd, the inventor of the relational model, introduced the concept of normalization in 1970, known as the first normal form (1NF). Codd went on to define the second normal form (2NF) and third normal form (3NF) in 1971, and in 1974 defined the Boyce–Codd normal form (BCNF) together with Raymond F. Boyce. Normal forms of 4NF and higher were later defined by other theorists, and the most recently introduced is the sixth normal form (6NF), introduced in 2002 by Christopher J. Date, Hugh Darwen, and Nikos Lorentzos.
Informally, a relational database table (in computer engineering terms, a relation) is said to be normalized if it is in third normal form (3NF). Most 3NF tables are free of insertion, update, and deletion anomalies, and most 3NF tables are in BCNF, 4NF, and 5NF. (However, they are generally not in 6NF.)
The standard guidance for database design is that a database should be designed to be fully normalized; afterward, some parts may be denormalized for performance reasons. However, some modeling disciplines, such as dimensional modeling for data warehouse design, exceptionally recommend denormalized designs. That is, designs that are in large part not in 3NF.
Categories
- 1NF
- 2NF
- 3NF
- BCNF