Database normalization: principles, normal forms, and design trade-offs
An overview of relational database normalization: goals, core concepts (functional dependency, keys), normal forms (1NF through 5NF/BCNF), decomposition, practical trade-offs and history.
Database normalization is a systematic process used in relational database design to organize data into tables (relations) and columns (attributes) so as to reduce redundancy and avoid undesirable update, insertion, and deletion anomalies. The process applies a sequence of formal criteria called normal forms. Each normal form imposes constraints on how attributes relate to one another; applying them progressively decomposes a schema into simpler structures while preserving the original information.
Key concepts
- Relation (table): a set of rows (tuples) sharing the same attributes (columns).
- Attribute: a named column that holds a particular kind of data.
- Functional dependency: a constraint indicating that the value of one set of attributes determines the value of another (A -> B).
- Key: an attribute or minimal set of attributes that uniquely identifies a row.
- Redundancy and anomalies: duplicated data can cause inconsistent updates and extra storage; anomalies appear when changes must be made in multiple places.
- Lossless-join and dependency preservation: desirable properties of decompositions. Lossless-join ensures no information is lost when tables are joined back; dependency preservation lets constraints be checked locally.
Normalization starts by examining the functional dependencies that hold in the data. Using those dependencies, a designer decomposes a relation into two or more relations such that the decomposition is lossless (reconstructable without spurious rows) and, ideally, preserves dependencies so integrity checks remain straightforward. Formal methods and algorithms assist in this process, though practical choices often balance theory against performance.
Common normal forms
- First Normal Form (1NF): ensures that each column contains atomic (indivisible) values and that each row-column intersection holds a single value, not a set or repeating group.
- Second Normal Form (2NF): built on 1NF; eliminates partial dependencies of non-key attributes on a part of a composite key.
- Third Normal Form (3NF): removes transitive dependencies where non-key attributes depend on other non-key attributes; commonly used in practical designs.
- Boyce–Codd Normal Form (BCNF): a stricter variant of 3NF requiring every nontrivial functional dependency to have a superkey on the left side; stronger but sometimes harder to preserve dependencies when decomposing.
- Fourth Normal Form (4NF): deals with multi-valued dependencies to prevent independent multi-valued facts from being stored together.
- Fifth Normal Form (5NF or PJNF): addresses join dependencies and ensures that a relation cannot be nontrivially decomposed further without loss of information.
Higher normal forms (beyond 3NF) are important for eliminating subtle redundancy types, but they appear less frequently in everyday schemas. Designers often aim for 3NF or BCNF as a pragmatic target and apply further normalization only when necessary.
Decomposition, algorithms and trade-offs
Decomposition splits a table into two or more tables based on dependencies. Two properties guide good decompositions: lossless-join, which guarantees the original relation can be reconstructed by joining the parts, and dependency preservation, which allows enforcement of constraints without performing costly joins. Algorithms exist that synthesize a 3NF schema from a set of dependencies or decompose into BCNF; these can be automated in design tools.
Normalization improves data integrity and typically reduces storage for redundant values, but it can increase the number of joins required at query time. In high-performance or analytic environments, designers sometimes deliberately denormalize — keeping some redundancy or combined tables — to speed reads at the expense of more complex updates. Indexing, materialized views, and careful query optimization are common complements to normalization.
History and practical importance
The relational model and the concept of normalization originated in the early 1970s, driven by the need to manage data reliably and predictably. Normalization remains a foundational technique in relational database design, taught in database courses and applied in industry to produce schemas that are easier to maintain, less error-prone, and clearer to reason about. Understanding when to normalize and when to relax constraints is a key skill for database architects working in transactional systems, data warehouses, or mixed workloads.
Notable considerations
- Normalization is a design guideline, not an absolute rule: performance, scale, and operational constraints influence final schema decisions.
- Modeling choices (keys, dependencies) must reflect real business rules; incorrect dependencies can lead to over- or under-normalization.
- Testing decompositions against representative data and queries helps ensure that theoretical benefits translate into practical improvements.
Proceedings
Goal: Consistency increase through redundancy avoidance
When normalizing in these areas, columns (synonymous terms: fields, attributes) of tables within these areas (the data schemas) are first split into new columns, e.g. addresses into postal code, city and street. Tables are then split, for example a table
tbl_AddressesAll with the fields Company, Street, Postal Code and City into these tables:
- tbl_Addresses with the fields AddressID, Company, Street and Postcode
- tbl_PLZOrt with the fields postcode and city
See image Splitting the table tbl_AddressesAll - where the table tbl_Addresses still gets the unique primary key AddressID.
Note: In this example it is assumed that there is only one place name for each postal code, but this is very often not the case in Germany - e.g. in rural areas, where sometimes up to 100 places "share" one postal code.
The purpose of normalisation is to reduce redundancies (multiple recording of the same facts) and thus prevent anomalies (e.g. due to changes in not all places), in order to simplify the updating of a database (changes only in one place) and to ensure the consistency of the data.
Example
An example of this: A database contains customers and their addresses as well as orders assigned to the customers. Since there can be several orders from the same customer, entering the customer data (possibly with address data) in the order table would result in it appearing there several times, although the customer always has only one set of valid data (redundancy). For example, it can happen that incorrect address data is entered for the customer in one order, and the correct data is entered in the next order. This can lead to contradictory data - in this table or in relation to other tables. The data would then not be consistent, you would not know which data is correct. Possibly even both addresses are incorrect because the customer has moved (solution see below).
In a normalized database, there is only one entry for the customer data in the customer table to which each order of this customer is linked (usually via the customer number). In the case of the relocation of a customer (another example is the change of VAT), there would be several entries in the corresponding table, but they are additionally distinguishable by the specification of a validity period and can be uniquely addressed in the above customer example via the combination order date/customer number.
Another advantage of freedom from redundancy, which still plays an important role today with millions of data records in a database, is the lower memory requirement if the data record of a table, for example tbl_order, refers to a data record of another table, for example tbl_customer, instead of containing this data itself.
These are the recommendations that are made on the basis of the theory of normalization in database development in order to ensure, above all, consistency of the data and an unambiguous selection of data. However, the freedom from redundancy sought for this purpose competes with processing speed or other goals in special use cases. It may therefore make sense to dispense with normalization or to reverse it by means of denormalization, in order to
- increase the processing speed (performance) or
- simplify queries and thus reduce the susceptibility to errors or
- Map specific features of processes (for example, business processes).
In these cases, automatic reconciliation routines should be implemented regularly to avoid inconsistencies. Alternatively, the data in question can also be locked for changes.
Questions and answers
Q: What is database normalisation?
A: Database normalisation is an approach to designing databases which was introduced by Edgar F. Codd in the 1970s. It involves breaking data into separate groups, known as tables, and establishing relationships between them to provide useful information.
Q: What is a flat file database?
A: A flat file database is where all of the data is grouped together like in a spreadsheet. This can lead to a lot of blank spaces and repeated information, making it more likely that mistakes will occur.
Q: How does relational databases reduce the chance of mistakes happening?
A: Relational databases break the data into groups, reducing the chance of mistakes happening and not taking up any more space than necessary.
Q: What are normal forms?
A: Normal forms are criteria that different databases must meet in order for them to be well designed relational databases. There are several "normal forms", each with their own set of rules which the database should be designed to meet.
Q: What are some drawbacks of meeting certain sets of criteria for normal forms?
A: The drawback of meeting such a set of criteria is usually that querying certain data from the database will become more difficult.
Related articles
Author
AlegsaOnline.com Database normalization: principles, normal forms, and design trade-offs Leandro Alegsa
URL: https://en.alegsaonline.com/art/25648
Comments
Weaknesses in the data model due to a lack of normalization can - in addition to the typical anomalies - mean a higher effort in a later further development. On the other hand, normalization steps can be deliberately omitted during database design for performance reasons (denormalization). A typical example of this is the star schema in the data warehouse.
The creation of a normalized schema is supported by automatic derivation from a conceptual data model; in practice, an extended entity-relationship model (ERM) or a Unified Modeling Language (UML) class diagram serves as a starting point for this. The relation schema derived from the conceptual design can then be checked using the normalizations; however, formalisms and algorithms exist that can already ensure this property.
Instead of the original ER model developed by Peter Chen in 1976, extended ER models are used today: The Structured ERM (SERM), the E3R model, the EER model, and the SAP SERM used by SAP AG.
If a relation schema is not in the 1NF, this form is also called non-first normal form (NF²) or un-normalized form (UNF).
The process of normalizing and decomposing a relation into the 1NF, 2NF, and 3NF must preserve the recoverability of the original relation, that is, the decomposition must be true to the compound and true to the dependency.