Entity Relationship Diagram

What this diagram shows

An Entity Relationship Diagram (ERD) represents the structure of a database by showing entities as rectangular boxes, their attributes as property lists, and the relationships between entities as connecting lines with cardinality notation. The flow demonstrates how data tables connect through foreign keys and primary keys, clarifying which entities reference which others and whether those relationships are one-to-one, one-to-many, or many-to-many. This gives you a complete logical view of your data model before or after building the actual database schema.

Key components

• Entity — A rectangular box representing a table or data object (such as Customer, Product, or Order) that stores a collection of related records.
• Attribute — A property or column name listed inside an entity box (such as customer_id, email, or creation_date) that describes what data the entity holds.
• Primary Key — An attribute marked with (PK) that uniquely identifies each record in an entity and serves as the lookup reference for relationships.
• Foreign Key — An attribute marked with (FK) in one entity that points to the primary key of another entity, establishing the connection between tables.
• Relationship Line — A connecting line between two entities showing how they relate, typically annotated with crow's foot notation or cardinality symbols (1, N, or M).
• Cardinality Notation — Symbols at the ends of relationship lines that indicate whether a relationship is one-to-one (1:1), one-to-many (1:N), or many-to-many (N:M).

When to use it

Use an ERD when designing a new relational database, documenting an existing schema for a team, planning data migrations, or validating that your table structure avoids redundancy and supports all required queries. It is essential before any SQL schema creation, useful for communicating data architecture to non-technical stakeholders, and invaluable when multiple developers need to understand how entities interlock. ERDs are also the standard tool for identifying normalisation problems and ensuring referential integrity.

Common mistakes

• Adding too many attributes to entities without distinguishing between core data and derived or calculated fields, making the diagram unreadable and obscuring the core relationships.
• Omitting many-to-many junction tables or bridge entities that resolve complex relationships, leading to incorrect cardinality notation and misrepresenting actual database structure.
• Failing to mark primary keys and foreign keys clearly, making it impossible for developers to understand which attributes enforce relationships or uniqueness constraints.

Adapting it to your system

Start by listing all the main entities your system needs (User, Product, Order, Payment, etc.). For each, write down its primary key and essential attributes. Then map the relationships: which entity refers to which, and is it one-to-many (a customer has many orders) or many-to-many (a product appears in many orders, and an order contains many products)? Junction tables are needed for many-to-many links. Use consistent naming (snake_case for attributes, singular for entity names) and mark every foreign key reference. Draw or generate the ERD, then validate it against your actual use cases to ensure no missing entities or incorrect cardinality.