Data Modelling and Schema Design

Data modelling is the discipline of deciding how parts of reality should be represented as entities, events, attributes, relationships and rules. Schema design turns that model into a formal structure that software and people can use consistently.

A schema is not reality. It is a controlled agreement about which parts of reality the system will represent, how it will name them and what relationships it will preserve.

Every database, spreadsheet, API, event stream, document repository and analytical model contains assumptions about the world. Good modelling makes those assumptions visible. Poor modelling hides them inside column names, ad hoc codes and application behaviour until different systems begin to disagree about what their own data means.

ARTICLE ID: DATA.MANAGEMENT.013
Canonical function: representation structure and schema design
Series route: What Is Data Management? → Data Architecture → Data Modelling and Schema Design.

The First Principle: Decide What One Record Represents

The first modelling question is deceptively simple: what does one record mean?

A row might represent one student, one enrolment, one examination attempt, one order, one order line, one invoice, one sensor observation or one daily summary. Those are different grains. Mixing them produces structural confusion.

Before naming fields, define the grain. Once the grain is stable, identifiers, attributes and relationships become easier to reason about.

Entities, Events and States

Most data models represent some combination of three things:

Entities: relatively persistent things such as people, products, assets, locations or accounts.
Events: occurrences such as orders, payments, submissions, measurements or movements.
States: conditions valid at a time, such as active, enrolled, open, approved or under repair.

Confusing these categories causes poor design. “Student” is an entity. “Student enrolled in Class A” is a relationship with time. “Student submitted Paper 2 at 10:13” is an event. “Submission accepted” is a state of that event.

Conceptual, Logical and Physical Models

Conceptual model

The conceptual model describes the domain in human terms. It asks what kinds of things exist and how they relate.

Logical model

The logical model defines entities, attributes, keys, relationships and constraints without being tied tightly to one storage technology.

Physical model

The physical model implements the logical design in a specific platform: tables, indexes, partitions, document collections, graph edges, storage types or service schemas.

Separating these levels protects domain meaning from accidental technology decisions.

Identifiers and Keys

Identifiers anchor records to entities or events. A stable identifier lets a thing remain the same thing even when names, addresses or other attributes change.

Common key concepts include:

primary key: the identifier used to distinguish one record in a structure;
foreign key: a reference to another entity or record;
natural key: a meaningful identifier from the domain;
surrogate key: a system-generated identifier with little or no domain meaning;
composite key: identity established by multiple fields together.

Good modelling distinguishes identity from description. A person’s name is an attribute, not a safe identity key.

Relationships

Relationships connect entities. A student belongs to a class for a period. An order contains products. A scientist collects a sample. A sensor belongs to an asset. A document has an author.

Common relationship shapes include one-to-one, one-to-many and many-to-many. The many-to-many case often deserves its own relationship entity because the relationship can have attributes: dates, roles, quantity, status or evidence.

Attributes

Attributes describe entities, events and relationships. A model should distinguish essential attributes from convenient copies and derived values.

A useful question for every field is: is this an observation, a source-owned fact, a derived value, a label, or an operational convenience?

Keeping those classes distinct improves provenance and correction.

Constraints

Constraints encode rules that should remain true. They may require fields, restrict ranges, enforce uniqueness or preserve valid relationships.

Examples include:

an order quantity must be positive;
a foreign key must point to an existing entity;
a student identifier must be unique;
a start date must not be later than an end date;
a status must come from an approved reference set.

Constraints turn part of the domain model into executable quality control.

Null, Unknown and Not Applicable

Missing values deserve explicit design. “Unknown”, “not collected”, “not applicable” and “temporarily unavailable” are different states. A schema that collapses all of them into an empty cell loses information.

Nullability should therefore be a semantic decision, not merely a database default.

Normalisation

Normalisation separates facts so that one piece of information is not unnecessarily repeated across many records. This reduces update anomalies and clarifies ownership.

For example, storing a student’s address on every assessment row duplicates master data. A normalised design stores the student identity separately and relates assessment events to it.

Denormalisation

Denormalisation deliberately duplicates or combines information to improve performance, simplify analytics or reduce joins.

Denormalisation is not inherently bad. It becomes dangerous when duplicated values lose their authority chain or cannot be refreshed reliably.

Relational Models

Relational systems represent information in tables linked through keys. They are strong where structured relationships, transactions, constraints and declarative querying matter.

The relational model remains useful because it makes many integrity rules explicit. It is not the only useful representation.

Document Models

Document models group related fields into flexible objects, often represented as JSON-like structures. They are useful when records vary, when nested objects belong naturally together or when applications benefit from retrieving one aggregate at once.

Flexibility increases the need for schema discipline. A system can become inconsistent if every document evolves independently without compatibility rules.

Graph Models

Graph models emphasise nodes and relationships. They can be powerful for networks, dependencies, identity resolution, recommendations, knowledge graphs and systems where paths matter.

The modelling question shifts from “which table?” toward “which entities, edges and edge meanings?”

Event Models

Event models preserve occurrences through time: what happened, when, to which entity and under which context. Event-oriented design is useful for auditability, streaming, operational history and rebuilding state.

An event should distinguish occurrence time from processing time and should preserve stable identity so that retries do not create duplicate business effects.

Wide-Column, Key-Value and Specialised Models

Some workloads favour key-value stores, wide-column structures, time-series databases, vector stores, spatial databases or specialised search indexes. The correct choice follows access patterns, consistency needs, scale, latency and semantics.

Technology should serve the model, not become a substitute for modelling.

Schema-on-Write and Schema-on-Read

Schema-on-write applies structure before or during storage. Schema-on-read may preserve source data more flexibly and apply interpretation later.

The first increases early consistency. The second increases flexibility. Mature architectures often use both in different layers. Flexibility does not remove the need for eventual semantics.

Temporal Modelling

Many facts change through time. A student changes class. A product changes category. An employee changes department. A price becomes effective on a date.

Temporal models distinguish current state from historical state. Useful fields include effective-from, effective-to, event time and recorded time.

Without time-aware design, systems can accidentally rewrite history when current attributes change.

Bitemporal Thinking

Some high-consequence systems need to distinguish when a fact was true in the world from when the system knew or recorded it. A correction entered today may say that a historical fact was different last month.

Keeping both timelines supports audit and reproducibility.

Reference Data in Schema Design

Categories, statuses, currencies, units and code sets should not be scattered as undocumented free text when shared meaning matters.

Reference data creates controlled vocabularies and stable mappings. See Master Data and Reference Data.

Schema Names Are Metadata

Names such as amt, stat or type save characters and lose meaning. Field names should be clear enough to reduce guesswork, but names alone are not definitions.

Important fields need metadata: definition, unit, owner, allowed values, source and lifecycle.

Schema Evolution

Schemas change because the world, organisation and requirements change. New fields appear, old fields are deprecated, definitions become more precise, relationships change and new receiver needs emerge.

Safe evolution requires:

versioning;
compatibility rules;
migration plans;
deprecation periods;
lineage to downstream receivers;
communication and ownership;
historical interpretation of older versions.

See Data Versioning and Change Management.

Backward and Forward Compatibility

Backward compatibility means newer consumers or systems can continue to handle older representations as required. Forward compatibility means older components can tolerate certain newer representations.

Compatibility should be designed, tested and documented rather than assumed.

Data Contracts and Schemas

A schema becomes an inter-system contract when other receivers depend on it. At that point, changing a field is not a local refactor. It is a change to a shared promise.

See Data Integration and Interoperability.

Analytical Modelling

Analytical models often prioritise clear grain, dimensions, measures and historical consistency over operational update efficiency.

A fact table may represent one event per row while dimensions provide time-aware context. The model should prevent double counting and preserve the definitions needed for historical comparison.

See Data Warehousing and Analytics.

Modelling and Privacy

Schema design determines what personal data is collected and how easily it spreads. Privacy can be strengthened by collecting fewer fields, separating direct identity from analytical attributes and modelling deletion and retention explicitly.

A field included “just in case” can become permanent technical debt and privacy exposure.

Modelling and Data Quality

Good schemas prevent entire classes of quality problems. Stable keys prevent ambiguous joins. constraints prevent impossible states. Reference data limits uncontrolled spelling variation. Temporal fields prevent stale values from masquerading as current.

Schema design is therefore preventive data quality.

Modelling for AI

AI systems still depend on structure even when they process text, images or embeddings. Training examples need identity and provenance. Retrieval indexes need source references and access metadata. Evaluation datasets need stable labels and versions. Vector representations need links back to canonical source content.

Unstructured data does not mean ungoverned data.

Worked Example: Student Assessment

A weak model stores student name, class, subject, score and teacher in one spreadsheet row for every test. Names change, teachers repeat, classes change and assessment identity is ambiguous.

A stronger conceptual model separates:

Student;
Class;
Enrolment;
Subject;
Assessment;
Assessment Attempt;
Teacher;
Score and grading context.

Now the system can preserve student identity, class history, assessment versions and score provenance without copying every attribute into every row.

Worked Example: Product Catalogue

A product model may distinguish product family, sellable item, variant, packaging, price, inventory unit and supplier relationship. Treating all of these as one “product” row can create confusion when one product has many variants, prices or suppliers.

Good modelling clarifies which attributes belong to which level.

Worked Example: Scientific Observation

A scientific observation should connect value, unit, sample, instrument, method, time, calibration state and uncertainty. If only the numeric value is stored, the schema has already discarded part of the evidence.

Common Modelling Failure Modes

Name as identity: display labels carry key responsibility.
Mixed grain: one table combines rows representing different kinds of things.
Free-text everything: controlled categories become inconsistent strings.
Current-state overwrite: history disappears when attributes change.
Derived-as-source: calculated values are stored without provenance.
Null ambiguity: missing, unknown and not applicable collapse together.
Schema by convenience: the current application screen dictates the domain model.
Premature universal model: distinct domain concepts are forced into false equivalence.
No deprecation path: old fields remain forever because downstream use is unknown.
Technology-first modelling: the database product decides the representation before the domain is understood.

A Schema Review Checklist

What does one record represent?
Which entities, events and states exist?
What is the stable identity of each?
Which relationships need their own attributes?
Which fields are observations, source facts or derived values?
Which categories should use controlled reference data?
Which constraints can be enforced?
How are missing and unknown states represented?
How does time affect the model?
What history must survive updates?
Which receivers depend on the schema?
How will the schema evolve without breaking them?
What personal data can be removed from the model entirely?
Can a future user reconstruct what each field meant?

A Maturity Ladder

Implicit: structure exists mainly in application code or spreadsheets.
Named: entities and fields have stable names.
Related: keys and relationships are explicit.
Constrained: important rules are enforced structurally.
Temporal: current and historical state are distinguished.
Governed: definitions and schema changes have owners.
Interoperable: shared schemas and mappings preserve meaning across systems.
Adaptive: the model evolves while preserving historical interpretation and receiver compatibility.

The Deeper Principle: A Model Is a Promise About Meaning

A schema does more than organise storage. It tells every future receiver what kinds of things the system believes exist, which differences matter, which relationships are preserved and which states are possible.

Good data modelling therefore begins with humility: the model is a representation designed for a purpose. It should be precise enough to support the job, explicit enough to reveal its assumptions and flexible enough to change when the world proves those assumptions incomplete.

Data Management Series

Final idea: good schema design does not merely make data fit a database. It creates a stable, inspectable agreement between reality, software and future receivers about what each record means and how that meaning may safely change.

Data Modelling and Schema Design | How Reality Becomes Entities, Fields, Relationships and Rules