Physical Data Modelling (PDM)

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.

In the loan domain example, the conceptual model identified entities such as Customer, Loan, ApprovalDecision, and Repayment. The logical model then defined their attributes, primary keys, and foreign keys. The physical model goes further by specifying exact column types such as BIGSERIAL for identifiers, NUMERIC for financial amounts, DATE or TIMESTAMPTZ for temporal values, and ENUM types to tightly control allowed status values. These choices are not arbitrary; they directly influence storage efficiency, query optimization, and data quality enforcement.

For instance, defining decision status and repayment status as enumerated types prevents invalid values from entering the system. Unique constraints enforce one-to-one relationships at the physical layer, such as ensuring that a loan can reference at most one approval decision. Check constraints guarantee business rules such as positive loan amounts or valid interest rates. Referential integrity constraints enforce the relationships defined logically, ensuring that no repayment exists without a corresponding loan.

Performance optimization becomes central at this stage. Indexes on foreign keys are essential to support efficient joins. Partitioning strategies, such as range partitioning loans by origination date or repayments by due date, allow large tables to scale while maintaining fast query performance through partition pruning. Partial indexes can accelerate high-frequency queries, such as retrieving open repayments, without inflating the index footprint unnecessarily. These are physical decisions driven by workload patterns rather than conceptual structure.

Physical modelling also incorporates operational considerations. DEFERRABLE foreign keys can simplify complex transactional loads. Update timestamp triggers ensure auditability. Change data capture strategies may be implemented to support downstream analytics or event streaming. Storage configuration, clustering, and archival strategies are defined according to regulatory and performance requirements. These concerns do not appear in conceptual or logical models but are critical at the production level.

The nature of Physical Data Modelling is therefore implementation-focused and performance-aware. It is where architectural intent meets operational reality. Logical independence gives way to platform-specific optimization. The same logical model may yield different physical designs depending on whether it is deployed on a traditional relational database, a distributed cloud warehouse, or a lakehouse storage engine.

Physical Data Modelling provides the executable blueprint for database administrators and data engineers. It ensures that the system not only reflects business meaning and logical integrity but also performs efficiently under real workloads. It translates structural precision into operational durability.

In summary, Physical Data Modelling marks the final step in the core progression from abstract business concepts to production-ready systems. Where the Conceptual Model defined what exists, and the Logical Model defined how it is structured, the Physical Model defines how it is implemented and optimized in a specific technology environment. This is the point at which design becomes deployment and architecture becomes running software.

-- 1) Reference/enum domains (tighten data quality)

CREATE TYPE decision_status AS ENUM ('APPROVED','REJECTED','PENDING');

CREATE TYPE repayment_status AS ENUM ('SCHEDULED','PAID','LATE','DEFAULTED');

-- 2) Customer

CREATE TABLE customer (

cust_id BIGSERIAL PRIMARY KEY,

nat_id VARCHAR(32), -- e.g., national ID / passport

full_name VARCHAR(150) NOT NULL,

email CITEXT, -- case-insensitive

phone VARCHAR(30),

created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),

CONSTRAINT uq_customer_nat UNIQUE (nat_id)

);

CREATE INDEX ix_customer_email ON customer (email);

CREATE INDEX ix_customer_updated_at ON customer (updated_at);

-- 3) ApprovalDecision (1:1 per loan when present)

CREATE TABLE approval_decision (

decision_id BIGSERIAL PRIMARY KEY,

status decision_status NOT NULL,

decided_at TIMESTAMPTZ,

decided_by VARCHAR(100),

reason_code VARCHAR(50),

created_at TIMESTAMPTZ NOT NULL DEFAULT now()

);

-- 4) Loan (PARTITIONED by origination year for big tables & time pruning)

CREATE TABLE loan (

loan_id BIGSERIAL PRIMARY KEY,

cust_id BIGINT NOT NULL REFERENCES customer(cust_id),

amount NUMERIC(14,2) NOT NULL CHECK (amount > 0),

currency CHAR(3) NOT NULL DEFAULT 'USD',

product_type VARCHAR(50) NOT NULL, -- e.g., PERSONAL, MORTGAGE

interest_rate NUMERIC(5,3) NOT NULL CHECK (interest_rate >= 0),

term_months SMALLINT NOT NULL CHECK (term_months BETWEEN 1 AND 600),

origination_dt DATE NOT NULL,

decision_id BIGINT UNIQUE -- 0..1; unique enforces 1:1 if present

REFERENCES approval_decision(decision_id)

DEFERRABLE INITIALLY DEFERRED,

created_at TIMESTAMPTZ NOT NULL DEFAULT now(),

updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

) PARTITION BY RANGE (origination_dt);

-- Example yearly partitions (automate this in DDL jobs)

CREATE TABLE loan_y2024 PARTITION OF loan

FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE loan_y2025 PARTITION OF loan

FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Indexing strategy for loan

CREATE INDEX ix_loan_cust ON ONLY loan (cust_id); -- inherited by parts in PG16+, else create per partition

CREATE INDEX ix_loan_origination ON ONLY loan (origination_dt);

CREATE INDEX ix_loan_status_lookup ON ONLY loan (decision_id);

-- 6) Repayment (child rows; heavy-write table → partition by due date)

CREATE TABLE repayment (

repay_id BIGSERIAL PRIMARY KEY,

loan_id BIGINT NOT NULL REFERENCES loan(loan_id) ON DELETE CASCADE,

due_date DATE NOT NULL,

paid_date DATE,

amount_due NUMERIC(14,2) NOT NULL CHECK (amount_due >= 0),

amount_paid NUMERIC(14,2) CHECK (amount_paid >= 0),

status repayment_status NOT NULL DEFAULT 'SCHEDULED',

created_at TIMESTAMPTZ NOT NULL DEFAULT now()

) PARTITION BY RANGE (due_date);

CREATE TABLE repayment_y2025 PARTITION OF repayment

FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Indexing for repayment (FK + common filters)

CREATE INDEX ix_repay_loan ON ONLY repayment (loan_id);

CREATE INDEX ix_repay_due ON ONLY repayment (due_date);

CREATE INDEX ix_repay_status ON ONLY repayment (status);

-- 7) Referential integrity & performance niceties

-- FK support indexes: already covered (cust_id on loan, loan_id on repayment)

-- Optional: partial index to accelerate open items

CREATE INDEX ix_repay_open_items ON ONLY repayment (loan_id, due_date)

WHERE status IN ('SCHEDULED','LATE');

-- 8) Auditable change capture (example)

CREATE EXTENSION IF NOT EXISTS hstore;

-- Row change timestamp trigger (simplified)

CREATE OR REPLACE FUNCTION touch_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$

BEGIN NEW.updated_at = now(); RETURN NEW; END $$;

CREATE TRIGGER trg_touch_loan BEFORE UPDATE ON loan FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

CREATE TRIGGER trg_touch_customer BEFORE UPDATE ON customer FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

From Core to Specialized Modelling

A Necessary Pre-Requisite

We have now completed the Core Data Modelling layers, Conceptual, Logical, and Physical. Together, these layers form the classical backbone of data architecture. The Conceptual Model defined what exists in the business. The Logical Model structured those entities with attributes, keys, and formal rules. The Physical Model translated that structure into executable database implementations.

These three layers provide the foundation. They answer three fundamental questions:

• What entities exist?
• How are they structured and related?
• How are they implemented in a system?

However, real-world systems rarely stop at this foundation. Modern data environments must support analytics, regulatory compliance, historical traceability, scalability, distributed workloads, and increasingly, AI-driven reasoning. Meeting these needs requires moving beyond the classical layers into what we call Specialized Data Modelling Techniques.

Before stepping into that space, there is an essential bridge that must be understood: the balance between normalization and denormalization.

Normalization represents the discipline of structuring relational data cleanly and systematically. It removes redundancy, enforces integrity, and ensures that each fact is stored in one and only one place. This principle underpins transactional systems and protects data accuracy.

Denormalization, by contrast, represents a deliberate and controlled relaxation of those rules. It introduces redundancy intentionally to improve performance, simplify queries, or optimize analytical workloads. In reporting and large-scale analytics systems, strict normalization can hinder performance and usability. Denormalization becomes a pragmatic design choice.

Understanding this balance is critical. Every specialized modelling technique that follows builds upon or reacts to these two forces. Some techniques extend normalization principles to handle agility, historical tracking, and structural flexibility. Others intentionally reshape or flatten structures to enable faster analytics and simpler consumption.

Normalization and denormalization therefore act as the hinge between relational theory and architectural pragmatism. They mark the point where modelling evolves from structural correctness alone to workload-driven optimization.

As we move into Specialized Modelling Techniques, dimensional, ensemble, temporal, object-oriented, NoSQL, and others, keep this hinge in mind. Each technique represents a different answer to the same question:

• How should data be structured to best serve its intended purpose?

The journey now shifts from foundational structure to purposeful design.