Anyone have an ERD symbols quick reference?

ERD Symbols Quick Reference: A Guide to Database Notation

Demystify Entity-Relationship Diagram (ERD) symbols with this comprehensive quick reference. Understand the common notations used in database design to effectively model data structures.

Entity-Relationship Diagrams (ERDs) are fundamental tools in database design, providing a visual representation of the relationships between entities within a system. Understanding the various symbols and notations used in ERDs is crucial for anyone involved in data modeling, from database administrators to software developers. This article serves as a quick reference guide to the most common ERD symbols, helping you interpret existing diagrams and create your own with clarity and precision.

Core Components of an ERD

Every ERD is built upon three core components: Entities, Attributes, and Relationships. Each of these has specific symbols to represent them, though the exact appearance can vary slightly depending on the notation style (e.g., Chen, Crow's Foot, UML).

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ LINE_ITEM : contains
    PRODUCT }o--|| LINE_ITEM : includes
    CUSTOMER { 
        VARCHAR(50) customer_id PK
        VARCHAR(100) name
        VARCHAR(255) email
    }
    ORDER { 
        VARCHAR(50) order_id PK
        VARCHAR(50) customer_id FK
        DATE order_date
        DECIMAL(10,2) total_amount
    }
    LINE_ITEM { 
        VARCHAR(50) line_item_id PK
        VARCHAR(50) order_id FK
        VARCHAR(50) product_id FK
        INT quantity
        DECIMAL(10,2) price
    }
    PRODUCT { 
        VARCHAR(50) product_id PK
        VARCHAR(255) product_name
        DECIMAL(10,2) unit_price
    }

Example of an ER Diagram using Mermaid's ER notation.

Entities: The Nouns of Your Database

An entity represents a real-world object or concept that can be distinctly identified and has data stored about it. In an ERD, entities are typically represented by rectangles. Each entity corresponds to a table in a relational database.

Attributes: Describing Your Entities

Attributes are the characteristics or properties of an entity. They describe the data points stored for each instance of an entity. Attributes are often represented as ovals connected to their respective entities. Key attributes, which uniquely identify an entity instance (primary keys), are typically underlined.

erDiagram
    CUSTOMER {
        VARCHAR(50) customer_id PK
        VARCHAR(100) first_name
        VARCHAR(100) last_name
        VARCHAR(255) email
        DATE date_of_birth
    }

Entity 'CUSTOMER' with its attributes, including a primary key.

Relationships: Connecting Entities

Relationships define how entities interact with each other. They are represented by lines connecting entities, often with symbols at each end indicating the cardinality (the number of instances of one entity that can be associated with instances of another entity). Common cardinality notations include:

• One-to-One (1:1): Each instance of Entity A is associated with exactly one instance of Entity B, and vice-versa.
• One-to-Many (1:N): Each instance of Entity A can be associated with one or more instances of Entity B, but each instance of Entity B is associated with only one instance of Entity A.
• Many-to-Many (N:M): Each instance of Entity A can be associated with one or more instances of Entity B, and vice-versa.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ LINE_ITEM : contains
    PRODUCT }o--|| LINE_ITEM : includes
    EMPLOYEE ||--o| DEPARTMENT : works_in
    STUDENT }o--o{ COURSE : enrolls_in

Various relationship types (one-to-many, many-to-many) using Crow's Foot notation.