Entity Relationship Diagram for Database Design

What this diagram shows

An entity relationship diagram (ERD) models the structure of a database by showing entities as rectangular boxes, their attributes as labelled fields, and relationships between entities using connecting lines annotated with cardinality notation. The diagram flows from logical business concepts to their database representation, clarifying what data must be stored, how records relate to one another, and the rules governing those relationships before implementation. It serves as a blueprint that bridges business requirements and physical database schema.

Key components

• Entity — A distinct object or concept in the domain (such as Customer, Order, or Product) that becomes a table in the database.
• Attribute — A property or characteristic of an entity (such as email, phone_number, or creation_date) that becomes a column in the table.
• Primary Key — A unique identifier attribute or group of attributes that distinguishes each record within an entity.
• Relationship — A line connecting two entities that describes how records in one table relate to records in another table.
• Cardinality Notation — Symbols at each end of a relationship line (1:1, 1:N, M:N) indicating how many records can participate on each side.
• Foreign Key — An attribute in one entity that references the primary key of another entity, enforcing the relationship constraint.

When to use it

Use an ER diagram when designing a relational database schema, planning a new application data layer, documenting existing database structure for team communication, or reviewing data dependencies before implementation. It is essential for systems with multiple interconnected tables where relationships matter (e-commerce platforms, content management systems, financial applications). ERDs help stakeholders visualise data structure without needing SQL knowledge, making them valuable in requirements and design phases.

Common mistakes

• Drawing a relationship without specifying cardinality, leaving ambiguity about whether a customer can have zero, one, or many orders.
• Including implementation details such as indexes, column constraints, or data types that belong in a schema diagram rather than a logical ERD, cluttering the model with technical noise.
• Creating cycles or redundant relationships that violate normalisation principles, such as storing customer_id in both Order and Invoice when Invoice already links through Order.

Adapting it to your system

Start by identifying core entities relevant to your domain (User, Transaction, Report). List the key attributes each needs (email, amount, date_created). Then map relationships between entities based on how data flows in your system (does one User create many Transactions?). Use standard cardinality notation: 1:1 for one-to-one (Person to Passport), 1:N for one-to-many (Customer to Order), and M:N for many-to-many (Student to Course). Check for normalisation issues, ensuring each attribute belongs logically to its entity and no data is duplicated across entities unnecessarily.