Data modelling basics
By the end of this module you will be able to:
- Create conceptual and logical data models
- Apply normalisation rules to reduce redundancy
- Choose between relational and document models for different use cases
Conceptual, logical, and physical models for three audiences
Data modelling has three levels for three audiences: conceptual, logical, physical.
Data modelling is three levels with three audiences: conceptual (business reviewers), logical (architects), physical (DBA + platform). Each level adds detail at the cost of audience scope. DAMA-DMBOK 2 Chapter 5 names the layers; skipping a layer loses the bridge between business and storage.
Normalisation trade-off across forms and workloads
Normalisation level is a trade-off against workload; OLTP wins from 3NF and OLAP wins from star.
Normalisation is a trade-off: 3NF reduces anomalies but explodes joins for analytics; denormalised stars cut join cost at the price of update anomalies. The right level depends on the workload, not on aesthetics. Codd 1971 defined the normal forms; Kimball + Inmon named the analytics counter.
Real-world problem · ongoing
A retailer stored customer addresses in 14 different tables. Updating one meant updating 14.
A UK retail chain stored customer address data in 14 separate tables across its order management, CRM, billing, loyalty, and marketing systems. Each system had its own copy of the address. When a customer moved house, each system needed a separate update.
Normalisation, the discipline of reducing redundancy in data models, would have stored the address once and linked it to each system by reference. The previous module covered experimental design. This module covers how to structure data so it stays consistent and efficient.
Every time a customer moved, 14 tables needed updating. Inevitably, some were missed. The result: conflicting addresses, returned mail, and compliance failures. How does data modelling prevent this?
Data modelling is the practice of designing how data is structured, stored, and related. A good model reduces redundancy, enforces consistency, and makes queries efficient. A bad model creates the 14-table address problem: data scattered across copies that inevitably diverge.
A useful model separates business meaning from implementation detail.
18.1 Three levels of data modelling
Data modelling operates at three levels, each answering a different design question:
- Conceptual model: what are the real-world things and relationships? Example: "A Customer places Orders. An Order contains Products."
- Logical model: what attributes, keys, relationships, and constraints express that meaning independent of a database product? Example: Customer(customer_id PK, name, email), Order(order_id PK, customer_id FK, order_date).
- Physical model: how will this be implemented in a specific technology? This covers table names, column types, indexes, partitioning, storage engine, and query pattern.
Worked example: "Customer" is easy to say and hard to model. Is the customer a person, a household, an account, a legal entity, or a billing relationship? The conceptual decision determines whether the same person can hold multiple accounts, whether a household can share one account, and how consent or erasure applies.
“A data model is a communication tool. If it cannot be understood by both business users and engineers, it has failed its primary purpose.”
DAMA-DMBOK2 (2017) - Chapter 5, Data Modelling and Design
DAMA frames models as communication tools, not technical artefacts. The conceptual model must be readable by a finance director. The physical model must be implementable by a DBA. Both are data models; they serve different conversations.
Common misconception
“We use MongoDB so we do not need data modelling.”
Document databases still benefit from modelling. Deciding which data to embed in a document versus reference from another collection is a modelling decision. Without it, you end up with inconsistent nested structures, unbounded document growth, and queries that scan entire collections. The trade-offs differ from relational databases, but the need for design does not disappear.
Once the entities are clear, normalisation controls duplication and update errors.
18.2 Normalisation
Normalisation is the process of organising data to reduce redundancy, dependency, and update anomalies. Three normal forms cover many practical transactional designs:
- First Normal Form (1NF): every cell contains a single value (no lists, no repeating groups). A cell containing "Red, Blue, Green" violates 1NF.
- Second Normal Form (2NF): all non-key attributes depend on the entire primary key, not just part of it. In a table with a composite key (order_id, product_id), the product_name depends only on product_id, not on the full key. Move it to a separate Products table.
- Third Normal Form (3NF): no non-key attribute depends on another non-key attribute. If postcode determines city, then city depends on postcode, not on the primary key. Move city to an Addresses table keyed by postcode.
The leadership implication is consistency. If address, product, price, or eligibility facts are copied into many places, each copy becomes a future disagreement. Normalisation reduces that risk. Deliberate denormalisation can still be right for analytics, but it should be a performance decision made after the canonical fact is understood.
“Future users of large data banks must be protected from having to know how the data is organized in the machine.”
E.F. Codd, 'A Relational Model of Data for Large Shared Data Banks' (1970) - Opening abstract
Codd's point is data independence: users should not need to understand storage layout to use data correctly. Normalisation supports the same discipline by separating stable facts from duplicate or implementation-driven copies.
Common misconception
“Normalisation should always be applied to the maximum degree.”
Over-normalisation creates excessive joins that slow query performance. Analytics workloads often benefit from deliberate denormalisation (star schemas, wide tables) that trades storage space for query speed. The right level of normalisation depends on the workload: transactional systems (many writes) benefit from higher normalisation; analytical systems (many reads, few writes) benefit from denormalisation.
A table has columns: order_id, product_id, product_name, product_category, order_date. The primary key is (order_id, product_id). product_name depends only on product_id. Which normal form is violated?
An e-commerce company considers using MongoDB (document database) for its product catalogue. Products have varying attributes: electronics have voltage and warranty fields; clothing has size and material. Which model is more suitable?
A data warehouse team denormalises a star schema by embedding dimension attributes directly into the fact table, creating a wide table with 200 columns. What are the trade-offs?
Modelling checks to carry forward
- Conceptual models define real-world meaning. Logical models define attributes, keys, and relationships. Physical models define implementation choices.
- Normalisation reduces duplication and update anomalies. The Kent mnemonic tests whether each non-key attribute describes the key, the whole key, and nothing but the key.
- Document databases still require modelling. Embedding, referencing, indexing, and document growth are design decisions, not reasons to avoid design.
- Analytics often denormalises for query speed. That should be a conscious workload trade-off, not a substitute for understanding the canonical facts.
Standards and sources cited in this module
Data Modelling and Design knowledge area
Industry guidance on conceptual, logical, and physical data modelling.
Kent, W. (1983). 'A Simple Guide to Five Normal Forms in Relational Database Theory'
Full paper
Source for the 'key, whole key, nothing but the key' mnemonic and accessible normalisation explanation.
Full paper
Foundational paper establishing the relational model. Every normalisation concept traces back to Codd's original work.
MongoDB Documentation, 'Data Modeling Introduction' (2024)
Full guide
Document modelling patterns including embedding vs referencing decisions.
Module 18 of 26 · Applied Data