ERD → Tables

Where Most People Mess Up

In this session we will take one small ERD (students and courses), turn it into concrete tables, and pick safe keys step by step.

ERD

Conceptual model

Ideas on paper

Tables

Physical implementation

Reality in code

Three Levels of Database Thinking

Week 1 lived in ideas. Week 2 lives in structure. In this session we only walk from ERD (conceptual) to tables (logical).

01

Conceptual

ERD — entities, relationships, attributes (ideas)

02

Logical

Tables, keys, columns, data types (structure)

03

Physical

Storage, indexes, partitions, performance (implementation)

Each level answers: What? → How? → Where?

ERD ≠ Tables (Direct Copy Is a Trap)

Most entities become tables... but not always. Relationships sometimes become tables. Example: in an ERD you draw "STUDENT enrolls in COURSE" as one relationship line, but in tables that relationship becomes its own ENROLLMENT table.

THE GAP

📐

ERD Entity

Rectangle on paper

↓

❓

Table?

Not automatic!

📋

Entity → Always Table

🔗

Relationship → Never Table

🎯

It Depends on Cardinality

Correct! Many-to-many relationships become junction tables.

Turning Entities Into Tables

Disciplined translation. Every element has a destination. We will keep one concrete example in mind: a single STUDENT box in the ERD becoming a real STUDENT table.

Mapping Rules

Strong Entity (Student)

→

Table: STUDENT

Attribute (name, email)

→

Columns: name, email

Primary Key (student_id)

→

PRIMARY KEY (student_id)

Weak Entity / Relationship

→

May become separate table

Golden Rule: Be slow. Be deliberate. Every table must earn its existence.

                ERD box: STUDENT (student_id, name, email)

                becomes table:

                CREATE TABLE STUDENT (

                  student_id INT PRIMARY KEY,

                  name TEXT,

                  email TEXT

                );

Stop Confusing Unique With Identity

The most expensive mistake in database design.

🔍

UNIQUE

Can distinguish rows
but may change

email
phone_number
passport_no (expires)

VS

🧬

IDENTITY

Never changes
has no meaning

student_id
UUID
Auto-increment

🎯

Primary Key

Identity. Immutable. Meaningless.

⭐

Candidate Key

Could be PK, but we chose one

🔒

Alternate Key

Unique constraint, not identity

The Immutable Law

"A key that can change
is not a key."

👤

Names Change

Marriage, correction, translation

📧

Emails Change

Providers, jobs, preferences

🏠

Addresses Change

Moving, restructuring

But student_id = S104729 is forever.

Design Challenge

Given this ERD fragment, what becomes a table?

STUDENT

student_id
name
email

enrolls
N:M

COURSE

course_id
title
credits

📋

STUDENT

📚

COURSE

🔗

ENROLLMENT

Junction Table!

❌

Nothing Else

Click to select. The N:M relationship becomes its own table.

Translation Checklist

✓

Entity Check

Is this a strong entity or a relationship in disguise?

✓

Key Check

Does my PK never change and have no business meaning?

✓

Attribute Check

Are these atomic? No repeating groups?

✓

Relationship Check

Do I need a junction table for N:M?