Database Entity Relationship Diagram

What this diagram shows

An entity relationship diagram (ERD) models the structure of a database by showing entities (tables) as boxes and the relationships between them as connecting lines. For an e-commerce platform, it displays how customers, products, orders, and payments relate to one another through foreign keys and cardinality rules (one-to-many, many-to-many). The diagram clarifies which data belongs in which table and how records are linked, forming the foundation for database design before any schema is created.

Key components

• Entity (Table) — Represents a database table such as Customers, Products, or Orders with its attributes listed as columns.
• Primary Key — A unique identifier field (often marked with 'PK') that uniquely identifies each record within an entity.
• Foreign Key — A field (marked 'FK') that references the primary key of another entity to establish relationships.
• Cardinality Notation — Symbols (1:1, 1:N, M:N) on connecting lines that show how many records in one entity can relate to records in another.
• Attribute — A column or field within an entity that stores a specific piece of data, such as email or product_price.
• Relationship Line — A connector between two entities indicating a foreign key constraint and the nature of their association.
• Crow's Foot Notation — A visual notation at the ends of relationship lines showing whether the connection is mandatory or optional and its multiplicity.

When to use it

Use an ERD when designing a new database schema, documenting an existing database structure, planning data migrations, or communicating data architecture to development teams. It is essential before writing CREATE TABLE statements and helps identify missing entities, redundant fields, and normalisation issues. ERDs are also valuable for requirements gathering and ensuring all stakeholders understand how data will be organised.

Common mistakes

• Omitting attributes from entities, leaving the diagram too abstract to serve as a specification for developers building the actual schema.
• Using unclear or inconsistent cardinality symbols, causing ambiguity about whether relationships are one-to-one, one-to-many, or many-to-many.
• Creating overly complex diagrams that show every possible field and relationship at once, making it difficult to follow core data structures and relationships.

Adapting it to your system

Start by listing your main entities (Products, Customers, Orders, Invoices) as separate boxes. Within each box, add critical attributes and mark primary keys. Draw relationship lines between entities and label them with cardinality (1:N, M:N). Remove redundant fields that could be derived from relationships. Test the design by tracing how a customer order would be recorded across multiple tables. Iterate based on query patterns you expect your application to perform frequently.