Database Schema Diagrams: Design Clear ERDs for Relational Applications
ERDdatabase designrelational databasesschema modelingSQL

Database Schema Diagrams: Design Clear ERDs for Relational Applications

DDiagrams.site Editorial Team
2026-11-16
7 min read

Learn how to create a clear ERD for relational applications, with checklists for keys, relationships, cardinality, junction tables, and schema reviews.

A clear database schema diagram turns tables, keys, and relationships into a model that developers, reviewers, and product teams can inspect together. This guide explains how to make an ERD for a relational database, from identifying entities and cardinality to comparing the conceptual design with production tables. Use the checklists before designing a feature, reviewing a migration, investigating duplication, or documenting an API.

Overview

An entity relationship diagram (ERD) is a visual model of a relational database. It shows the entities represented by tables, the attributes stored in them, and the relationships that connect them. An ERD can describe a proposed design, a current production schema, or the difference between the two.

A useful database schema diagram should answer five questions quickly:

  • What are the main entities in the domain?
  • Which attributes belong to each entity?
  • What uniquely identifies each record?
  • How do records relate, and is each relationship required?
  • Which business rules are enforced by the database rather than only by application code?

For software design, begin with a conceptual ERD that reflects the domain rather than immediately copying every column from an existing database. Then create a logical or physical version that includes data types, indexes, constraints, naming conventions, and implementation details. Keeping these views distinct prevents the diagram from becoming either too abstract to review or too crowded to use.

Core ERD notation

ElementMeaningExample
EntityA person, object, or concept represented by a tableCustomer, Order, Invoice
AttributeA property stored on an entityemail, created_at, status
Primary keyThe column or column set that uniquely identifies a roworders.id
Foreign keyA reference from one table to a key in another tableorders.customer_id
One-to-manyOne parent can be related to multiple child recordsCustomer to Order
Many-to-manyRecords on both sides can have multiple related recordsProduct to Collection
Optional relationshipA related record may be absentOrder may have no shipment yet

Notation varies between tools. Crow's foot notation is common for showing cardinality, while UML-style diagrams use multiplicities such as 1, 0..1, and 0..*. Choose one notation, include a small legend, and use it consistently.

Checklist by scenario

When designing a new relational schema

  1. Define the business scope. Write down the workflow or capability the schema supports. A schema for checkout, for example, may need customers, carts, orders, payments, and shipments, but not every concept in the wider application.
  2. List the entities. Identify nouns that have their own identity, lifecycle, or collection of records. Avoid treating every noun as a table; some are values, statuses, or attributes.
  3. Assign attributes. Record the fields needed to describe each entity. For every attribute, ask whether it is atomic, whether it can be null, and whether it belongs in this table or another entity.
  4. Choose primary keys. Mark a primary key for every entity. Document whether the key is generated, natural, composite, or supplied by another system. Make the choice explicit even in a conceptual ERD.
  5. Map relationships. Connect entities and label each relationship in both directions. For example, a customer places orders, and an order belongs to one customer.
  6. Set cardinality and optionality. Distinguish one-to-one, one-to-many, and many-to-many relationships. State whether zero related records are allowed, not just whether one or more are allowed.
  7. Resolve many-to-many relationships. Introduce a junction table such as order_items, product_categories, or user_roles. Give it foreign keys to both parent tables and decide whether the pair must be unique.
  8. Add constraints. Identify uniqueness rules, required fields, check constraints, cascading behavior, and foreign-key actions. These are part of the design, not merely implementation cleanup.

Commerce ERD example

A basic commerce model might include customers, orders, products, and order_items. A customer can place many orders, so orders.customer_id references customers.id. An order contains many products, and a product can appear in many orders; order_items resolves this many-to-many relationship.

The junction table normally carries more than two foreign keys. It may include quantity, unit_price, discount_amount, and a snapshot of the product description used at the time of purchase. This is an important modeling distinction: current product data and historical order data often have different ownership and change requirements.

SaaS ERD example

A multi-tenant SaaS model may include organizations, users, memberships, projects, and audit_events. Memberships can connect users to organizations while storing role, invitation status, and joined_at. Projects can belong to an organization, while audit events may reference the actor, organization, target type, and target identifier.

For this type of schema, make tenant boundaries visible. Ask whether every tenant-owned table has an organization_id directly, whether access checks can be enforced consistently, and whether unique constraints should be scoped to an organization. A diagram cannot prove authorization is correct, but it can reveal where the data model makes authorization difficult.

When documenting an existing production database

  1. Generate or inspect the current table and constraint inventory.
  2. Group tables by bounded context or business capability rather than placing everything on one canvas.
  3. Separate active relationships enforced by foreign keys from logical relationships enforced only in application code.
  4. Mark legacy columns, denormalized fields, soft-delete flags, and deprecated tables.
  5. Compare the production schema with migrations and record any unexplained drift.
  6. Link important entities to API resources, ownership documentation, or architecture decisions.

For a broader system view, pair the ERD with an application data flow diagram. The ERD explains storage relationships; the data flow view explains how services, users, and third parties move that data.

What to double-check

Keys and identity

Every table should have an unambiguous identity. Check whether a supposedly unique business value can change, whether a composite key reflects the real rule, and whether external identifiers need their own unique constraint. Do not assume that a primary key alone prevents duplicate business records.

Cardinality and optionality

Read each connector as a sentence. “An order must belong to exactly one customer” is different from “an order may belong to one customer.” Confirm both ends of the relationship and verify that the database constraints match the intended rule. Nullable foreign keys, unique indexes, and join-table design often determine whether the diagram is accurate.

Normalization and intentional duplication

Look for repeated facts such as customer addresses, product names, or status labels stored in several places. Decide whether the repetition is accidental or intentional. Snapshot fields can be appropriate when historical accuracy matters, but label them so future maintainers do not mistake them for the current source of truth.

Audit and lifecycle fields

Decide which entities need created_at, updated_at, deleted_at, version, or created_by fields. Audit fields should support a real operational need and use consistent names and time handling. For soft deletion, document whether relationships should ignore deleted records, preserve them, or cascade a logical deletion.

Schema-to-documentation alignment

Compare entity names and fields with API resources and application terminology. If an API calls something a workspace while the database calls it an organization, document the mapping. When diagrams are stored with code or documentation, review them alongside migrations. A version-control workflow for diagrams can make schema-related visual changes easier to review with code changes.

Reusable comparison template

AreaConceptual modelProduction schemaAction
Entity nameBusiness termTable nameDocument mapping or rename
IdentityExpected identifierPrimary key and typeConfirm stability and uniqueness
RelationshipBusiness cardinalityForeign key or application logicAdd, clarify, or test enforcement
Required fieldMust exist by domain rulesNullable or non-null columnAlign constraint and validation
LifecycleExpected statesStatus, timestamps, deletion behaviorDocument transitions and retention

Common mistakes

  • Diagramming every column at once. Start with entities, keys, and relationships. Add operational fields in a physical view or a detail layer.
  • Leaving cardinality implied. A line between two tables does not explain whether the relationship is required, optional, one-to-one, or one-to-many.
  • Using a direct many-to-many line in a physical schema. Relational implementations generally need a junction table, especially when the relationship has attributes.
  • Confusing a reference with ownership. A foreign key shows a connection, but it does not automatically describe lifecycle, deletion, or permission behavior.
  • Ignoring historical data. Reusing current profile or product fields for past transactions can make records change meaning over time.
  • Mixing conceptual and physical detail. A business review needs readable concepts; a migration review needs types, indexes, constraints, and naming detail. Use separate views when necessary.
  • Treating the ERD as permanent. A diagram that is not updated after migrations becomes misleading documentation. Assign an owner or include diagram updates in the schema-change workflow.

When to revisit

Review the database schema diagram whenever the underlying model changes, not only during a major redesign. Practical triggers include a new feature that introduces an entity, a migration that adds or removes a relationship, a change to tenant boundaries, a data duplication incident, an API version change, or a new reporting requirement.

Before seasonal planning cycles or other roadmap reviews, compare the ERD with the current migrations and production inventory. Confirm that key constraints, nullable fields, junction tables, and lifecycle rules still represent the application. When workflows or modeling tools change, check that exported diagrams, notation, links, and documentation embeds remain readable and reproducible.

Use this final review sequence:

  1. Export the current schema or inspect the latest migration state.
  2. Update the conceptual and physical ERD views.
  3. Mark differences between intended relationships and database-enforced relationships.
  4. Ask an application owner and a data owner to review the rules from their perspectives.
  5. Link the approved diagram to the relevant migration, API documentation, or architecture decision.
  6. Record the next review trigger, especially if known legacy or intentionally denormalized areas remain.

An ERD is most valuable when it supports a decision: whether a feature fits the model, whether a migration is safe, or whether an API exposes the right boundaries. Keep the diagram focused on those decisions, and it will remain useful as the relational application evolves.

Related Topics

#ERD#database design#relational databases#schema modeling#SQL
D

Diagrams.site Editorial Team

Technical Documentation Editor

Senior editor and content strategist. Writing about technology, design, and the future of digital media. Follow along for deep dives into the industry's moving parts.