3NF DATA MODELLING(BILL INMON)

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.

Importantly, the 3NF EDW is not typically optimized for direct business intelligence queries. Because the model is normalized, analytical queries may require multiple joins. Instead, dimensional data marts, such as star or snowflake schemas, are derived from the EDW to serve reporting and dashboard needs. In this design, the EDW acts as the “hub,” and downstream data marts act as “spokes” tailored for specific analytical use cases.

Consider a loan approval environment. A bank implementing an Inmon-style EDW would maintain normalized tables for Customer, Loan, ApprovalDecision, Officer, and Branch. Each entity is stored once, even if sourced from multiple operational systems. If analysts need a Loan Approval dashboard, a dimensional mart is built from the EDW, perhaps containing a FactLoanApproval table with Customer, Product, Date, and Officer dimensions. The mart supports fast queries, but the EDW remains the authoritative backbone.

The strengths of 3NF Data Modelling in an EDW context include strong governance, high data quality, minimized redundancy, and enterprise-wide consistency. It aligns well with regulatory and compliance requirements because every data element can be traced back to an integrated, controlled source. It also provides a flexible foundation for creating multiple downstream analytical views without duplicating integration logic.

However, this approach has trade-offs. Designing and maintaining a 3NF EDW can be complex and time-consuming. Query performance for end users may be slower unless dimensional marts are created. The model is also less intuitive for business users compared to denormalized star schemas.

From a modelling family perspective, 3NF Data Modelling is firmly rooted in relational theory and Third Normal Form principles. It contrasts with Kimball’s dimensional-first approach, where data marts are built directly for analytics and later integrated conceptually. Inmon’s philosophy is EDW-first: build the normalized enterprise core, then derive marts as needed.

In summary, 3NF Data Modelling in an Enterprise Data Warehouse represents a governance-first strategy. It emphasizes integration, integrity, and long-term consistency. While not optimized for direct analytics, it provides the disciplined foundation upon which reliable, enterprise-scale reporting and decision-making systems can be built.

ERD Diagram

Table Definitions (minimal 3NF attributes)

• Customer
  • CustomerID (PK)
  • Name
  • Segment
  • ValidFrom
  • ValidTo
• Officer
  • OfficerID (PK)
  • Name
  • BranchID (FK)
  • ValidFrom
  • ValidTo
• Branch
  • BranchID (PK)
  • BranchName
  • Region
• Loan
  • LoanID (PK)
  • CustomerID (FK)
  • PrincipalAmount
  • InterestRate
  • OriginationDate
  • ValidFrom
  • ValidTo
• Approval
  • ApprovalID (PK)
  • LoanID (FK)
  • OfficerID (FK)
  • DecisionDate
  • StatusID (FK)
  • Comments
• Status
  • StatusID (PK)
  • StatusName

3NF EDW – Loan Approval (Inmon Style)

Core Entities & Keys (3NF)

• Customer (CustomerID PK) → one row per enterprise customer
• Officer (OfficerID PK, BranchID FK) → one row per officer
• Branch (BranchID PK) → reference for officer’s branch
• Loan (LoanID PK, CustomerID FK) → one row per loan account/contract
• Approval (ApprovalID PK, LoanID FK, OfficerID FK, StatusID FK) → one row per approval decision event
• Status (StatusID PK) → lookup for approval status values

Time variance can be modeled either with history tables (Type 2) or validity columns on entities that change (shown below).

How It Reads (3NF logic)

• Loan L001 belongs to Customer C123 (Ali), was approved on 2025-01-05 by Officer O045 (Noor) from Central branch, for 100,000 at 5.5%.
• Each concept is stored once (customer, officer, branch, status), linked by foreign keys → minimal redundancy, strong integrity.