Knowledge Hub

(Projection–Join Normal Form)

Fifth Normal Form (5NF), also known as Projection–Join Normal Form (PJNF), eliminates redundancy that appears only when a table is decomposed into smaller projections and then re-joined. Unlike earlier normal forms, which focus on functional or multi-valued dependencies, 5NF addresses join dependencies, situations where a table can be reconstructed from multiple smaller tables, yet still contains hidden redundancy.

A table is in 5NF if it is already in Fourth Normal Form and every join dependency in the table is implied by the candidate keys alone. If a table can be decomposed into smaller tables in such a way that the original data can be perfectly reconstructed by joining them, without introducing spurious rows, then the decomposition should be performed.

To understand this more intuitively, consider an expanded loan example. Suppose we create a table that records relationships between Loan, Guarantor, and CollateralType together in one structure:

Sixth Normal Form (6NF) represents the most fine-grained level of relational decomposition used in practical modelling. A table is in 6NF when it is already in Fifth Normal Form and has been decomposed so that each relation captures a single fact about a key, typically with explicit time validity. In this form, every table represents one attribute, or a tightly bound set of attributes, over time.

The guiding idea of 6NF can be summarized simply: one attribute, one timeline, one table.

Earlier normalization stages focused on eliminating redundancy caused by partial dependencies, transitive dependencies, multi-valued dependencies, and join dependencies. Sixth Normal Form goes further by decomposing data to its smallest meaningful components, particularly when time becomes an important dimension.

To understand 6NF in the loan approval context, consider a Loan table that includes attributes such as Amount, InterestRate, ProductType, and Status. In earlier normal forms, these attributes might coexist in a single table keyed by LoanID. However, if each of these attributes can change independently over time, storing them together may create temporal anomalies.

Domain–Key Normal Form (DKNF) represents the theoretical “ultimate” stage of normalization. A relation is in DKNF if every constraint on the table can be expressed solely as either a domain constraint or a key constraint. If any rule governing the data cannot be enforced purely through domain definitions or key definitions, the relation is not in DKNF.

A domain constraint defines what values are valid for a column. For example, a DecisionStatus column may allow only APPROVED, REJECTED, or PENDING. An InterestRate column may allow only non-negative numeric values. These constraints govern permissible values.

A key constraint defines uniqueness. It specifies which combination of attributes uniquely identifies a row. For example, LoanID may uniquely identify a loan. A composite key such as (LoanID, EffectiveStart) may uniquely identify a time-bound fact.

The rule for achieving DKNF is conceptually simple but practically demanding. Starting from Sixth Normal Form, all remaining constraints, including business rules, dependencies, conditional requirements, and cross-attribute logic, must be expressible through domain or key rules alone. No non-trivial constraints should remain outside these two categories.

Event-Driven Data Modelling structures data around events, discrete occurrences that capture something that has happened, typically with a timestamp and contextual details. Instead of centering the model on static entities such as Customer or Loan, this approach treats events as the primary unit of design. Each event represents a fact in time.

The goal of event-driven modelling is to enable real-time processing, streaming architectures, and event sourcing patterns. By designing systems around events, organizations can react immediately to business activity while maintaining a complete, replayable history of what occurred.

In this model, the focus shifts from storing the current state of entities to recording event streams. Events are immutable and append-only. Once an event is written, it is never updated or deleted. Typical events in a loan approval domain might include LoanApplicationSubmitted, LoanApplicationApproved, or RepaymentMade. Each event contains identifying information, contextual data, and a timestamp.

Temporal Data Modelling is the practice of structuring data so that changes over time are explicitly captured and preserved. Instead of storing only the current state of information, temporal models record how data has evolved, enabling systems to answer questions about the past, the present, and even planned future states.

The primary goal of Temporal Data Modelling is to enable time-aware queries, regulatory compliance, auditing, and historical analysis. In many industries, especially banking, insurance, healthcare, and government, organizations must demonstrate not only what data looks like today, but what it looked like at a specific point in time.

Temporal modelling distinguishes between different types of time. Valid Time refers to when a fact is true in the real world. For example, an interest rate may be valid from January to March. Transaction Time refers to when the fact was recorded in the system. A correction entered in February about a January event has a different transaction time from its valid time. When both are captured together, the model is called bi-temporal.

3NF Data Modelling, championed by Bill Inmon, organizes enterprise data in a normalized structure, typically Third Normal Form (3NF), to build a centralized Enterprise Data Warehouse (EDW). The warehouse is subject-oriented, integrated, time-variant, and non-volatile, serving as the authoritative single source of truth for the organization.

The primary goal of a 3NF Data Warehouse is to create a consistent and governed enterprise data foundation. By eliminating redundancy and enforcing referential integrity, the EDW ensures that core entities such as Customer, Loan, Officer, and Product are defined once and integrated across all source systems. This approach prioritizes data quality, traceability, and long-term governance over direct query performance.

In this architecture, data from operational systems is extracted, cleansed, standardized, and loaded into normalized tables. For example, Customer data from a CRM system and Customer data from a Core Banking system are reconciled and integrated under a single enterprise identifier. Loan terms, balances, approval decisions, and officer assignments are stored in separate but related 3NF tables, each with clearly defined primary and foreign keys. Redundancy is minimized, and relationships are explicitly maintained.

Data Vault Data Modelling belongs to the family of Ensemble Data Modelling.

In discussions about modern data warehousing, terms like Data Vault (DV), Business Vault (BV), Raw Vault, and Information Vault are often used, leading to confusion. However, it’s crucial to clarify there is only one Data Vault methodology, designed to streamline and enhance data warehousing. Originating in 2000, Data Vault was developed as a comprehensive, agile warehousing method that effectively manages complex data requirements. What some refer to as “Business Vault” or “Raw Vault” are not distinct systems but extensions within the core Data Vault model, designed to address specific needs within the same foundational framework. This misconception often creates unnecessary complexity; it’s more accurate to simply refer to “Data Vault” as a single, unified approach.

To fully understand Data Vault modelling, it's helpful to first revisit foundational Data Warehouse concepts. Decades ago, Bill Inmon, known as the "Father of Data Warehousing," introduced the Data Warehouse concept, based on third normal form (3NF) data modelling, which emphasizes organizing data by business entities to reduce redundancy and maintain integrity. Ralph Kimball later popularized the dimensional approach, which uses star or snowflake schemas to simplify data for analytical purposes. This article will focus on the Data Vault model, which is a modern, agile alternative that addresses some of the limitations of traditional models.

Just like Data Vault, Focal Point Data Modelling also belongs to the family of Ensemble Data Modelling.

The modelling process in the Focal methodology is organized into five distinct stages, referred to as the Focal Forms:

• Focal Form One – Identify and define the primary business concept entities.
• Focal Form Two – Identify and define the natural business relationships between the core business concepts.
• Focal Form Three – Identify the attributes of the core business concepts, focusing on capturing terms or attributes that are categorized as descriptive characteristics of the business concepts.
• Focal Form Four – Group the attributes into Descriptor Concepts. This step is unique to Focal, as it emphasizes good definition practices. The task is to categorize attributes into one of 10 predefined Descriptor Concepts, each with a specific definition. For example, one such descriptor concept is Condition, which is a qualifying attribute related to a Focal. A Condition serves two main purposes: (1) it imposes constraints or modifications on the Entity’s characteristics or behaviour, and (2) it specifies requirements that must be met by the Entity. These Conditions are often established through agreements between relevant parties regarding specific Focal members. In practice, Conditions take the form of concrete parameters that influence business relationships or transactions. For instance, in a Contract Focal, the start date acts as a temporal Condition; in a Loan Focal, the interest rate represents a financial Condition; and in an Order Focal, the payment date serves as a temporal Condition that needs to be met.
• Focal Form Five – Atomic Context. Determine which attributes group together at the most granular level, answering a specific atomic question. This step is crucial for physical implementation, as it helps identify attribute sets that consist of a small number (usually no more than five) of related attributes.

Just like Data Vault & Focal Point, Anchor Data Modelling also belongs to the family of Ensemble Data Modelling.

These methodologies aim to design data models that are flexible, scalable, and adaptable to changing business requirements. Anchor Data Modelling focuses on simplifying the Modelling process, improving data governance, and ensuring data consistency while supporting agile data integration and analytics.

The modelling process in the Anchor methodology is organized into below list of elements from which main are Anchor, Knot and Attribute.

The main elements of Anchor Data Modelling, as you mentioned, include Anchor, Knot, Attribute, and Tie. Below is an elaborate explanation of each of these elements:

1. Anchor

The Anchor is the fundamental building block of Anchor Data Modelling. It represents a core entity or concept that exists over time and is independent of other attributes. Anchors are stable and persistent throughout their lifecycle.

The Unified Star Schema (USS) is an advanced dimensional modelling approach that enhances traditional star schemas by introducing a central structure known as the Puppini Bridge. This bridge aligns facts and dimensions into a single, consistent analytical framework, making queries more reliable, intuitive, and resistant to common join errors.

The primary goal of USS is to provide a unified query entry point that eliminates ambiguity when working with multiple fact tables. It simplifies drill-across analysis and ensures business users receive consistent results without needing deep SQL expertise.

In a traditional dimensional warehouse, each business process, such as Loan Approvals, Repayments, or Customer Interactions, typically has its own fact table. While dimensions may be conformed across these stars, combining multiple fact tables in a single query can lead to common modelling pitfalls: