Design Better Databases: Learn ER Diagrams the Right Way

Table of contents

Overview

Hello Designers!

Writing this blog to share my experience and steps I follow to design DB.

What is Design? Blueprint of database system.

What is Schema? Structure of a Database.

It contains:

• tables (entities)
• columns
• data type
• primary key
• foreign key
• indexes

How to find cardinality of a relation?

Cardinality simply means:

How many records of one table are connected to how many records of another table?

Think like this:

• One → Only one
• Many → More than one

There are four type of cardinalities:

1. One to One
2. One to Many
3. Many to One
4. Many to Many

One to One (1:1)

A ----- B

One record of A is connected to one record of B
AND
One record of B is connected to one record of A

Example

Husband ----- Wife

Let’s take example of Husband - Wife.

One husband can have how many wife? One
One wife can have how many husband? One

Husband & Wife has One to One(1:1) cardinality.

One to Many (1:M)

A ----- B

One record of A connects to many record of B
AND
But each record of B connects to only one record of A

Example

BookMyShow
Ticket ----- Seat

One ticket can have how many seats? Multiple
But one seats can have how many tickets? Only one

Ticket & Seat is having relation of One to Many (1:M)

Many to One (M:1)

A ----- B

One record of A connects to one record of B
AND
One record of B connects to many records of A

Example

Let's say Hitesh sir decide to assign one mentor to each student
Student ----- Mentor

One student can have how many mentor? One
But one mentor belongs to how many students? Multiple
(Anirudh can be mentor of Ankit, Rachit & Vinny, because of limited mentors)

Student & Mentor is having cardinality of Many to One (M:1)

This is just reverse of One to Many. Both are same relation, just different perspective.

Many to Many (M:M)

A ----- B

Many records of A connect to many records of B
AND
Many records of B connect to many records of A

Example

Student ----- Cohort

One student can be part of how many cohorts? More than one
(I can be part of web dev as well as system design cohort)

But one cohort can have how many students? Many
(there are multiple students part of single cohort)

Student & Cohort is having cardinality of Many to Many (M:M)

How to represent different cardinality?

One to One

Both are having one to one relation with each other. Adding reference(id) of one entity into other entity should work.

Husband table can have wife_id
OR
Wife table can have husband_id

One to Many | Many to One

Let’s take same example of Student & Mentor

Students Table:

Mentors Table:

Sagar & Naman can have Anirudh as his mentor.
John can have Shubham as his mentor.
Aman can have Akash as his mentor.

❌ Wrong approach
If we put student_id column to Mentors table.
Mentor Table:

Problem:

• Storing list in one column is bad practice
• Hard to query
• Poor performance

Example: Let’s say in a live cohort, Anirudh is assigned 25 students. If all 25 student IDs are stored in one cell, it creates a problem.

Now, to find which mentor Sagar is assigned to, you would have to scan every row and process that cell for given column one by one.

You cannot query it directly. This is not a good database design practice.

✅ Correct approach:
Let’s put mentor_id to Students table.
Students Table:

Rule: Always add foreign key on the side which has Many

Many to Many

Let’s take an example of Student and Cohort. Students Table:

Cohorts Table:

❌ Wrong approaches
Let’s put each student’s cohort in Students table.
Students Table

If we put each cohort’s students in Cohorts table.
Cohorts Table

We already saw this is not good practice.

✅ Correct approach
We can create seperate mapping table for this relations.
student_cohorts table:

Now:

• Easy to query
• Scalable
• Clean design

Here, we can easily fetch which student is part of which cohort and which cohort has which students by join two tables.

Steps to Design Database Schema

Let’s take an example of “Online Book Store System”

Step 1: Understand System Deeply

First ask questions,

• what problem are we solving?
• what all operations will happen?

Examples:

• Users can register
• Users can buy books
• Books have authors
• One book can have multiple authors
• One order can have multiple books
• User will do online payment for order
• Order will be shipped to user’s address

Don’t directly skip to the entities

Step 2: Identify all Candidates for Entities

Extract nouns from the definition and above examples. But filter smartly.

Raw Nouns: User, Book, Author, Order, Payment, Address

Final Entities:

• User
• Book
• Author
• Order
• OrderItem (as one order can have multiple books)
• Payment
• Address

Not all noun becomes an entity (e.g. name, price are attributes, not entities)

Step 3: Define Attributes for Each Entity

users:
id PK
name
email

books:
id PK
title
price

authors:
id PK
name

orders:
id PK
user_id FK
created_at

address:
id PK
address1
address2
city
state
pincode
country

Step 4: Identify Relationships & Cardinalities

Ask:

• One to One?
• One to Many?
• Many to Many?

Example: User → Order = 1:M
Order → Book = M:M
Book → Author = M:M

Step 5: Resolve Many to Many Relations

Create mapping tables

Example: Order - Book

OrderItem
id PK
order_id FK
book_id FK
quantity

Book - Author

BookAuthor
id PK
book_id PK
author_id FK

Most real world complexity comes from M:M relations

Step 6: Define Primary Keys & Foreign Keys

Formalize Structure:

• Every table → Primary Key
• Every relationships → Foreing Keys

Example:

• Order.user_id → user.id
• OrderItem.order_id → Order.id
• OrderItem.book_id → Book.id

Step 7: Add Data Types, Constraints & Rules

• Where to add NOT NULL?
• Where to add UNIQUE?
• Where to add DEFAULT values?

Example: email → UNIQUE
quantity → NOT NULL
price → NOT NULL

Step 8: Handle Special Cases (advanced)

Ask:

• Do we need soft delete? (deleted_at)
• Audit fields (created_at, updated_at)

Flow of Designing ER Diagram

Happy DB Design :)

Connect with me on X or LinkedIn, if you have any feedback or suggestion.