How many vehicles does Sales actually use? That is the kind of question you assume is already answered before you ask it. The table shows which person belongs to which vehicle, and it is well maintained — no gaps, nothing null. What it does not tell you is whether that person was assigned the car or actually drove it, and the answer turns on exactly that.
The question comes from Yerodin, controller at FastChangeCo, the fictional company I use to make real situations from projects and coaching sessions tangible.
Last week's hub article sketched the map for this series: three levels, and the business one is usually the first to fall away. This part follows a single term through all three — from its first definition to the column it ends up in. At every step you can see what gets lost.
The question nobody can answer
A question from controlling meets a well-maintained table — and stays unanswered.
Xuefang, junior data modeler, looks at the table and finds nothing unusual. A column person_id, a foreign key into the person table, filled everywhere. Her question is the one I get at this point in almost every coaching session: the person is right there, so what is missing?
Diego rephrases it. What did the relationship between vehicle and person mean before it was a combination of columns in a table? Who decided that the two of them belong together in exactly one way?
At FastChangeCo the vehicles belong to the company. Amal has a company car registered to her. The pool cars sit in the yard and get booked, by one person today and someone else tomorrow. Those are two different arrangements between a person and a vehicle, and both cost money in the business — different money.
Three levels, one vehicle
Two relationships between the same two terms, each with its own reason in the business.
Conceptual means: which terms exist, how do they relate, what do they mean. In our case that is two terms, person and vehicle, and between them not one relationship but two. Assigned to is one of them — the company car registered to Amal, with everything attached to it: cost center, taxable benefit, the car policy. Driven by is the other — whoever was actually behind the wheel, for insurance, liability and mileage. Nothing gets built at this level; things get named and decided.
Logical turns that into structure, still without a database in mind. Person and vehicle stay where they are, they are still two terms. The two relationships come in alongside them, each with its own detail: an assignment has a start and an end, a trip has a timestamp and an odometer reading. Over time both are many-to-many — one person drives several vehicles, one vehicle gets driven by several people — so each relationship gets an entry of its own. Those in-between entries are called associative entities. You can talk about cardinality here (how many to how many) if you like — it is still the business side of the job. If you want the three levels from the ground up, I wrote that down back in 2015 (in German).
The same subject across three levels. Going down, person_id is added; valid_from and the odometer fall away.
Physically it becomes one single table. Assignment and trip get pulled into the vehicle — denormalized is the word for it — and what is left is a column called person_id. One foreign key, one value per vehicle. Not a decision against the trip — by that point nobody remembered there were two. What does not get decided at the first level gets decided at the third, by whoever creates the table.
There is a mundane reason this stays invisible. On a company car, the assigned person and the driving person are usually the same. In the vast majority of rows the missing distinction makes no difference at all. So nobody notices, for years. The two only come apart on the pool cars — and on the company car that someone else took out for a day.
About this series: This is part 1 of 3 of "Data Modeling Fundamentals". The overview is in the hub article. Part 2 asks on September 16 what an employee actually is at FastChangeCo; part 3 arrives on September 23 with the method question and the checklist.
Two relationships, one column — sound familiar?
Clarifying terms, naming relationships, negotiating definitions before the first table exists: that is the core of the Data Modeling Master Class. New dates are in preparation.
What it costs later
On the left what actually happened, on the right what the table stores. The rows do not show which reading was meant.
Yerodin gets his number in the end. Questions like that do not stay unanswered — someone looks at the column, picks a reading and runs the calculation. The person on the vehicle is probably the one driving it. From then on that assumption lives in a report filter and nowhere else. It is not in the model, because there is no place for it there.
A few months later cost allocation arrives. The cost center works off the assignment: who has a company car, what does it cost, which team does it land on. Fleet and insurance work off the usage: who drove, how many kilometers, who had the pool car that day. Both take the same column, because there is only one, and both read it differently. On the company cars the results match; on the pool cars they don't.
When this surfaces, it gets reported as a data quality problem. Except the data is fine. Neither report contains a calculation error, so there is no bug for anyone to find. The difference comes out of a decision that was never made.
Cleaning it up is where the real bill arrives. The table does not tell you which row once meant assigned and which meant driven. The distinction was never in there, so it cannot simply be recovered from the data — assuming it ever existed.
What belongs on record for every term
One term, written down: definition, owner, and both relationships with their reason.
The hub article covers how to start from zero: don't model everything, take five to twenty terms people argue about every day. Three things per term — a definition you agree on, an owner, and the relationships to the other terms. Written down somewhere you can version.
Our vehicle shows why the third one is where it hangs. Every relationship comes with a reason: why does it exist in the business? Assigned to exists because of the cost center and the taxable benefit. Driven by exists because of insurance, liability and mileage. Two reasons, so two relationships — the reason is how you notice it isn't one. In the Master Class that is an exercise of its own: write down the reason for every relationship before anything gets built.
And two relationships in the information model mean two implementations in the physical model. Not necessarily two tables, that depends on the method — but certainly not one column meant to carry both.
With the vehicles the meaning was there. Someone knew that assignment and trip are two different things. It just never got written down, and that is how it went missing on the way to the table.
It also works the other way around. Sometimes the meaning was never decided at all, and then there is nothing to lose. Next week is one of those cases: what an employee actually is at FastChangeCo, and why that one number took weeks to produce.
So long,
Dirk