An ERD, or entity relationship diagram, is a graphical representation of how tables in a database connect to each other. ERDs show a database’s structure (or schema) at a high-level. ERDs are a useful tool when designing a new data model or identifying issues within an existing schema.

• Entity relationship diagrams are basically just boxes (your entities, or tables) connected with lines (the relationships between them).
• Your database software may have some built-in functionality to create ERDs, but you can also use whatever design software you like best or go the analog route and draw out your ERD on a piece of paper.
• The how is less important; what really matters is making sure that your diagram is accurate and logical, so you can design the most effective database for your specific use case.
• The four tables, Orders, Products, People, and Reviews, are our entities, and the connecting lines show the three one-to-many relationships between them.

ERD design and notation

1. When sketching out an entity relationship diagram, each box should contain information like that table’s name, field, and key information (primary and foreign keys).
2. You’ll notice in the example above that each table’s key information is indicated with (PK) and (FK) next to field names.

The type of line between each entity illustrates the kind of relationship each table has to another. Different organizations and industries use different conventions for ERD notation, but one of the most common is crow’s foot notation, named because the three-pronged symbol (the one that gets used for “to many”) looks a bit like the foot of a bird.

Figure 2 shows the common symbols used in crow’s foot notation and their corresponding relationship types:

Here’s an explanation of each type of crow’s foot notation and when to use it:

1. To One (|)
• Notation: A straight line ending with one short perpendicular line (|).
• Usage: Used when there is exactly one relationship between entities. For example, in a one-to-one relationship, where each record in one table is associated with only one record in another table (e.g., each user has only one profile).
• Entities: Employees and Departments
• Relationship: Each employee belongs to one department.
💡

Employees ──|──── Departments

• Explanation: This represents a many-to-one relationship where each employee is linked to only one department.

Tables: Employees and Departments

• Explanation: The Employees table has a many-to-one relationship with the Departments table. This means that each employee belongs to only one department, but each department can have multiple employees. In this setup, the department_id in the Employees table acts as a foreign key linking each employee to a specific department. This structure ensures that every employee has a single department association, while allowing departments to manage numerous employees.

2. To Many (<)
• Notation: A line ending with a “crow’s foot” (three prongs).
• Usage: Indicates a many-to-many relationship. It’s used when multiple records in one table relate to multiple records in another table (e.g., students can enroll in multiple courses, and each course can have multiple students). This relationship often requires a junction table.
• Entities: Products and Categories
• Relationship: Products and categories have a many-to-many relationship through a junction table ProductCategories.
💡

Products ───< ProductCategories >───> Categories

• Explanation: Each product can belong to multiple categories, and each category can include multiple products. This is achieved through the ProductCategories junction table.
• Tables: Products and Categories, linked through ProductCategories
• Explanation: The Products and Categories tables have a many-to-many relationship managed through the ProductCategories junction table. In this case, each product can be assigned to multiple categories (e.g., a product might fit under “Electronics” and “Home Appliances”), and each category can contain multiple products. The ProductCategories table holds foreign keys from both Products and Categories, enabling flexible product categorization across the database.

3. To One and Only One (||)
• Notation: Two parallel vertical lines at the end of the line (||).
• Usage: Used to represent a mandatory one-to-one relationship, where each record in one table must correspond to exactly one record in another table. This ensures that each entity on both sides of the relationship has a match.
• Entities: Users and Profiles
• Relationship: Each user has exactly one profile, and each profile belongs to exactly one user.
💡

Users ───||─── Profiles

• Explanation: This one-to-one mandatory relationship ensures that each user has a unique profile and vice versa.
• Tables: Users and Profiles
• Explanation: The Users and Profiles tables exhibit a one-to-one mandatory relationship. Each user must have a unique profile, and each profile corresponds to exactly one user. In this schema, the user_id in the Profiles table is unique and corresponds to a user_id in the Users table, ensuring that every user is linked to a single, distinct profile, and no user exists without one.
4. To One or Many (|<)
• Notation: A vertical line and a crow’s foot (|<).
• Usage: Used in one-to-many relationships where one record in the primary table relates to one or many records in the associated table (e.g., a single department can have many employees, but each employee belongs to only one department).
• Entities: Projects and Tasks
• Relationship: Each project can have one or more tasks.
💡

Projects ───|<─── Tasks

• Explanation: This one-to-many relationship shows that each project can have multiple tasks, but each task belongs to only one project.
• Tables: Projects and Tasks
• Explanation: This one-to-many relationship between the Projects and Tasks tables allows each project to include multiple tasks, but each task is associated with only one project. The project_id in the Tasks table is a foreign key that connects each task to a specific project, facilitating organized task tracking within projects while allowing projects to manage multiple related tasks.
5. To One or Zero (|O)
• Notation: A vertical line followed by a circle (|O).
• Usage: Used for an optional one-to-one relationship. It indicates that a record in one table may have a related record in another table, but it’s not required. For example, an employee may or may not have a company car.
• Entities: Orders and ShippingDetails
• Relationship: Each order may optionally have a shipping detail, but each shipping detail is tied to only one order.
💡

Orders ───|O─── ShippingDetails

• Explanation: This optional one-to-one relationship allows an order to exist without a shipping detail, but if a shipping detail exists, it links to only one order.
• Tables: Orders and ShippingDetails
• Explanation: The relationship between Orders and ShippingDetails is an optional one-to-one relationship. Each order may or may not have a corresponding shipping detail, but any shipping detail in the ShippingDetails table is linked to only one order. This is useful in cases where not all orders require shipping, allowing flexibility by not mandating that every order has associated shipping details.

6. To Zero or Many (O<)
• Notation: A circle followed by a crow’s foot (O<).
• Usage: Represents an optional many-to-many relationship, where records in one table may have multiple matching records in another table, but having a relationship isn’t mandatory. For example, a project may have many tasks, and a task may be related to multiple projects, but not all projects have tasks.
• Entities: Customers and CustomerOrders
• Relationship: A customer may have zero or many orders.
💡

Customers ───O<─── CustomerOrders

• Explanation: This optional one-to-many relationship shows that a customer can have multiple orders, but it’s also possible for a customer to have no orders.
• Tables: Customers and CustomerOrders
• Explanation: The Customers and CustomerOrders tables demonstrate a one-to-many optional relationship, where a customer may place zero or multiple orders. The customer_id in the CustomerOrders table references the Customers table, allowing each customer to have any number of orders or no orders at all, giving flexibility in representing customer order history within the database.