Knowledge Hub

Denormalization is the deliberate process of combining tables or reintroducing controlled redundancy into a data model to improve query performance and analytical usability. Where normalization focuses on structural purity, integrity, and elimination of redundancy, denormalization prioritizes speed, simplicity, and workload efficiency. It is not a careless design shortcut; rather, it is a conscious architectural decision made after understanding normalized principles.

The core idea behind denormalization is that once data has been properly structured, typically up to Third Normal Form (3NF) or Boyce–Codd Normal Form (BCNF), certain tables may be merged or attributes duplicated to reduce the number of joins required during querying. Instead of reconstructing information dynamically from many normalized tables, denormalized designs pre-assemble commonly accessed data into wider structures. This improves read performance, particularly in analytical or reporting-heavy systems.

The Full Ladder:

UNF → 1NF → 2NF → 3NF → BCNF → 4NF → 5NF → 6NF → DKNF

We now move into a detailed exploration of normalization, not just as a concept, but as a structured progression. Normalization is often introduced briefly in textbooks and then quickly reduced to 3NF in practice. However, to truly understand relational modelling and the rationale behind specialized modelling techniques, it is important to examine the full ladder.

The normalization journey follows a clear sequence:

UNF → 1NF → 2NF → 3NF → BCNF → 4NF → 5NF → 6NF → DKNF

Each stage builds upon the previous one. At every step, structural weaknesses are removed and data integrity is strengthened. The objectives remain consistent throughout:

• Eliminate redundancy
• Prevent update, insert, and delete anomalies
• Increase structural integrity
• Prepare the model for specialized architectural use

This progression is not merely theoretical. It represents the structural discipline that underpins clean relational design.

Unnormalized Form (UNF) represents the raw and unstructured state of data before any normalization rules are applied. It is the natural starting point of most data collections, whether they originate from spreadsheets, CSV extracts, operational exports, or loosely designed legacy systems. In UNF, structure exists, but discipline does not. Data is captured for convenience rather than integrity.

In this form, tables may contain repeating groups, multi-valued attributes, and mixed entities within a single row. For example, in the loan approval case, a single row may contain customer information, loan details, approval status, and multiple repayment dates, repayment amounts, and repayment statuses all embedded within the same record. Columns such as RepayDates, RepayAmounts, and RepayStatuses may store several values in one cell, separated by commas or line breaks. This violates relational principles because each column should hold a single, atomic value.

First Normal Form (1NF) introduces the first formal rule of relational structure: every attribute must contain atomic, indivisible values. This means that each cell in a table must hold exactly one value, not a list, not a set, and not a repeating group. In addition, each row must represent a single instance of an entity, and every record must be uniquely identifiable.

In the Unnormalized Form, we saw repayment dates, amounts, and statuses stored as multi-valued attributes inside a single row. This violates relational principles because a column should not contain multiple values. First Normal Form eliminates this issue by removing repeating groups and restructuring the data so that each field contains only one value.

Applying 1NF to the loan approval example means separating multi-valued repayment information into individual rows. Instead of storing multiple repayment dates in a single column such as “Jan-31, Feb-28,” we create separate rows for each repayment instance. Each repayment date, repayment amount, and repayment status becomes a distinct record. As a result, the table grows vertically rather than horizontally, and repeating groups are eliminated.

Second Normal Form (2NF) builds on First Normal Form by eliminating partial dependencies. A table is in 2NF if it is already in 1NF and every non-key attribute depends on the entire primary key, not just part of it. This rule becomes relevant only when a table has a composite primary key, that is, a primary key made up of more than one column.

To understand this clearly, consider the loan approval example after applying 1NF. Suppose we have a table representing repayments with a composite primary key such as (LoanID, RepayDate). This combination uniquely identifies each repayment record. However, if we include attributes like LoanAmount or CustomerName in that same table, those attributes depend only on LoanID, not on the full composite key (LoanID, RepayDate). They are not specific to an individual repayment date. This creates a partial dependency.

A partial dependency occurs when an attribute depends on only part of a composite key rather than the whole key. In relational design, this is undesirable because it introduces redundancy and update anomalies. For example, if LoanAmount is stored alongside each repayment record, the same loan amount would repeat for every repayment row. If the loan amount needed correction, multiple rows would require updates.

Third Normal Form (3NF) removes transitive dependencies. A table is in 3NF if it is already in Second Normal Form and every non-key attribute depends only on the primary key, not on another non-key attribute. In other words, there must be no indirect dependency chains inside the table.

A transitive dependency occurs when a non-key attribute depends on another non-key attribute rather than directly on the primary key. This creates hidden redundancy and potential inconsistency. The dependency pattern can be expressed as A → B → C, where A is the primary key, B is a non-key attribute, and C depends on B instead of directly on A.

Returning to the loan approval example, suppose the Loan table contains the following attributes: LoanID (primary key), CustID, CustomerName, and CustomerAddress. While LoanID determines CustID, the attributes CustomerName and CustomerAddress actually depend on CustID, not directly on LoanID. This means there is a transitive dependency: LoanID → CustID → CustomerName. The customer details are not properties of the loan itself; they are properties of the customer.

Hierarchical Data Modelling is one of the earliest approaches to organizing data in computer systems. It structures data in a strict tree-like format, where each child record has exactly one parent. This creates a single-rooted hierarchy composed entirely of one-to-many relationships. The model enforces a rigid parent–child structure in which navigation always follows predefined paths.

The goal of hierarchical modelling is clarity and efficiency in environments where data relationships are predictable and fixed. By organizing records into a tree, systems can navigate from parent to child quickly and deterministically. Each level in the hierarchy represents a deeper level of detail within the same structural path.

Historically, this model dominated the 1960s and 1970s, particularly in mainframe environments such as IBM’s Information Management System (IMS). In such systems, data might be structured as:

Network Data Modelling is a pre-relational database approach that organizes data using owner–member sets, allowing records to participate in multiple parent and child relationships. Unlike the strict tree structure of hierarchical modelling, the network model forms a flexible graph-like structure where records can have many-to-many connections.

The goal of the network model was to overcome the limitations of hierarchical databases. In hierarchical systems, each child could have only one parent, making many-to-many relationships difficult or impossible to represent naturally. The network model removed this restriction by allowing records to be linked through multiple set relationships.

Historically, this model gained prominence in the 1960s through the CODASYL Data Base Task Group and systems such as IDMS. It became widely used in banking, insurance, and government systems before the relational model emerged.

Object-Oriented Data Modelling represents data as objects that combine attributes (data) and methods (behavior), mirroring the principles of object-oriented programming such as classes, inheritance, encapsulation, and polymorphism. Instead of separating data from logic, this approach treats real-world entities as self-contained units that include both state and behavior. The goal was to align persistent storage directly with object-oriented application design, removing the need to transform objects into relational tables.

In this model, entities such as Customer, LoanApplication, or LoanOfficer are defined as classes. Each class contains attributes that describe its state and methods that define its behavior. For example, a Customer object may contain attributes like CustomerID, Name, and CreditScore, along with a method such as calculateRisk(). A LoanApplication object may include attributes such as LoanID, Amount, InterestRate, and Status, together with methods like approve() or reject(). Relationships between entities are expressed through object references rather than foreign keys, and inheritance allows specialized types to extend base classes.

NoSQL Data Modelling refers to the practice of designing data structures for non-relational databases such as document stores, key-value stores, wide-column databases, and graph databases. Unlike relational modelling, which is driven by normalization theory and functional dependencies, NoSQL modelling is driven primarily by application access patterns and workload requirements. The goal is not theoretical purity but scalable, high-performance data access.

The primary objective of NoSQL data modelling is to support flexible or schema-less storage, distributed scale-out architectures, and fast, query-driven design. Instead of decomposing data into highly normalized tables, NoSQL models are often intentionally denormalized or embedded to minimize joins and reduce cross-node communication in distributed environments. Data is structured according to how it will be read and written, not according to relational dependency rules.