Knowledge Hub

Conceptual Data Modelling (CDM) provides a high-level, business-oriented view of data by identifying core entities and the relationships between them without introducing technical implementation details. Its purpose is to describe how the business views its data, not how the database stores it. At this stage, the emphasis is on clarity and shared understanding rather than normalization, indexing, or storage optimization.

The primary goal of Conceptual Data Modelling is to capture the essence of business information in a way that non-technical stakeholders can understand and validate. Business leaders, analysts, and architects must first agree on what exists in the domain and how those things relate to each other before moving into more detailed design stages. CDM therefore serves as a bridge between business requirements and logical data modelling.

In a loan approval domain, for example, the conceptual model identifies high-level entities such as Customer, Loan, Application, Approval Decision, and Repayment Schedule. It then defines how these entities relate. A Customer applies for a Loan. A Loan results in an Approval Decision. A Loan has a Repayment Schedule. These relationships are represented visually as connected entities, as shown in the referenced diagram, where lines between boxes indicate how entities are associated. At this level, attributes such as interest rate, date fields, or data types are intentionally omitted. The focus remains on “what exists” and “how it connects.”

Logical Data Modelling defines the detailed structure of data entities, attributes, and relationships by applying formal structural principles such as normalization to ensure accuracy, consistency, and elimination of redundancy. Where the Conceptual Data Model described what exists in the business and how entities relate, the Logical Data Model specifies exactly how those entities are structured in terms of attributes, identifiers, and constraints, while still remaining independent of any specific database technology.

The primary goal of Logical Data Modelling is to create a precise and normalized representation of data that is implementation-ready but not yet tied to a particular platform. It acts as a structural blueprint that ensures data integrity, clarity of relationships, and enforcement of business rules before moving into physical optimization. At this stage, ambiguity is removed and formal definitions are introduced.

Physical Data Modelling defines the concrete implementation of data structures within a specific database platform. While the Logical Data Model specifies entities, attributes, and relationships in a technology-independent manner, the Physical Data Model translates that blueprint into executable database objects. At this stage, abstract schemas become real tables, columns, datatypes, indexes, partitions, constraints, and storage configurations tailored to a chosen platform such as PostgreSQL, Oracle, SQL Server, Snowflake, or a cloud-native engine.

The primary goal of Physical Data Modelling is to implement the logical design in a way that supports performance, scalability, reliability, and security. Unlike conceptual and logical models, which remain platform-agnostic, the physical model is deeply technology-specific. Decisions about datatypes, indexing strategies, clustering methods, partitioning schemes, and storage layouts are made with awareness of the target database engine and workload characteristics.

Normalization is the systematic process of organizing data into well-structured tables by decomposing it step by step into progressively refined forms. Its purpose is to eliminate redundancy, prevent data anomalies, and ensure that each fact is stored in exactly one place. Rather than repeating the same information across multiple rows or tables, normalization restructures data so that dependencies are clear, consistent, and logically enforced.

At its core, normalization follows a simple principle: each attribute should depend only on the key, the whole key, and nothing but the key. This means that every piece of information in a table must be directly related to the primary identifier of that table. If an attribute depends on only part of a composite key, or depends on another non-key attribute, the structure must be refined further.

The normalization journey typically progresses through defined stages: Unnormalized Form (UNF), First Normal Form (1NF), Second Normal Form (2NF), Third Normal Form (3NF), Boyce–Codd Normal Form (BCNF), Fourth Normal Form (4NF), Fifth Normal Form (5NF), and in advanced theoretical discussions, Sixth Normal Form (6NF). Each stage removes a specific class of redundancy or dependency issue. Third Normal Form is commonly sufficient for most transactional systems. BCNF strengthens 3NF by addressing certain edge cases involving overlapping candidate keys. Higher forms such as 4NF and 5NF address multi-valued and join dependencies, while 6NF decomposes data to a level where each table represents a single fact, often useful in temporal modelling. Domain-Key Normal Form (DKNF) represents a theoretical ideal in which all constraints can be expressed solely through domain definitions and key relationships.

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.