ER Diagram (Entity-Relationship Diagram)

In a Nutshell

An ER diagram models your persistent data — the tables (entities), their columns (attributes), and how they relate to each other (relationships with cardinality). It's the bridge between your domain understanding and your database schema. While a class diagram shows how code is structured, an ER diagram shows how data is stored. If the interviewer asks you to "design the data model," this is the diagram they want — and getting it right means your queries will be efficient and your schema won't need a painful migration six months later.

How It Actually Works

Notation (Crow's Foot — the industry standard)

Symbol	Meaning
──┤├── (two vertical lines)	One and only one (mandatory)
──○├── (circle + line)	Zero or one (optional)
──┤<── (fork/crow's foot)	One or more
──○<── (circle + fork)	Zero or more

Cardinality Patterns

Relationship	Meaning	Example	Implementation
One-to-One	Each A maps to exactly one B	User ↔ UserProfile	FK in either table, or same table
One-to-Many	Each A maps to many Bs	User → Orders	FK on the "many" side (orders.user_id)
Many-to-Many	Each A maps to many Bs and vice versa	Students ↔ Courses	Junction table (student_courses)

How to Design an ER Diagram

1. Identify entities — The nouns: User, Order, Product, Payment, Review
2. List attributes — For each entity, what data do we store? Mark the primary key (PK).
3. Define relationships — How do entities relate? What's the cardinality?
4. Determine keys — Primary keys (PK), foreign keys (FK), unique constraints
5. Add indexes — Which columns will be queried frequently? Those need indexes.
6. Normalize (then selectively denormalize) — Start in 3NF, then denormalize where read performance demands it

Normalization Quick Reference

Normal Form	Rule	Violation Example	Fix
1NF	No repeating groups, atomic values	phone_numbers: "123,456,789"	Separate table for phone numbers
2NF	No partial dependency (non-key depends on part of composite key)	In order_items(order_id, product_id, product_name), product_name depends only on product_id	Move product_name to products table
3NF	No transitive dependency (non-key depends on another non-key)	orders(order_id, customer_id, customer_name) — customer_name depends on customer_id, not order_id	Move customer_name to customers table

When to Denormalize

Normalization reduces redundancy but increases joins. Denormalize when:

• Read performance is critical and joins are expensive
• The data is read-heavy (100:1 read/write ratio)
• The denormalized data rarely changes (e.g., product name at time of order)

Common denormalization: store customer_name on the order record so you don't need a join to display order history.

Seeing It in Action

Scenario: ER diagram for a social media platform

┌──────────────────┐         ┌──────────────────┐
│      USERS        │         │      POSTS        │
├──────────────────┤         ├──────────────────┤
│ PK  user_id      │─┐       │ PK  post_id      │
│     username      │ │  1:N  │ FK  user_id      │──┐
│     email         │─┼──────▶│     content       │  │
│     display_name  │ │       │     media_url     │  │
│     bio           │ │       │     created_at    │  │
│     created_at    │ │       │     like_count    │  │ (denormalized)
└──────────────────┘ │       └──────────────────┘  │
                      │                              │
                      │       ┌──────────────────┐  │
                      │       │    COMMENTS       │  │
                      │       ├──────────────────┤  │
                      │  1:N  │ PK  comment_id   │  │
                      ├──────▶│ FK  post_id      │◀─┘ 1:N
                      │       │ FK  user_id      │
                      │       │     content       │
                      │       │     created_at    │
                      │       └──────────────────┘
                      │
                      │       ┌──────────────────┐
                      │       │    FOLLOWS        │  (junction table)
                      │       ├──────────────────┤
                      │  M:N  │ FK  follower_id  │──▶ USERS
                      └──────▶│ FK  following_id │──▶ USERS
                              │     created_at    │
                              │ PK (follower_id,  │
                              │     following_id) │
                              └──────────────────┘

                              ┌──────────────────┐
                              │      LIKES        │  (junction table)
                              ├──────────────────┤
                              │ FK  user_id      │──▶ USERS
                              │ FK  post_id      │──▶ POSTS
                              │     created_at    │
                              │ PK (user_id,      │
                              │     post_id)      │  (prevents double-like)
                              └──────────────────┘

Design decisions visible:

• FOLLOWS is a many-to-many self-referencing relationship (users follow users) — implemented as a junction table
• LIKES uses a composite primary key (user_id, post_id) — this prevents a user from liking the same post twice at the database level
• like_count on POSTS is denormalized — avoids a COUNT(*) on the LIKES table for every post render (huge read optimization)
• Indexes needed: posts.user_id (fetch user's posts), comments.post_id (fetch comments for a post), follows.follower_id (fetch who I follow), follows.following_id (fetch my followers)

SQL for the key query — "get my feed":

SELECT p.*
FROM posts p
JOIN follows f ON f.following_id = p.user_id
WHERE f.follower_id = :current_user_id
ORDER BY p.created_at DESC
LIMIT 20;

Interview Questions

1. Q: Design an ER diagram for an e-commerce platform. What are the core entities? Hint: Users, Products, Categories (M:N with Products via junction table), Orders, OrderItems (junction between Orders and Products with quantity and price_at_purchase), Payments, Addresses (1:N from Users), Reviews (FK to both User and Product).

2. Q: How would you model a many-to-many relationship? When would you add attributes to the junction table? Hint: Junction table with FKs to both sides. Add attributes when the relationship itself has data: enrollments(student_id, course_id, enrollment_date, grade) — grade belongs to the relationship, not to either entity.

3. Q: When would you choose to denormalize your schema? What are the trade-offs? Hint: Denormalize when reads dominate writes, joins are expensive at scale, and the denormalized data changes infrequently. Trade-off: faster reads, but writes must update multiple places (data inconsistency risk) and storage increases. Example: storing product_name on order_items so you don't join Products for order history.

4. Q: What's the difference between a class diagram and an ER diagram? Hint: Class diagram: code structure (objects, methods, inheritance, composition). ER diagram: data structure (tables, columns, FK relationships, cardinality). A class might have methods and inheritance (not in ER). A table might have indexes and constraints (not in class diagram). They're complementary views.

5. Q: Your social media app needs to support both "followers" and "close friends" lists. How would you modify the ER diagram? Hint: Add a relationship_type column to FOLLOWS (FOLLOW, CLOSE_FRIEND) or create a separate close_friends(user_id, friend_id) junction table. The first approach is simpler but makes the FOLLOWS table do double duty. The second is cleaner but requires checking two tables for visibility rules.

References

• Crow's Foot Notation — Lucidchart — visual guide to ER notation
• Designing Data-Intensive Applications by Martin Kleppmann — Chapter 2 on data models and query languages
• Database Design for Mere Mortals by Michael Hernandez — accessible guide to normalization and ER modeling

Dive Deeper

• Use The Index, Luke — deep dive into indexing and how schema design affects query performance
• SQL Antipatterns by Bill Karwin — common ER design mistakes and how to avoid them
• dbdiagram.io — free tool for drawing ER diagrams with code-like syntax