Technical Data Model

What a technical data model is

A technical data model is the document that defines the data your system stores: the things it keeps track of, the facts it records about each one, and how those things relate to each other. It is the shared reference that tells engineers, analysts, and product people exactly what "an Order" or "a Customer" means in your system, so everyone builds against the same definitions.

Data models are usually described at three levels of detail, and a good document is explicit about which level it is working at:

• Conceptual — the big picture. The main entities (Customer, Order, Product) and how they relate, with no technical detail. This is the level you show a stakeholder who does not write code.
• Logical — the full set of entities, every attribute, the keys that identify each record, and the relationships between them, expressed independently of any particular database. This is where most of the real design work happens.
• Physical — how the logical model is actually implemented in a specific database: table names, column types, indexes, partitions, and constraints. This is the level engineers build from.

A single document can cover all three, but each level answers a different question. The conceptual level answers "what does the business care about?"; the logical level answers "what exactly do we store and how is it connected?"; the physical level answers "how is it laid out in the database we chose?".

Entities and relationships

An entity is a thing the system needs to remember — a customer, an order, a payment. Each entity has attributes, the individual facts you record about it: a customer has an email address and a name; an order has a status and a total. Entities almost never stand alone; they connect to one another through relationships.

The relationship's cardinality describes how many of one entity can connect to how many of another:

• One-to-one — one record links to exactly one record on the other side (a user and their single profile row).
• One-to-many — one record links to many on the other side (one customer places many orders). This is the most common relationship in business systems.
• Many-to-many — records on both sides link to several on the other (a product appears on many orders, and an order contains many products). In a relational database these are resolved with a junction entity that sits between the two and holds a foreign key to each, often with extra attributes of its own.

Naming each relationship in plain language — "a Customer places Orders", "an Order contains OrderItems" — keeps the model readable and surfaces hidden rules, such as whether a child record can exist without its parent.

Keys, normalization, and integrity

A primary key is the attribute (or combination of attributes) that uniquely identifies each record in an entity — typically an id column. A foreign key is an attribute in one entity that points at the primary key of another; it is how relationships are physically enforced. When a foreign key always points at a real, existing record, the data is said to have referential integrity, and the database can guarantee it for you.

Normalization is the discipline of organising attributes so each fact is stored in exactly one place. The practical goal is to avoid storing the same value redundantly, because redundant copies drift out of sync. The common targets are described as normal forms: first normal form removes repeating groups so each field holds a single value; second normal form removes attributes that depend on only part of a composite key; third normal form removes attributes that depend on another non-key attribute rather than on the key itself. In everyday terms: if an attribute is really a fact about a different entity, give it its own entity and link to it.

Normalization keeps writes safe and data consistent. Sometimes you will deliberately denormalize — duplicate a value to make a read faster — but a good data model treats that as an explicit, justified exception, not the default.

When to write a data model

Write a data model before you create the database for any system that will outlive a single sprint, before two teams need to integrate against the same data, or whenever the shape of the data is itself a point of disagreement. It is far cheaper to rename an entity or fix a relationship on a diagram than to migrate a production table with millions of rows.

Revisit and update the model whenever you add a new entity, change what identifies a record, or alter a relationship. The model is a living document: an out-of-date data model is worse than none, because people trust it and build on the wrong assumptions.

How it relates to the system architecture

The data model and the system architecture describe the same system from two angles. The architecture shows the moving parts — services, queues, the database — and how requests flow between them; the data model zooms into the database those parts share and defines what lives inside it. The architecture says "the order service owns the orders table"; the data model says exactly what an order record contains and what it connects to.

The data model also feeds directly into other technical documents. A technical specification references the entities when it describes behaviour, an integration specification maps external payloads onto your entities, and a product requirements document often surfaces the entities implicitly through the features it describes. Keeping the data model consistent with these documents prevents the quiet drift where the code, the spec, and the database slowly disagree.

Common mistakes to avoid

• Mixing levels without saying so. A diagram that shows business concepts but also column types confuses every reader. State which level each section is at.
• No primary keys. If you cannot say what uniquely identifies a record, the entity is not yet defined.
• Hiding many-to-many relationships. They need a junction entity; pretending otherwise leads to either duplicated data or impossible queries.
• Accidental denormalization. Storing the same fact in two entities guarantees they will disagree. Denormalize only on purpose, and write down why.
• Vague relationships. "Customer relates to Order" tells you nothing. Name the relationship and state the cardinality and whether the link is optional.
• Letting the model rot. A data model that no longer matches the database actively misleads. Update it with the schema, not months later.