Data Modeling Guide for Developers

Data modeling is the discipline of deciding how information is structured before you store it - what entities exist, what attributes they have, and how they relate. It is one of the most consequential things a developer does and one of the least formally taught, so many engineers pick it up by trial and painful error. Yet the payoff is enormous: a good data model makes an application straightforward to build and evolve, while a bad one taxes every query, every feature, and every migration for the life of the system.

This guide is a practical grounding for developers: the three levels of modeling from conceptual to physical, how to identify entities and relationships, how to choose keys, and how normalization keeps a model clean. Throughout, an entity relationship diagram is the thinking tool that makes the model visible, and you can build yours in Atlas Diagram Studio at /diagrams with the ERD tool at /diagram-tools/erd-tool. The aim is not database theory for its own sake but the working judgment that produces schemas you will be glad to live with.

Three levels: conceptual, logical, physical

Data modeling happens at three levels of increasing detail, and keeping them distinct clarifies your thinking. The conceptual model is the highest level: entities and relationships in the abstract, capturing meaning without any concern for database specifics. It is where you decide that customers place orders containing products, and it is the model you can discuss with non-technical stakeholders because it is about the business, not the technology.

The logical model adds structure without committing to a particular database: attributes on each entity, primary and foreign keys, normalized relationships, and cardinality. The physical model is the concrete implementation for a specific database - exact data types, indexes, constraints, and any denormalization for performance. Working top-down through these levels keeps early conversations about meaning and defers technical decisions until the meaning is settled, which prevents the common mistake of arguing about column types before agreeing on what the data even is.

Entities, attributes, and relationships

The heart of data modeling is identifying entities and how they relate. An entity is a thing worth storing with its own identity and attributes - a customer, an order, a product. The reliable way to find them is to look at the nouns in how the domain is described, and the reliable way to find relationships is to look at the verbs connecting those nouns. A customer places an order gives you two entities and a relationship in a single sentence.

Getting relationships and their cardinality right is where models succeed or fail. For each relationship, ask how many of each side relate to how many of the other, and whether either side is optional. One customer has many orders is one-to-many; a product appears on many orders and an order contains many products is many-to-many, which you resolve with a junction entity carrying a foreign key to each side. Drawing these on a diagram in the editor at /diagrams makes the cardinality decisions explicit and catches the ambiguities that plague purely verbal specifications.

Practical modeling principles

A handful of principles separate models that age well from ones that rot. These are the habits worth internalizing.

• Give every entity a stable surrogate primary key - an auto-incrementing id or UUID - that never has to change.
• Make relationships with foreign keys and enforce them with constraints, so the database guarantees integrity.
• Keep attributes atomic - one value per column - so the model reaches first normal form naturally.
• Normalize to third normal form by default, storing each fact exactly once to avoid update anomalies.
• Resolve every many-to-many relationship with a junction entity, and put relationship attributes there.
• Push rules into the schema with NOT NULL, UNIQUE, and CHECK constraints rather than trusting application code.
• Name entities and columns consistently and clearly, since names are the model's documentation.
• Model for the domain's truth first, and denormalize only later where measured performance demands it.

Keeping the model and the code together

A data model is not a one-time artifact; it evolves with the application, and the discipline that keeps it healthy is keeping the model and the code in step. The strongest practice is to treat the schema definition as the source of truth and generate the diagram from it - from a Prisma schema, SQL DDL, or DBML - so the picture always matches reality. When the model changes, the diagram regenerates, and reviewers see the structural change alongside the code, as described in the guide on generating diagrams from code at /guides/how-to-generate-diagrams-from-code.

Around that generated core, use the editor at /diagrams to maintain readable, curated views - an overview of the whole model and detailed diagrams per subsystem - that a raw generator cannot produce. This gives you both accuracy and comprehension. To go deeper, the database design guide at /guides/database-design covers schema design end to end, the normalization guide explains the normal forms with diagrams, and the ER notation guide details the crow's foot symbols your diagrams use. Data modeling rewards practice, and a good diagram is the fastest way to see whether a model is sound.