Data Modeling Fundamentals: Where Do You Actually Start?

In most projects the first table exists before the first definition. That is understandable — you can show a table, while a definition first has to be agreed on. And that agreement decides whether you end up with answers or with numbers three departments argue about.

What a data model actually is

When I ask in coaching sessions what a data model is, the answer is almost always about tables. Mine is a different one: a model that collects all the terms of a company, the relationships between those terms and the rules in between — in the language of the business, not in the language of the database or some operational system.

What you call it matters little to me. Conceptual model, business data model, business terms model, information model — as long as you agreed on one name in your project, the label is a matter of taste. The thing itself I do care about.

An information model is not an ER diagram eight meters wide and two meters high, the kind we printed out in a project and proudly hung on the wall. It is not a table layout either, and it is not a dbt layer or the semantic layer in your BI tool. Those are expressions for one technology, and they do not replace the model. It is definitely not a document written once and parked on SharePoint ever since.

It is not just the definition itself, it is how the definition comes about. An information model is a negotiated agreement out of the business. The value sits in the argument about it, and the diagram is only the record of that agreement. Why AI demands that agreement harder than BI ever did is in the AI series from spring. Without semantics the machine guesses, and what you get is a guess with good grammar.

The three levels — and the one that gets skipped

Anyone who has opened a modeling book knows the three levels. Conceptual: which terms exist in the company, how they relate, what they mean. Logical: attributes, keys, cardinalities, still without a database in mind. Physical: tables, columns, indexes, partitioning, for one concrete platform.

Where conceptual ends and logical begins blurs in practice, and I see little point in drawing that line artificially clean. Take both together as the business model: technology-independent, in the language of the people who run the business. That is what I mean when I say information modeling.

In projects it is about something else anyway, not about that boundary. Almost everyone runs straight to the physical level (implementation), because there is something to do there: a schema, a load process, a deployment, something you can show in a sprint review. The business work looks like talking. It is also the part no tool and no AI takes off your hands, and the only part where somebody has to decide in the end.

→ Covered in depth in part 1: The three levels — and the one that gets skipped, following one term from its first definition all the way to a column. Published September 9.

Why this gets expensive

Every company puts a value on computers, buildings, vehicles and machines. Data is just somehow there. There is a department for buildings, because facility management has to happen, and a company with a department for the information map is something I have rarely come across. One of my clients has a CDO and a team called Data Curators; that is the exception I remember.

I see the bill most clearly in project durations. If every new project has to work out again what a customer is, the project takes a year. With an information model as the blueprint it takes three months. One client put it to me like this: our business data models, these are our asset, our IP. It's not the implementation technology.

And still the benefit shows up nowhere. A good data model prevents a problem, and prevented problems don't appear in any status report. Everything went fine today — which is why nobody in the project I am thinking of celebrated the model.

→ Covered in depth in part 2: What does "employee" mean? The conceptual model, on a single number a client could not produce for weeks. Published September 16.

The method question comes second

In workshops a different question comes up first almost every time: 3NF, Data Vault or dimensional? I understand that, because it sounds like a decision you can make and then tick off. It is the second question, though. The method follows from the requirement rather than from the technology, and it assumes you already know what you are modeling.

There is also a misunderstanding that keeps holding on: that dimensional equals star schema and is therefore physical. Dimensional thinking is business work, long before a table exists. Fact-Oriented Modeling (article in German) makes the same point more sharply, by decoupling the conceptual model from the physical one and automating the transformation afterwards.

And part of what passes for modeling in projects turns out to be a business rule on closer inspection. I worked that distinction through on the 1:M link back in 2014 (in German), and it has not become easier since.

→ Covered in depth in part 3: From model to implementation — and the checklist, with a decision tree per layer and the question of what became of that decision after two or three years. Published September 23.

How you know a model is good

There is a test that needs no tooling. Take a number people argue about in your company — headcount, for example. Can someone from outside the domain, or an AI, build the same number from your model that the business builds? If not, then what you are missing is the information model, not the technology and not another fancy add-on.

A term goes vague as soon as you look closely. I went through that with customer already, for insurance, e-commerce and B2B SaaS. How expensive the second question gets, George McGeachie told under one of my posts recently. A bank was fined by the regulator after three departments delivered three different figures for active credit card accounts. What was unclear was not only what active means, but also whether the subject is an account or a card. Nobody in that bank had ever decided either term.

→ Also a topic of part 3, there as a written-out checklist along the categories of the Data Model Scorecard®.

This question is eleven years old

In March 2015 I asked here whether data modeling is still necessary with Data Vault (in German). The answer back then was yes, and it still is. What changed is who supplies the counter-argument: it used to be Data Vault, today it is AI. The article is still up, under 500 words, and I would not take back a sentence.

If you want to start somewhere, please don't start an initiative to model everything. Take five to twenty terms people argue about daily, or that your current use case needs — customer, contract, location, or something apparently harmless like active or cancelled. Three things per term: a definition you agree on, an owner, and the relationships to the other terms. Documented in a place you can version. A modeling tool is a dream, of course, but an Obsidian vault with Git does the job too.

All the experts out there may forgive me if that sounds oversimplified. If you see it differently, tell me — I find that discussion interesting every time.

So long,
Dirk