The data model for analysts: entities, relationships and the data dictionary

The spec says “customer”, the interface says “buyer”, the database has user and account, accounting has “counterparty”. Everyone is sure they mean the same thing. The difference surfaces when one customer turns out to have two accounts and the report counts them twice.

The data model is usually treated as the developers’ business: tables, keys, indexes. But a database schema answers “how is it stored”, and the analyst needs a different question answered — “what exists in the domain at all, and under which rules”. If nobody asks it, the schema answers it, and answers in the way that is convenient to store rather than the way the business works.

The price of that answer arrives later: a rule baked into the structure changes not with an edit to the spec but with a migration.

Logical model and database schema

The distinction is old and useful. A logical model describes entities, their meaning, relationships and rules — with no reference to technology. The physical schema decides how to lay that out in tables, which fields to index, what to denormalise for speed.

The analyst owns the first and does not need to master the second. But they do need to notice when the physical schema silently changes the meaning: for example, “a customer has one account” became a unique index, while in real life one customer sometimes has two.

Three questions for every entity

What is it — in one sentence. Not “an order is an order” but “an order is a commitment to sell the listed goods at a fixed price to one buyer”. A definition that cannot be written means there are actually two entities.
What tells one apart from another. What makes two records the same thing: a number, a combination of fields, an external identifier? The answer decides what counts as a duplicate and how data from two sources is merged.
How it lives. Who creates it, which statuses it passes through, whether it can go back, whether it is ever deleted — or only marked closed.

Deletion is a requirement of its own. “Delete a customer” can mean three different things: hide from the interface, anonymise the data, or erase it physically along with the order history. For reports, contracts and the law these are different decisions, and the developer should not be the one choosing.

Relationships are where requirements hide

Every relationship between entities is a business rule written in the form “one to many”. Such rules change, and they are the most expensive to change, because everything else rests on them.

Relationship in the model	Question worth asking	What breaks if the answer is “yes”
An order belongs to one customer	Are there orders without a customer — guest checkout?	Customer reports, deleting a customer, repeat purchase
An order has one delivery address	Can part of an order ship somewhere else?	Delivery pricing, statuses, partial returns
A product has one price	Is the price at order time needed, not the current one?	Old orders recalculated at the new price
A user has one role	Is someone ever both a manager and a reviewer?	Access rights, segregation of duties

A good habit is to phrase a relationship in words both ways: “an order has exactly one customer; a customer has zero or more orders”. The word “exactly” almost always draws an objection, and that objection is a requirement found.

The data dictionary

The model shows entities and relationships. The data dictionary says what each field means. That is where the questions live that otherwise surface at acceptance: does the total include delivery, in which currency, how is it rounded.

Order.total
  meaning        amount payable by the buyer, including delivery, after discounts
  format         number, 2 decimal places, order currency
  mandatory      yes, from the moment the order is confirmed
  source         calculated by the system; never edited by hand
  rules          rounding per BR-07; unchanged by a partial return,
                 the refund is stored separately
  example        1,249.90

There is no need to keep a dictionary for every field in the system. The ones that take part in calculations, reports and integrations are enough: that is where different people meet, each with their own understanding.

Where the analyst’s job ends

Column types, indexes and normalisation are not the subject of a spec. But some properties of data look technical and are actually requirements — and cannot be added retroactively:

Change history. If nobody stores who changed a status or an amount and when, the past cannot be reconstructed — not for a dispute, not for a report.
Retention. How long data is kept and what happens after: delete, anonymise, move to an archive.
Uniqueness. What must not be duplicated — and what to do when a duplicate arrives from another system anyway.
Mandatoriness over time. A field can be optional on creation and mandatory on closing — a lifecycle rule, not a form rule.

The sign there is no model: asked “what is an active customer”, sales, accounting and the report all answer differently — and all are right.

A twenty-minute check

Take three entities from the product’s main scenario. Ask a developer and the customer, separately, to define each in one sentence. Compare. Every divergence is a requirement that currently lives in two heads.

What next

The model says what is stored and how it connects. It says nothing about how much of it there will be, how fast it must be found, or how long the system may be down. Those are non-functional requirements — and they are most often written so that they cannot be checked.