A useful database schema diagram does more than show tables: it explains ownership, relationships, constraints, and the decisions that keep application data reliable. This guide provides a reusable checklist for creating, reviewing, and maintaining an entity relationship diagram (ERD) alongside SQL schemas, migrations, and developer documentation.
Overview
A database schema diagram is a visual representation of a database’s structure. It commonly shows entities or tables, columns, primary keys, foreign keys, and the relationships between tables. An ERD may also communicate cardinality, optionality, indexes, unique constraints, and domain boundaries.
The diagram should answer practical questions quickly:
- Which table owns a piece of data?
- How are records connected?
- Can a record exist without a related record?
- Which fields identify records or prevent duplicates?
- What assumptions should a developer understand before changing the schema?
Use an ERD as a documentation view, not as a substitute for the database definition. The migration files, schema declarations, and production database remain the operational sources of truth. The diagram adds context that is often difficult to obtain by reading SQL alone.
For a small feature, a focused database design diagram may contain only the tables affected by a change. For a broader system, create separate views for the domain model, service ownership, and deployment or integration context. Keeping each view purposeful makes it easier to review and maintain.
Checklist by scenario
When designing a new schema
- List the core entities and give each one a clear, singular responsibility.
- Choose a primary key for every table and document whether it is generated, assigned, or composed.
- Define the columns needed for the current use cases without hiding important domain distinctions in vague fields.
- Mark required fields separately from nullable fields.
- Identify candidate unique keys, such as an account identifier or an external system reference.
- Draw relationships before finalizing table names and foreign keys. The relationships often reveal missing entities or ambiguous ownership.
- Record important constraints, including check constraints, cascade behavior, and allowed status values.
When modeling one-to-one relationships
A one-to-one relationship means one record in table A corresponds to at most one record in table B. Before drawing one, confirm that the relationship is genuinely one-to-one rather than a one-to-many relationship that happens to contain one row today.
- Place the foreign key on the table that depends on the other record.
- Use a unique constraint on that foreign key when the database must prevent multiple related records.
- Clarify whether the related record is mandatory or optional.
- Explain why the data is separated. Common reasons include different access controls, lifecycle rules, or optional profile data.
When modeling one-to-many relationships
One-to-many is the most common relational pattern. A customer may have many orders, while each order belongs to one customer. Place the foreign key on the “many” side and show the direction clearly in the ERD.
- Label the parent and child tables.
- State whether a child must have a parent.
- Decide what happens when the parent is deleted or archived.
- Consider an index on the foreign key when the application frequently filters or joins by it.
- Check whether the relationship should support reassignment or should be immutable after creation.
When modeling many-to-many relationships
Relational databases usually represent many-to-many relationships with a junction, join, or associative table. For example, users and projects can be connected through a user_projects table.
- Give the junction table a clear name that describes both sides of the relationship.
- Use foreign keys to both parent tables.
- Prevent duplicate pairs with a composite unique constraint or composite primary key when appropriate.
- Add attributes to the junction table when the relationship itself has data, such as a role, invitation date, or membership status.
- Show the relationship as two one-to-many relationships in the diagram.
When reviewing a migration or pull request
- Update the relevant ERD view before or during the review, rather than after the change has shipped.
- Compare every new or changed column with the migration: type, nullability, default, and naming should agree.
- Check that foreign keys, indexes, and unique constraints are represented where they affect behavior.
- Review data migration steps separately from structural changes.
- Identify how existing rows will behave when a new required column or constraint is introduced.
- Link the diagram or rendered image from the design document, pull request, or architecture decision record.
Teams that keep diagrams in documentation repositories can use diagram-as-code approaches such as Mermaid, PlantUML, D2, or Graphviz. The right choice depends on the team’s review, rendering, and publishing workflow; compare those approaches in Mermaid vs PlantUML vs D2 vs Graphviz.
What to double-check
Keys and constraints
Do not treat primary and foreign keys as decorative labels. They communicate identity and referential integrity. Verify that the diagram distinguishes a primary key from an ordinary indexed column and that composite keys are shown completely. If a foreign key is nullable, make that visible through notation or a short note.
Unique constraints deserve equal attention. A column such as email may look unique from application behavior, but the database design should show whether uniqueness is enforced, scoped to a tenant, or intentionally not guaranteed.
Indexes and query paths
An ERD does not need to display every implementation detail, but important indexes should be documented when they influence expected access patterns. Note indexes supporting foreign-key lookups, frequent filters, sorting, or multi-column queries. Include the column order for composite indexes because changing that order can change which queries the index supports.
Naming and semantics
Use consistent names for tables, keys, timestamps, and status fields. Decide whether the team uses singular or plural table names, a standard foreign-key format such as customer_id, and a consistent convention for created and updated timestamps. More important than any single convention is applying it consistently and documenting exceptions.
Names should also express meaning. A field called status may be insufficient when an entity has separate lifecycle, payment, and review states. Clear names reduce the amount of explanation required beside the diagram.
Ownership and boundaries
If multiple services or teams access the database, show ownership boundaries. Distinguish tables owned by one service from tables that are read through an API or replicated into another store. Avoid drawing every integration as a direct foreign-key relationship: a data dependency is not necessarily a relational constraint.
For broader context, pair the ERD with an API or software architecture diagram. The guide to C4, containers, and deployment views can help separate database structure from service and infrastructure views.
Common mistakes
- Showing only boxes and lines. A diagram without keys, cardinality, or optionality leaves out the details developers need to make safe changes.
- Putting the entire database on one page. Large diagrams become difficult to read and review. Use a high-level overview plus smaller domain-specific views.
- Confusing an application object with a table. An object model may combine, split, or transform data. Label the diagram as an ERD or domain model so readers understand its purpose.
- Leaving many-to-many relationships implicit. If a join table carries permissions, dates, or state, hiding it obscures important business rules.
- Documenting indexes without query context. An index list is more useful when it explains the access pattern it supports.
- Relying on screenshots as the only source. A static export is useful in a ticket or design document, but the editable or source representation should be versioned where possible.
- Updating the diagram only at project launch. Schema documentation becomes misleading when migrations continue but the visual model does not.
- Including sensitive sample data. Use realistic structure and anonymized examples without copying production values into documentation.
For a broader quality review, use a separate diagram review checklist covering clarity, accuracy, security, and ownership.
When to revisit
Revisit the database schema diagram whenever the underlying inputs change, not only during an annual documentation exercise. The most useful update points are:
- Before a seasonal planning or roadmap cycle, when teams reassess ownership and domain boundaries.
- During schema migrations that add, remove, rename, or split tables and columns.
- When a new service begins reading or writing existing data.
- When query performance work introduces or changes important indexes.
- When retention, archival, soft-delete, or audit requirements change the data lifecycle.
- When a workflow or diagramming tool changes, especially if the rendering process or source format changes.
- After an incident reveals an undocumented dependency or constraint.
At each review, compare the diagram with the current migrations or schema export, confirm table ownership, and remove obsolete elements. A short maintenance note can record the review date, source of truth, and responsible team without turning the ERD into a second issue tracker.
For a practical workflow, store the diagram source near the schema or documentation, render it automatically when the documentation is published, and include diagram changes in schema pull requests. If a visual editor is more suitable for collaborative modeling, export a readable version and document how it is kept synchronized. The goal is not to choose one “best” ERD diagram tool; it is to make the review path visible and repeatable.
Final checklist: before sharing a database design diagram, confirm that every table has a clear purpose, every relationship has correct cardinality, keys and constraints are visible, important indexes are explained, ownership is clear, sensitive data is excluded, and the diagram has a named source of truth. Then link it from the migration, design document, or architecture decision record that explains the change.
