Data modeling

Teams model data before they build because the structure of the data outlives every application written against it. A schema that was designed in an afternoon will be queried, migrated and worked around for years, so an hour spent on the model saves weeks later.

Why does data modeling matter?

• Shared vocabulary. The conceptual model is where the business and the engineers agree on what a customer, an order or a subscription actually is.
• Fewer rewrites. A relationship that is wrong in the model is a one-line fix. The same mistake in a production table with ten million rows is a migration project.
• Query performance. The physical model decides which questions are cheap to answer. A model built around the real access patterns stays fast as data grows.
• Integration. Every ETL pipeline and every report reads the model, so a clear one makes everything downstream simpler.

What are the levels of data modeling?

The hero diagram shows a customer and subscription domain at each level.

Level	What it includes	Leaves out	Reader
Conceptual	The main entities and how they relate	Attributes, keys, types	Stakeholders, product, early design
Logical	Attributes, primary and foreign keys, cardinality, normalization	Database-specific types, indexes	Architects, data modelers, engineers
Physical	Tables, column types, indexes, constraints, partitions	Nothing; this is what the database runs	Engineers, DBAs

In the diagram, the conceptual panel says only that a customer has one or more subscriptions. The logical panel names the attributes and makes customerId a primary key in one table and a foreign key in the other. The physical panel turns those into customers and subscriptions tables with serial, varchar and int columns, a unique constraint on email and an index on the foreign key. Each panel is a legitimate deliverable on its own, and the conceptual and logical levels are usually drawn as an entity relationship diagram.

What are the techniques of data modeling?

Normalization

The standard technique for application databases. Each fact is stored in one place, so it cannot disagree with itself, and tables are split until every non-key column depends only on the key. Third normal form is the usual target. The trade-off is more joins, which is fine for transactional workloads that read and write small sets of rows.

Dimensional modeling

The standard technique for a data warehouse. Numbers that get summed, such as revenue or quantity, go in a fact table. The things you group them by, such as date, product, customer and region, go in dimension tables that hang off it, which gives the layout its name, the star schema. It denormalizes on purpose: a dimension repeats values so that analysts can query without deep joins.

Document modeling

The technique for NoSQL document stores. Instead of splitting data into tables, you design the documents around how the application reads them, embedding data that is read together and referencing data that is shared or changes independently. An order document might embed its line items and reference its customer. The relational model is still worth drawing first, because it shows what you are choosing to duplicate; see NoSQL vs SQL for how to make that choice.

How do you do data modeling?

1. Gather the requirements. Collect the questions the data must answer and the actions it must support, from the people who will use it.
2. Draw the conceptual model. Name the entities and the relationships between them in business terms, and get agreement before adding detail.
3. Build the logical model. Add attributes, choose keys, set cardinality and normalize. Resolve many-to-many relationships with join tables.
4. Choose the technique for the workload. Normalize for an application, model dimensionally for analytics, design documents for a document store.
5. Produce the physical model. Pick types, add indexes for the known queries, add constraints for the business rules and write it as a database schema in migrations.
6. Test it against real queries. Write the ten most important queries against the model before data arrives and check that each one is simple and fast.

What are common data modeling mistakes?

• Starting at the physical level. Jumping to CREATE TABLE skips the conversation where the business tells you what the entities really are.
• Modeling the UI. A table per screen produces duplication and awkward joins once a second screen needs the same data.
• Wrong cardinality. Treating a many-to-many as a one-to-many works until the first customer with two addresses.
• Missing the access patterns. A normalized model that is correct but needs eight joins for the home page will be denormalized in a hurry later.
• Denormalizing without a reason. Copying columns for speed before measuring creates two sources of truth that will drift.

How does data modeling compare with related practices?

Practice	What it produces	How it relates
Entity relationship diagram	A drawing of entities and relationships	The notation for the conceptual and logical levels
Database schema	The definition a database enforces	The output of the physical level
Data flow diagram	How data moves between processes and stores	Shows movement where a data model shows structure
Data lineage	Where each field came from and what depends on it	Tracks data across models once pipelines connect them

What tools help with data modeling?

• Dedicated data modeling tools. These manage the three levels as linked models and generate DDL for several databases, which suits large enterprise schemas and the teams that govern them.
• Schema-as-code tools. Migration frameworks and schema DSLs keep the physical model in version control and can draw it, so the diagram and the database cannot disagree.
• AI diagramming tools. Tools like Eraser turn a description of your domain or an existing schema into an entity relationship diagram you can keep editing as text, which is the fastest way to get a conceptual or logical model in front of stakeholders.

For more on this kind of diagram, see data models.

Frequently asked questions