Introduction to Data Modeling

That picture is the model. Notice that it says nothing about MySQL, indexes, or VARCHAR. It says what exists and how the pieces fit together.

Why the picture is the thing you produce

The librarian cannot read SQL. But she can look at the picture above and tell you, in ten seconds, that you have forgotten something.

She will say the library also keeps a record of fines. That is a missing entity, and finding it now costs nothing. Finding it after the system is built costs a migration and a week of work.

A data model is the only design document that both a business person and a programmer can read. That is most of its value.

A model decides which questions you can answer

This is the part people learn the hard way. A model that does not hold a fact makes some questions impossible, no matter how good the code above it is.

With only Members and Books, all of these are unanswerable:

• How many copies of this title are on loan right now?
• Which member had this exact copy when it came back damaged?
• Which copies have never been borrowed?

Add the Copy entity and every one of them becomes a short query. You did not write cleverer code. You stored a fact you were not storing before.

That is why the model comes before the tables, and well before the code.

What data modeling is not

It is not writing SQL. SQL comes at the end, and it comes almost mechanically once the model is right.

It is not performance tuning. Indexes, caching, and query plans matter, and they come later. A fast answer to the wrong question is still wrong.

It is not the application code. Two applications with very different code can sit on the same model.

Who reads a data model

Business people check that it matches how the work actually happens. They catch missing entities and wrong rules.

Developers use it to know what to build and what each table means.

Database administrators use it to choose storage, indexes, and backup plans.

Analysts use it to write reports, because it tells them where each fact lives.

Because four groups read it, the model is written in plain words first and technical detail later. That is the reason for the three levels of data model, which is the next lesson.

What good modeling gives you

Fewer wrong answers. Rules held in the model are enforced by the database, so no application can break them.

Less repeated data. Each fact is stored once, so it cannot become inconsistent.

Cheaper change. A model that matches the real world absorbs new requirements. A model that does not fights every one of them.

A shared language. When the team says Loan, everybody means the same thing, because the model defines it.

Key Takeaways

• Data modeling is deciding what things you store, what facts you keep about each one, and how they connect.
• A data model is a picture of the data structures and the relationships between them.
• It has exactly three parts: entities (things), attributes (facts about a thing), and relationships (links between things).
• Entities become tables, attributes become columns, and relationships become foreign keys.
• A model that does not hold a fact makes some questions impossible to answer, and no amount of code can repair that.
• The model is the one design document that business people and programmers can both read, which is why it is written in plain words before any technical detail.

The library example above is deliberately small. The next lesson takes the same library and shows it three times, once for each audience, with more detail added at every step.