Database Design Fundamentals

Introduction

What is it? Database design is the work of deciding which tables to create, what each table stores, and how the tables connect, before (and while) you write any queries. A good design stores every fact exactly once and uses keys to connect facts. A bad design stores copies of the same facts in many places.

Why do we need it? Most database chaos does not come from bad SQL. It comes from never pausing to think about how data should be organized. A badly designed table can be queried correctly and still give wrong answers, because the data itself contradicts itself. A well-designed schema rejects bad data automatically, stays correct as it grows, and keeps queries simple.

Real-world analogy Think of a school. The office keeps a student register (one line per student), a course list (one line per course), and an enrollment sheet that only says "student 7 joined course 3". Nobody rewrites the student's full name, address, and the course title on every enrollment line. If a student changes her name, the office fixes one line in the register. A spreadsheet dump, on the other hand, is like a notebook where every event is written out in full, every time: change a name and you must hunt through every page.

Business use case A learning platform sells courses. Students buy courses, instructors teach them, payments are made (and sometimes refunded). The team starts with one big spreadsheet-like table. It works for a week. Then names disagree, prices differ, duplicate payments appear, and nobody is sure which row is right. This chapter follows that exact platform from the messy table to a clean design.

1. Raw Data and Pain

In the early days of a product, data feels harmless. You export everything from a spreadsheet and dump it into a single table. You see rows, you see values, and it looks like everything is working. It even feels productive.

But what you are actually storing is not structure, it is repetition. Not relationships, but copies. Not clarity, but the seeds of future confusion. That is the quiet pain of raw data.

The flat table

When teams build quickly, they often store everything in one flat table:

CREATE TABLE raw_learning_events (
  row_id INT AUTO_INCREMENT PRIMARY KEY,
  student_email VARCHAR(255),
  student_name  VARCHAR(100),
  course_code   VARCHAR(30),
  course_title  VARCHAR(200),
  instructor_email VARCHAR(255),
  instructor_name  VARCHAR(100),
  listed_price DECIMAL(10,2),
  currency     CHAR(3),
  payment_id   VARCHAR(40),
  payment_status VARCHAR(20),  
  paid_at      DATETIME NULL,
  enrolled_at  DATETIME NULL
);

Insert data

INSERT INTO raw_learning_events
(student_email, student_name, course_code, course_title, instructor_email, instructor_name,
 listed_price, currency, payment_id, payment_status, paid_at, enrolled_at)
VALUES
('asha@gmail.com', 'Asha', 'SQL101', 'SQL Basics', 'raj@tuf.com', 'Raj', 499.00, 'INR', 'pay_1001', 'PAID',    '2026-01-05 10:10:00', '2026-01-05 10:12:00'),
('asha@gmail.com', 'Asha Sharma', 'SQL101', 'SQL Basics', 'raj@tuf.com', 'Raj', 499.00, 'INR', 'pay_1002', 'REFUNDED','2026-01-06 09:00:00', '2026-01-06 09:02:00'),
('ravi@gmail.com', 'Ravi', 'SQL101', 'SQL Basics', 'raj@tuf.com', 'Raj', 599.00, 'INR', 'pay_1003', 'PAID',    '2026-01-10 18:00:00', '2026-01-10 18:02:00'),
('neha@gmail.com', 'Neha', 'DE201', 'Data Eng Foundations', 'sara@tuf.com', 'Sara', 19.00, 'USD', 'pay_1004', 'PAID', '2026-01-12 20:00:00', '2026-01-12 20:05:00'),
('neha@gmail.com', 'Neha', 'SQL101', 'SQL Basics', 'raj@tuf.com', 'Raj', 499.00, 'INR', 'pay_1005', 'PENDING', NULL, '2026-01-15 08:00:00'),
('dev@gmail.com', 'Dev', 'LLD301', 'LLD Bootcamp', 'mike@tuf.com', 'Mike', 49.00, 'USD', 'pay_1006', 'PAID', '2026-01-20 11:30:00', '2026-01-20 11:31:00'),
('dev@gmail.com', 'Dev', 'LLD301', 'LLD Bootcamp', 'mike@tuf.com', 'Mike', 49.00, 'USD', 'pay_1007', 'PAID', '2026-01-21 12:00:00', '2026-01-21 12:01:00'), -- duplicate enrollment scenario
('utkarsh@gmail.com', 'Utkarsh', 'DSA401', 'DSA Mastery', 'raj@tuf.com', 'Raj', 999.00, 'INR', 'pay_1008', 'PAID', '2026-01-22 09:00:00', '2026-01-22 09:05:00'),
('anuj@gmail.com', 'Anuj', 'SQL101', 'SQL Basics', 'raj@tuf.com', 'Raj', 499.00, 'INR', 'pay_1009', 'PAID', '2026-01-25 10:00:00', '2026-01-25 10:02:00'),
('ronit@gmail.com', 'Ronit', 'DE201', 'Data Eng Foundations', 'sara@tuf.com', 'Sara', 19.00, 'USD', 'pay_1010', 'PAID', '2026-01-25 15:00:00', '2026-01-25 15:05:00'),
('shivi@gmail.com', 'Shivi', 'SQL101', 'SQL Basics', 'raj@tuf.com', 'Raj', 499.00, 'INR', 'pay_1011', 'PAID', '2026-01-28 19:00:00', '2026-01-28 19:02:00'),
('siddhant@gmail.com', 'Siddhant', 'DE201', 'Data Eng Foundations', 'sara@tuf.com', 'Sara', 19.00, 'USD', 'pay_1012', 'REFUNDED', '2026-01-29 10:00:00', '2026-01-29 10:01:00'),
('vinayak@gmail.com', 'Vinayak', 'DSA401', 'DSA Mastery', 'raj@tuf.com', 'Raj', 999.00, 'INR', 'pay_1013', 'PAID', '2026-02-01 09:00:00', '2026-02-01 09:03:00'),
('mridul@gmail.com', 'Mridul', 'LLD301', 'LLD Bootcamp', 'mike@tuf.com', 'Mike', 49.00, 'USD', 'pay_1014', 'PAID', '2026-02-02 14:00:00', '2026-02-02 14:02:00'),
('prashant@gmail.com', 'Prashant', 'SQL101', 'SQL Basics', 'raj@tuf.com', 'Raj', 499.00, 'INR', 'pay_1015', 'PAID', '2026-02-03 16:00:00', '2026-02-03 16:01:00');

The same data as a table (instructor email and course title are left out only to save width; they repeat exactly as in the INSERT above)

At first glance, this looks complete. It has student information, course information, instructor details, payment status, and enrollment timestamps. Everything in one place. That is exactly the problem.

SQL is not about storing everything. It is about structuring relationships correctly. And this table violates that principle.

The real problems

Flat tables are comfortable because they resemble spreadsheets. Each row represents "an event": a student buys a course, so one row; another student buys, so another row. But slowly, problems start appearing. Look at the sample data carefully.

a. Student details repeated everywhere

Asha appears twice, with the same email but two different names:

Which one is correct? This is how inconsistency begins. In a spreadsheet, you might ignore it. In a real product, it becomes a support issue, a reporting issue, and a trust issue.

b. Course and instructor details repeated

Every time someone buys SQL101, this gets repeated:

 'SQL101', 'SQL Basics', 'raj@tuf.com', 'Raj'

It appears in 7 of the 15 rows. Raj's name is stored in 9 rows. Imagine 50,000 purchases. Now imagine that the course title changes, the instructor changes, or the price changes. You now need to update 50,000 rows. That is not scalable design. That is a data maintenance nightmare.

The data already shows the damage. SQL101 has a listed price of 499.00 in six rows and 599.00 in Ravi's row. Which one is the real price of the course? The table cannot say.

c. Duplicate enrollment confusion

Same student. Same course. Two successful payments. Is this:

• a duplicate enrollment?
• a payment retry?
• a system bug?

The flat table cannot enforce the business rule.

 What the flat table mixes into every row

 +-----------+---------+------------+---------+----------+
 | STUDENT   | COURSE  | INSTRUCTOR | PAYMENT | ENROLL   |
 | details   | details | details    | details | details  |
 +-----------+---------+------------+---------+----------+
   repeated     repeated   repeated    one      one
   on every     on every   on every    per      per
   row          row        row         row      row

Why this happens

Because this table is not a database design. It is a spreadsheet dump. Spreadsheets store events row by row. Databases model entities and relationships. There is a difference.

2. Core Concepts

Before you can redesign a messy table, you must understand the language of structure. Most database chaos does not happen because developers write bad SQL. It happens because they never pause to think about the core concepts that define how data should be organized.

These concepts are not academic theory. They are practical tools that prevent duplication, inconsistency, and scaling disasters.

Normalization

Normalization is the process of designing tables in a way that reduces duplication and prevents data anomalies. It forces you to separate repeated information into structured, logical entities.

• When student details appear on every purchase, that is duplication.
• When a course title must be updated in 10,000 rows, that is a design flaw.
• When deleting one row accidentally removes important information, that is an anomaly.

Normalization is not about "splitting tables randomly." It is about designing tables so that each fact is stored once and only once.

Entities

An entity is a real-world thing you store data about. In the learning platform, the entities are clear:

• Student
• Course
• Instructor
• Payment
• Enrollment

Each of these exists independently in the real world. A student exists whether they pay or not. A course exists whether someone enrolls or not. A payment exists because an enrollment exists. If you mix all entities into one row, you lose clarity about what each row truly represents. Good database design begins by identifying entities correctly.

Attributes

An attribute is a property of an entity.

Attributes describe entities. They do not define relationships between them. Whether student email must be unique is a domain rule, not a consequence of being an attribute. Likewise, payment validity rules such as allowing zero-value payments must match the business domain. This is where structure starts to enforce correctness.

Relationships

Relationships define how entities interact.

• A student enrolls in a course.
• A course is taught by an instructor.
• An enrollment has a payment.

Without relationships, a database is just disconnected tables. In the raw table, relationships are implied but not enforced. That is dangerous. Relationships can exist in the model without an enforced foreign key. A foreign key makes an eligible relationship database-enforced and protects referential integrity.

Cardinality

Cardinality answers one simple but critical question: how many?

• One student can enroll in many courses.
• One course can have many students.
• One enrollment should have one payment (or maybe multiple attempts).

Cardinality defines rules such as One-to-One, One-to-Many, and Many-to-Many. Without defining cardinality, you allow duplicate enrollments, inconsistent payments, and broken business logic. Cardinality protects your data model from ambiguity.

Constraints

Constraints are rules that reject bad data. They are not optional. They are essential.

In the raw design, there are almost no constraints. That means duplicate emails are possible, duplicate enrollments are possible, and negative payments are possible. The database accepts everything, including mistakes. A well-designed system rejects bad data automatically.

Why this matters

 Ignore the core concepts            Apply the core concepts
 ------------------------            -----------------------
 Entities become mixed          ->   Each entity has its own table
 Attributes become duplicated   ->   Each attribute belongs in one place
 Relationships become unclear   ->   Relationships are enforced
 Cardinality is accidental      ->   Cardinality is intentional
 Constraints are missing        ->   Constraints protect the system
        |                                    |
        v                                    v
   DATA CHAOS                        A database that scales naturally

3. The Mental Model

Before normalization rules, before 1NF, 2NF, 3NF, there is something more important: the mental model.

When you design data (for a coaching center, an affiliate system, or a product), pause and ask: how should I start thinking about tables?

Most engineers start with queries. They ask:

• How much revenue did we make?
• How many students do we have?
• Which course generates the most revenue?
• Who is the instructor of this course?

Then they design tables that answer those queries easily. This is the wrong approach.

You should never design tables for convenience. You should design tables for truth.

A production-style example: the affiliate system

Suppose you run an affiliate system:

• A user gets an affiliate code.
• When someone purchases using that code, the user earns 15%.

The convenient (but wrong) design

 user_id | total_affiliate_earnings

Every time a purchase happens, compute 15% and add it to the existing total. Checking earnings is simple:

SELECT total_affiliate_earnings
FROM users
WHERE user_id = 1;

Easy. Clean. Fast. But wrong. Why? Because you stored a derived value as truth. Now ask:

• What if a refund happens?
• What if a calculation bug happens?
• What if the commission rules change?

Your total column becomes corrupted, and you cannot repair it, because the individual sales it came from were never stored. You optimized for convenience, not correctness.

The correct design: one fact per row

 affiliate_code | customer_email | purchase_date | purchase_amount | commission_share

Each row represents one sale. Not one user's total. Not an accumulated number. Just one sale. If three purchases happen, you store three rows. To get total earnings, you compute it:

SELECT SUM(commission_share)
FROM affiliate_sales
WHERE affiliate_code = 'RAJ123';

You compute truth from facts. You do not store pre-aggregated assumptions.

A small worked example (extra)

SUM(commission_share) gives 525.00. Now the customer of the 500.00 sale is refunded. You change that one row's status to REFUNDED, and the query (with WHERE status = 'COMPLETED') gives 450.00 automatically. With a stored running total, somebody would have to remember to subtract 75.00 by hand.

 Stored total                          Stored facts
 ------------                          ------------
 525.00  (one number, source unknown)  sale 1: 150.00
                                       sale 2: 300.00
 a refund happens...                   sale 3:  75.00  -> change status to REFUNDED
 ... who fixes 525.00 ?                total is recomputed: 450.00

One fact per row

This is the first principle: one row should represent one fact.

• If you are storing students: one row = one student.
• If you are storing payments: one row = one payment.
• If you are storing courses: one row = one course.

Never mix facts in one row. Never store accumulated summaries as primary data.

What is a "truth table"?

A truth table stores the single source of truth for an entity.

In a proper truth table:

• Each row represents one real-world fact.
• If a value changes, you update it in exactly one place. Always one place. Never two.

If a student changes their name, you should update it once. If you need to update it in ten rows, your model is wrong. That is the signal.

The convenience table trap

Convenience tables are built to make reads easier or faster. For example:

• a dashboard summary table
• a reporting table with pre-joined data
• a denormalized analytics snapshot

These tables are not wrong. But they must be built from truth tables. They should never become the source of truth themselves.

 TRUTH TABLES (source of truth)          CONVENIENCE TABLES (derived)
 students, courses, payments   ------>   daily_revenue, dashboard_summary
        ^                                          |
        |                                          v
 edited here, in one place              read here; can be rebuilt any time

Truth tables define reality. Convenience tables derive from reality.

4. Schema Design

When you move from a flat spreadsheet model to a real production database, the biggest shift is not syntax. It is mindset. You stop designing for convenience. You start designing for correctness, integrity, and scale.

Identify the core entities

From first principles, we identify the real-world objects:

• Students
• Student Profiles
• Instructors
• Courses
• Enrollments
• Payments

Each represents a separate concept. Each deserves its own table.

 instructors (1) ------- teaches ------> (N) courses
 students    (1) ------- has ----------> (0 or 1) student_profiles
 students    (1) ------- enrolls ------> (N) enrollments (N) <------ (1) courses
 students    (1) ------- pays ---------> (N) payments    (N) <------ (1) courses

 enrollments is the BRIDGE between students and courses (many-to-many)

Students

CREATE TABLE students (
  student_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT uq_students_email UNIQUE (email)
);

Why this design? We generate our own internal ID:

student_id INT AUTO_INCREMENT PRIMARY KEY

This is called a surrogate key. We deliberately avoid using email as the primary key.

Why not use email as the primary key? This is one of the most common production mistakes. Email is unique, yes. But email is not stable. Imagine a student changes their email. If you used email as the foreign key in enrollments, payments, audit logs, and activity tables, you must now update it everywhere. That causes:

• update anomalies
• risk of partial updates
• lock contention
• integrity bugs

The email is still protected from duplicates by UNIQUE (email). The surrogate key gives identity. The unique constraint gives the business rule.

Student profiles

CREATE TABLE student_profiles (
  student_id INT PRIMARY KEY,
  country_code CHAR(2) NULL,
  graduation_year INT NULL,
  github_url VARCHAR(255) NULL,
  FOREIGN KEY (student_id)
    REFERENCES students(student_id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
);

Why a separate table? Not every student provides GitHub, graduation year, or location (or LinkedIn). If these were stored in students, most rows would contain NULL. The production principle:

• Keep core identity tables lean.
• Consider separate tables for optional or sparse data when access patterns, row width, ownership, and lifecycle justify the split.

This is workload-dependent: a separate table can improve a hot narrow row, but it also adds joins and operational complexity.

Why is student_id both the primary key and the foreign key here? Because a profile belongs to exactly one student, and a student has at most one profile. Using the student's id as the profile's primary key makes the relationship one-to-one automatically, since a primary key cannot repeat.

Why ON DELETE CASCADE here? If a student is deleted, their profile should automatically disappear. A profile has no meaning without its student. That is ownership, and ownership relationships use CASCADE.

Instructors

CREATE TABLE instructors (
  instructor_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  full_name VARCHAR(100) NOT NULL,
  CONSTRAINT uq_instructors_email UNIQUE (email)
);

Same logic as students: a primary key for identity, a unique email for the business rule, and never tie relationships to mutable business attributes.

Courses

CREATE TABLE courses (
  course_id INT AUTO_INCREMENT PRIMARY KEY,
  course_code VARCHAR(30) NOT NULL,
  title VARCHAR(200) NOT NULL,
  instructor_id INT NOT NULL,
  base_price DECIMAL(10,2) NOT NULL,
  currency CHAR(3) NOT NULL,
  is_active TINYINT NOT NULL DEFAULT 1,

  CONSTRAINT uq_courses_code UNIQUE (course_code),
  CONSTRAINT ck_courses_currency CHECK (currency IN ('INR','USD')),
  CONSTRAINT ck_courses_price CHECK (base_price >= 0),

  CONSTRAINT fk_courses_instructor
    FOREIGN KEY (instructor_id) REFERENCES instructors(instructor_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);

Data integrity constraints

CONSTRAINT uq_courses_code UNIQUE (course_code),
CONSTRAINT ck_courses_currency CHECK (currency IN ('INR','USD')),
CONSTRAINT ck_courses_price CHECK (base_price >= 0),

We validate data at the database level:

• No negative price
• No invalid currency
• No duplicate course codes

Production rule: never trust application validation alone. Enforce correctness at the database layer.

(CHECK constraints are enforced from MySQL 8.0.16. Older versions accepted them but ignored them.)

Instructor relationship

FOREIGN KEY (instructor_id)
REFERENCES instructors(instructor_id)
ON DELETE RESTRICT
ON UPDATE RESTRICT

Why RESTRICT? If an instructor teaches a course, you cannot delete that instructor. If you allowed the deletion:

• the course would point to a non-existent instructor
• frontend joins would fail
• reports would break
• analytics would be corrupted

RESTRICT protects referential integrity. Deletion must be intentional and safe.

Enrollments (the bridge table)

CREATE TABLE enrollments (
  enrollment_id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrolled_at DATETIME NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE', -- ACTIVE / COMPLETED / DROPPED

  CONSTRAINT ck_enroll_status CHECK (status IN ('ACTIVE','COMPLETED','DROPPED')),

  CONSTRAINT fk_enroll_student
    FOREIGN KEY (student_id) REFERENCES students(student_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,

  CONSTRAINT fk_enroll_course
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,

  -- Prevent duplicate enrollment for same student+course
  CONSTRAINT uq_enroll_unique UNIQUE (student_id, course_id)
);

Why a bridge table? Students and courses are a many-to-many relationship. One student has many courses, and one course has many students. That cannot be modeled with one foreign key.

Wrong design:

 courses_enrolled = [1, 4, 7]

This breaks First Normal Form, filtering, indexing, query performance, and data consistency.

Correct design:

 enrollments
 student_id | course_id

Each row = one fact. Bridge tables convert N:M into two 1:N relationships. This is fundamental relational modeling.

 students                 enrollments                 courses
 +------------+           +-----------+-----------+   +-----------+
 | 3  Neha    | <---1---N | student 3 | course 2  | N---1> | 2  DE201  |
 |            | <---1---N | student 3 | course 1  | N---1> | 1  SQL101 |
 | 1  Asha    | <---1---N | student 1 | course 1  | N---1> |           |
 +------------+           +-----------+-----------+   +-----------+
   Neha has 2 courses; SQL101 has 2 students (Asha and Neha), and more in the real data

Why UNIQUE (student_id, course_id)? It prevents duplicate enrollments. Without it, a student could enroll in the same course 3 times, and data corruption would be possible. Constraints prevent silent corruption.

Payments

CREATE TABLE payments (
  payment_pk INT AUTO_INCREMENT PRIMARY KEY,
  payment_id VARCHAR(40) NOT NULL,         -- external payment reference
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency CHAR(3) NOT NULL,
  status VARCHAR(20) NOT NULL,             -- PAID / PENDING / REFUNDED
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME NULL,

  CONSTRAINT uq_payments_payment_id UNIQUE (payment_id),
  CONSTRAINT ck_pay_currency CHECK (currency IN ('INR','USD')),
  CONSTRAINT ck_pay_amount CHECK (amount >= 0),
  CONSTRAINT ck_pay_status CHECK (status IN ('PAID','PENDING','REFUNDED')),

  CONSTRAINT fk_pay_student
    FOREIGN KEY (student_id) REFERENCES students(student_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT,

  CONSTRAINT fk_pay_course
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
    ON DELETE RESTRICT
    ON UPDATE RESTRICT
);

Why a separate payment primary key?

• payment_pk is the internal database identifier.
• payment_id is the external gateway reference (Stripe, Razorpay, and so on).

Never depend on an external id as your primary key. External systems change formats. Your system must remain stable. The UNIQUE (payment_id) still stops the same gateway payment from being stored twice.

Why RESTRICT for payments? Payments are financial records. You cannot delete a student with payment history, or a course with paid transactions. Financial integrity is more important than convenience.

Why are payments linked to a student and a course, and not to an enrollment? A payment can exist before an enrollment does (for example, a PENDING payment), and one student-course pair can have several payments (retries, refunds). That is why there is no UNIQUE (student_id, course_id) on payments. It is the design that lets the data say "Dev paid twice for LLD301" instead of hiding it.

A note on amount >= 0 vs amount > 0. The concept list earlier mentioned CHECK(amount > 0). The table allows 0, which permits free or fully discounted payments. Pick the rule that matches your business.

Insert the data into the normalized tables

INSERT INTO instructors (instructor_id, email, full_name)
VALUES
(1, 'raj@tuf.com', 'Raj'),
(2, 'sara@tuf.com', 'Sara'),
(3, 'mike@tuf.com', 'Mike');

INSERT INTO students (student_id, email, full_name)
VALUES
(1, 'asha@gmail.com', 'Asha Sharma'),
(2, 'ravi@gmail.com', 'Ravi'),
(3, 'neha@gmail.com', 'Neha'),
(4, 'dev@gmail.com', 'Dev'),
(5, 'utkarsh@gmail.com', 'Utkarsh'),
(6, 'anuj@gmail.com', 'Anuj'),
(7, 'ronit@gmail.com', 'Ronit'),
(8, 'shivi@gmail.com', 'Shivi'),
(9, 'siddhant@gmail.com', 'Siddhant'),
(10, 'vinayak@gmail.com', 'Vinayak'),
(11, 'mridul@gmail.com', 'Mridul'),
(12, 'prashant@gmail.com', 'Prashant');

INSERT INTO student_profiles (student_id, country_code, graduation_year, github_url)
VALUES
(1, 'IN', NULL, NULL),
(2, 'IN', NULL, NULL),
(3, 'IN', NULL, NULL),
(4, 'IN', NULL, NULL),
(5, 'IN', NULL, NULL),
(6, 'IN', NULL, NULL),
(7, 'IN', NULL, NULL),
(8, 'IN', NULL, NULL),
(9, 'IN', NULL, NULL),
(10, 'IN', NULL, NULL),
(11, 'IN', NULL, NULL),
(12, 'IN', NULL, NULL);

INSERT INTO courses (course_id, course_code, title, instructor_id, base_price, currency, is_active)
VALUES
(1, 'SQL101', 'SQL Basics', 1, 499.00, 'INR', 1),
(2, 'DE201', 'Data Eng Foundations', 2, 19.00, 'USD', 1),
(3, 'LLD301', 'LLD Bootcamp', 3, 49.00, 'USD', 1),
(4, 'DSA401', 'DSA Mastery', 1, 999.00, 'INR', 1);

INSERT INTO enrollments (enrollment_id, student_id, course_id, enrolled_at, status)
VALUES
(1, 1, 1, '2026-01-05 10:12:00', 'ACTIVE'),
(2, 2, 1, '2026-01-10 18:02:00', 'ACTIVE'),
(3, 3, 2, '2026-01-12 20:05:00', 'ACTIVE'),
(4, 3, 1, '2026-01-15 08:00:00', 'ACTIVE'),
(5, 4, 3, '2026-01-20 11:31:00', 'ACTIVE'),
(6, 5, 4, '2026-01-22 09:05:00', 'ACTIVE'),
(7, 6, 1, '2026-01-25 10:02:00', 'ACTIVE'),
(8, 7, 2, '2026-01-25 15:05:00', 'ACTIVE'),
(9, 8, 1, '2026-01-28 19:02:00', 'ACTIVE'),
(10, 9, 2, '2026-01-29 10:01:00', 'ACTIVE'),
(11, 10, 4, '2026-02-01 09:03:00', 'ACTIVE'),
(12, 11, 3, '2026-02-02 14:02:00', 'ACTIVE'),
(13, 12, 1, '2026-02-03 16:01:00', 'ACTIVE');

INSERT INTO payments (payment_pk, payment_id, student_id, course_id, amount, currency, status, created_at, paid_at)
VALUES
(1, 'pay_1001', 1, 1, 499.00, 'INR', 'PAID', '2026-01-05 10:10:00', '2026-01-05 10:10:00'),
(2, 'pay_1002', 1, 1, 499.00, 'INR', 'REFUNDED', '2026-01-06 09:00:00', '2026-01-06 09:00:00'),
(3, 'pay_1003', 2, 1, 599.00, 'INR', 'PAID', '2026-01-10 18:00:00', '2026-01-10 18:00:00'),
(4, 'pay_1004', 3, 2, 19.00, 'USD', 'PAID', '2026-01-12 20:00:00', '2026-01-12 20:00:00'),
(5, 'pay_1005', 3, 1, 499.00, 'INR', 'PENDING', '2026-01-15 08:00:00', NULL),
(6, 'pay_1006', 4, 3, 49.00, 'USD', 'PAID', '2026-01-20 11:30:00', '2026-01-20 11:30:00'),
(7, 'pay_1007', 4, 3, 49.00, 'USD', 'PAID', '2026-01-21 12:00:00', '2026-01-21 12:00:00'),
(8, 'pay_1008', 5, 4, 999.00, 'INR', 'PAID', '2026-01-22 09:00:00', '2026-01-22 09:00:00'),
(9, 'pay_1009', 6, 1, 499.00, 'INR', 'PAID', '2026-01-25 10:00:00', '2026-01-25 10:00:00'),
(10, 'pay_1010', 7, 2, 19.00, 'USD', 'PAID', '2026-01-25 15:00:00', '2026-01-25 15:00:00'),
(11, 'pay_1011', 8, 1, 499.00, 'INR', 'PAID', '2026-01-28 19:00:00', '2026-01-28 19:00:00'),
(12, 'pay_1012', 9, 2, 19.00, 'USD', 'REFUNDED', '2026-01-29 10:00:00', '2026-01-29 10:00:00'),
(13, 'pay_1013', 10, 4, 999.00, 'INR', 'PAID', '2026-02-01 09:00:00', '2026-02-01 09:00:00'),
(14, 'pay_1014', 11, 3, 49.00, 'USD', 'PAID', '2026-02-02 14:00:00', '2026-02-02 14:00:00'),
(15, 'pay_1015', 12, 1, 499.00, 'INR', 'PAID', '2026-02-03 16:00:00', '2026-02-03 16:00:00');

Where did each fact from the flat table go?

The enrollment count is 13 (not 15) because the flat table had 15 events but only 13 different student-course pairs: Asha's and Dev's repeated rows collapse into one enrollment each.

Seeing the design work

1. Change one fact in one place

UPDATE instructors SET full_name = 'Raj Kumar' WHERE instructor_id = 1;

2. Revenue per course (computed from facts)

SELECT c.course_code, c.title, c.currency,
       COUNT(*) AS paid_payments,
       SUM(p.amount) AS revenue
FROM payments p
JOIN courses c ON c.course_id = p.course_id
WHERE p.status = 'PAID'
GROUP BY c.course_id, c.course_code, c.title, c.currency
ORDER BY c.course_id;

Revenue is shown per currency. You cannot add INR and USD together without a conversion rate. The refunded payments (pay_1002, pay_1012) and the pending one (pay_1005) are not counted.

3. Find duplicate paid payments for the same student and course

SELECT student_id, course_id, COUNT(*) AS paid_count
FROM payments
WHERE status = 'PAID'
GROUP BY student_id, course_id
HAVING COUNT(*) > 1;

Dev (student 4) paid twice for LLD301 (course 3). Asha's pair is not listed, because one of her two payments was refunded. The flat table could not even ask this question cleanly.

4. Find enrollments with no PAID payment

SELECT e.enrollment_id, s.full_name, c.course_code
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses c  ON c.course_id  = e.course_id
WHERE NOT EXISTS (
  SELECT 1 FROM payments p
  WHERE p.student_id = e.student_id
    AND p.course_id  = e.course_id
    AND p.status = 'PAID'
);

Neha's SQL101 payment is still PENDING, and Siddhant's DE201 payment was REFUNDED. Both enrollments are ACTIVE in the sample data. Whether that is allowed is a business rule that the design now lets you detect and decide on.

5. The database rejects bad data

In the flat table, every one of these mistakes would have been accepted silently.

5. Entities and Attributes

Once you understand the mental model of "one fact per row", the next foundation is understanding entities and attributes.

What is an entity?

An entity is a real-world object or concept about which you store data. It represents something that exists independently in your system.

Examples: Student, Course, Payment, Instructor, Affiliate.

If you can say "I want to store information about this thing", that thing is likely an entity. In database design, each entity usually becomes its own table.

What is an attribute?

An attribute is a property or characteristic of an entity.

Attributes describe the entity. They do not represent separate real-world objects on their own.

What is a table?

A table is a structured collection of rows representing instances of an entity. If Student is an entity, the students table stores all student records. Each row is one student. Each column is one attribute.

What is a row?

A row represents one instance of an entity. If the entity is Student:

• One row = one student.
• Not multiple students.
• Not part of a student.
• Exactly one student.

This matches the mental model: one row should represent one fact.

 Entity   ->  Table      Attribute  ->  Column      Instance  ->  Row
 Student      students   email          email        Asha         1 row

Example

In a coaching center system, typical entities are Students, Courses, and Payments. Each should have its own table. You should not mix student attributes, course attributes, and payment attributes into one large flat table. That violates entity boundaries.

Production tip: keep entities stable. Do not mix multiple entities into a single table.

6. Relationships, Cardinality, and Optionality

This is one of the most important concepts in database design. Once entities and keys are clear, the next step is understanding how entities connect. If you understand this section, your schemas become predictable, scalable, and production-ready.

Relationship

A relationship defines how one entity is connected to another. In a relational schema it may be represented by matching key values even when the database does not enforce a foreign key. When enforced, a foreign key can reference an eligible primary or unique key, subject to the database's rules. The constraint validates the connection; it does not create the domain relationship itself.

Example 1: Course to Instructor

 courses
 ---------
 course_id (PK)
 course_name
 instructor_id (FK => instructors.instructor_id)

instructor_id is a foreign key. It stores the primary key from the instructors table. If a row contains course_id = 1, course_name = SQL 101, instructor_id = 1, it means "this course belongs to instructor 1". That is a relationship.

Example 2: Student profile to Student

 student_profiles
 ----------------
 profile_id (PK)
 student_id (FK => students.student_id)

This means "this profile belongs to that student". Again, the relationship is made by a foreign key. (In the schema built in section 4, student_id is itself the primary key of student_profiles. Both ways work. The next part explains how each one enforces one-to-one.)

Important clarification

• A primary key uniquely identifies a row.
• There can be other unique columns (candidate keys).
• But there is only one primary key per table.

Other tables commonly reference the primary key, but a foreign key may also target another eligible unique key where the database permits it.

Cardinality

Cardinality answers one question: for one row on the left, how many rows can exist on the right? There are three types.

 One-to-One   (1:1)   A ----- B          one A has at most one B
 One-to-Many  (1:N)   A ----< B          one A has many Bs; each B has one A
 Many-to-Many (N:M)   A >---< B          needs a third (bridge) table

One-to-One (1:1)

One A maps to at most one B.

Example: students and student_profiles. One student can have one profile or zero profiles, but never multiple profiles.

Important detail: a foreign key alone does NOT make it 1:1. To enforce 1:1, the foreign key must also be unique. There are two ways:

 Way 1 (used in the schema):       Way 2:
 student_profiles                  student_profiles
 ----------------                  ----------------
 student_id PK + FK                profile_id  PK
                                   student_id  FK + UNIQUE

Without UNIQUE, the relationship becomes 1:N, because many profile rows could point to one student.

One-to-Many (1:N)

One A maps to many B. Each B maps back to exactly one A.

Example: Instructor to Courses. One instructor can teach many courses, but each course has only one instructor. If Raj teaches SQL 101, DSA Mastery, and SQL Intermediate, Raj appears multiple times in the courses table. That is 1:N.

Rule of thumb: the foreign key goes on the side that can repeat. Many courses can have the same instructor, so instructor_id is stored inside courses.

 instructors                courses
 +----+------+              +----+--------+---------------+
 | 1  | Raj  | <----------- | 1  | SQL101 | instructor 1  |
 |    |      | <----------- | 4  | DSA401 | instructor 1  |
 | 2  | Sara | <----------- | 2  | DE201  | instructor 2  |
 +----+------+              +----+--------+---------------+
  the "one" side              the "many" side holds the FK

Many-to-Many (N:M)

Many A relate to many B.

Example: Students and Courses. One student can enroll in many courses, and one course can have many students. You cannot store this using a single foreign key.

Wrong approach (a common beginner mistake)

 students
 ---------
 student_id
 courses_enrolled  -- storing a list

Storing a list:

• breaks First Normal Form
• breaks indexing
• breaks filtering
• breaks joins
• violates the single source of truth
• causes update chaos

Never store lists inside a column.

Correct approach: a bridge (junction) table

 enrollments
 --------------
 student_id (FK)
 course_id  (FK)
 PRIMARY KEY (student_id, course_id)

Each row represents a fact: "this student enrolled in this course". Now N:M becomes two 1:N relationships:

• students to enrollments
• courses to enrollments

Production tip: In a normalized relational design, an N:M relationship is typically modeled with a bridge table. That table has real storage, join, and maintenance costs, but it provides queryable relationship rows and enforceable keys; choose exceptions only when the domain and workload justify them.

Optionality

Optionality answers: is the relationship mandatory, or can it be missing? It is controlled by NULL vs NOT NULL.

Mandatory relationship Example: every course must have an instructor.

instructor_id INT NOT NULL

A course cannot exist without an instructor. The relationship is required.

Optional relationship Example: a student may or may not have a profile. Some students have profiles, and some do not. For an optional relationship held by a column (like a "referred by" student), the foreign key can be NULL.

Do not confuse an optional relationship with an optional attribute. In the sample data, every student has a profile row, but graduation_year and github_url are NULL (optional attributes inside an existing row).

7. Keys

After defining relationships, cardinality, and optionality, the next critical concept is identity. Every row must be uniquely identifiable. Without identity, you cannot reliably update, delete, or join data.

Primary key

A primary key uniquely identifies each row in a table.

• It must be unique.
• It must not be NULL.
• There can be only one primary key per table.

 students
 ---------
 student_id (PK)
 name
 email

student_id ensures every student row is uniquely identifiable. Even if two students share the same name, their student_id is different.

Candidate key

A candidate key is a minimal superkey: a column set that uniquely identifies a row, with no proper subset that still does so. From all candidate keys, one is chosen as the primary key.

Example: if email is unique in students, then student_id is a candidate key and email is a candidate key. You choose one as primary. The rest remain alternate keys.

Unique constraint

A UNIQUE constraint ensures that no duplicate values exist in a column.

ALTER TABLE students
ADD CONSTRAINT unique_email UNIQUE (email);

This enforces uniqueness but does not make the column the primary key. (In the schema above, students already has uq_students_email, so this statement is shown only to teach the syntax. Running it again would add a second, redundant unique index.)

A table can have many unique constraints but only one primary key. A unique column can allow NULL values (the primary key cannot).

Foreign key

A foreign key enforces valid relationships between tables. It ensures referential integrity.

 payments
 ---------
 payment_id (PK)
 student_id (FK => students.student_id)
 amount

This guarantees that a payment cannot exist without a valid student. The relationship is enforced by the database.

Important: a foreign key does not imply uniqueness. Multiple payments can refer to the same student.

(In the real schema, the payments table uses payment_pk as its primary key and keeps payment_id as a unique external reference, as explained in section 4.)

What happens on delete and update

Composite key

Sometimes a single column is not enough to ensure uniqueness. A composite key combines multiple columns.

 enrollments
 ------------
 student_id
 course_id
 PRIMARY KEY (student_id, course_id)

Here:

• A student can enroll in many courses.
• A course can have many students.
• But the same student cannot enroll in the same course twice.

The combination uniquely identifies a row.

Two valid ways to key a bridge table

Both prevent duplicate enrollments.

Production insight

Keys make joins reliable. When tables have proper primary and foreign keys, joins become predictable, data integrity is maintained, and bugs caused by duplication are reduced. Constraints catch errors that your application code may miss. Always let the database enforce integrity.

8. Surrogate vs Natural Keys

Now comes an important architectural decision: should your primary key have business meaning?

Natural key

A natural key is something that already exists in the business domain. Examples: email, phone number, PAN number, passport number. These values have real-world meaning.

 students
 ---------
 email (PK)
 name

Sounds clean. But there is a problem: natural keys can change. Emails change, phone numbers change, business rules change. If your primary key changes, all related foreign keys must change too. That creates cascading complexity.

Surrogate key

A surrogate key is a generated identifier with no business meaning. Examples: student_id (auto-increment), order_id, a UUID.

 students
 ---------
 student_id (PK)
 email (UNIQUE)
 name

Here student_id is stable, email is still enforced as UNIQUE, and email can change safely (only one row changes).

Why the industry prefers surrogate keys for joins

• Stability: once assigned, it never changes.
• Performance: smaller numeric keys mean faster indexing and more efficient joins.
• Isolation from business changes: business logic evolves, ids stay stable.

 Email as the key                      Surrogate key
 -----------------                     -------------
 student changes email:                student changes email:
   update students                       update students       (1 row)
   update enrollments                    nothing else changes
   update payments
   update audit logs ...

Important clarification

Surrogate keys do not prevent duplicates by themselves. If you use only student_id (PK) and email without a UNIQUE constraint on email, you can still store duplicate emails. Therefore:

• Surrogate key => for identity
• Unique constraint => for business rules

Both are needed.

9. Why Normalization Helps

Normalization is not about memorizing definitions. It is about protecting truth. The formal definition:

Normalization is the process of designing database tables so that each real-world fact is stored exactly once, and everything else references it using keys.

That single sentence contains the entire philosophy: store the truth once, avoid duplication, link using relationships. When you design from that mindset, your schema becomes stable. When you ignore it, anomalies creep in.

What goes wrong without normalization?

When data is duplicated, three types of anomalies appear.

1. Update anomaly

Say you have:

 students
 ---------
 student_id | name | email

But you also stored student_name in enrollments for convenience. Now Asha updates her name. You update the students table, but you forget to update enrollments. Now:

• one table says "Asha Sharma"
• another says "Asha S."

Reports break. Analytics break. Joins produce inconsistent data.

 students                     enrollments (with a copied name)
 +----+-------------+          +------------+-----------+-------------+
 | 1  | Asha Sharma | (new)    | student_id | course_id | student_name|
 +----+-------------+          | 1          | 1         | Asha S.     |  (old)
                               | 1          | 4         | Asha S.     |  (old)
                               +------------+-----------+-------------+

The raw table already shows this: Asha is "Asha" in one row and "Asha Sharma" in another. Normalization prevents this by making sure the name lives in one table only.

2. Insert anomaly

Suppose the enrollments table also stores student name and email for "faster access". When inserting a new enrollment:

• you must insert the redundant student details again
• if a new column is added later (like a phone number), you must update the insert logic everywhere

Insertion becomes complex and error-prone. Proper normalization ensures you insert the student once, insert the enrollment once, and connect them via foreign keys.

The classic form of this anomaly (extra): in the flat table you cannot record a brand-new course (say DB501) until a student buys it, because every row is a purchase. You would have to invent a fake student row full of NULLs. In the normalized design, courses holds the new course by itself.

3. Delete anomaly

Imagine a student record exists, their profile exists, and their enrollments exist. If you delete the student:

• what happens to the enrollments?
• what happens to the profile?
• what about financial audit records?

If relationships are not carefully defined, you either accidentally lose important data, or the database refuses the deletion.

The classic form of this anomaly (extra): in the flat table, DSA401 exists only in the two rows bought by Utkarsh and Vinayak. If those two rows are deleted (say, both are cleaned up), the course, its price, and its instructor vanish from your data. In the normalized design, deleting an enrollment never deletes a course.

How foreign key rules protect you

This is why foreign key rules exist:

• ON DELETE CASCADE deletes dependent records automatically.
• ON DELETE RESTRICT prevents deletion if child records exist.
• ON UPDATE RESTRICT prevents key changes that would break references.

Demo of the two behaviors (extra)

-- a new student with a profile, and no enrollments or payments
INSERT INTO students (student_id, email, full_name) VALUES (13, 'new@gmail.com', 'New Student');
INSERT INTO student_profiles (student_id, country_code) VALUES (13, 'IN');

DELETE FROM students WHERE student_id = 13;
-- the profile row is deleted too (CASCADE)

DELETE FROM students WHERE student_id = 1;
-- fails with error 1451: Asha still has enrollments and payments (RESTRICT)

10. Normal Forms

Normalization is broken into stages called normal forms:

• 1NF (First Normal Form)
• 2NF (Second Normal Form)
• 3NF (Third Normal Form)

Each form removes a specific type of structural flaw, and each one builds on the one before it.

 Raw table
    |
    v  one value per cell, no lists, no repeating groups
   1NF
    |
    v  no column depends on only PART of a composite key
   2NF
    |
    v  no non-key column depends on another non-key column
   3NF

First Normal Form (1NF)

A table is in 1NF if:

• Every column holds a single, atomic value.
• No column contains lists or comma-separated values.
• No repeating groups like course1, course2, course3.
• As practical modeling guidance, each row has a clear meaning; the formal 1NF rule is about relational values and repeating groups, not a requirement that a row represent one business entity type.

Why atomic values matter

Bad design:

This violates 1NF. To check if student 1 enrolled in course 4, you must parse a string and do a linear search. Indexing becomes useless.

Correct design:

Now indexing works, search becomes instant, and joins are clean.

Key idea: one cell = one value. That is the foundation of relational databases.

Repeating groups problem

Bad:

 student_id | course1_notes | course2_notes | course3_notes

This assumes a maximum of 3 courses, and the schema must change if a 4th course appears.

Correct:

 note_id | student_id | course_id | note_text

Now you can have unlimited courses, the schema stays stable, and queries become clean.

Why 1NF is critical. If you violate 1NF:

• indexing fails
• searching becomes slow
• aggregations break
• joins become unreliable

1NF is the bare minimum requirement for serious databases.

Second Normal Form (2NF)

2NF is defined across all candidate keys. Partial dependencies can arise when a candidate key is composite, even if a different key was chosen as the primary key.

A table is in 2NF if:

• It is already in 1NF.
• Every non-prime attribute is fully functionally dependent on every candidate key.
• No partial dependency exists.

What is a partial dependency?

Suppose the primary key is (student_id, course_id). Now you store student_name and enrollment_date (illustrative rows, not from the sample data):

• student_name depends only on student_id. It does not depend on course_id. This is a partial dependency. It violates 2NF. (Notice how the copies can disagree.)
• enrollment_date depends on both the student and the course. It belongs here.

Correct approach

• Move student_name to the students table.
• Keep only data that depends on both student and course in enrollments.

Valid fields in enrollments: enrollment_date, grade, payment_id.

 Key = (student_id, course_id)

 student_name     depends on student_id only   -> partial dependency (move to students)
 enrollment_date  depends on BOTH              -> stays

Rule: every non-key column must depend on the whole key, not part of it.

A table whose every candidate key is single-column satisfies 2NF once it satisfies 1NF; a single-column primary key alone is not enough if another candidate key is composite.

Third Normal Form (3NF)

3NF removes transitive dependency.

A table is in 3NF if:

• It is already in 2NF.
• As a useful mnemonic, non-key attributes should not depend on other non-key attributes. Formally, for every non-trivial functional dependency X -> A, X is a superkey or A is a prime attribute.

What is a transitive dependency?

Suppose in enrollments the primary key is (student_id, course_id), and you store payment_id, payment_amount, and payment_gateway (illustrative rows):

Here:

• payment_id determines payment_amount
• payment_id determines payment_gateway

So:

 (student_id, course_id)  =>  payment_id  =>  payment_amount

The payment amount depends on payment_id, not directly on the primary key. That is a transitive dependency, and it violates 3NF.

Correct design

Keep only payment_id in enrollments. Store amount and gateway in the payments table.

Simplified rule: non-key columns should depend on the key rather than another non-key column. Formally, 3NF allows X -> A when X is a superkey or A is prime.

(The gateway column is illustrative. The real payments table in this chapter does not have one.)

Reading the flat table through the normal forms (extra)

The flat table's primary key is row_id, a single column, so 2NF is automatic, and every cell holds one atomic value, so it is in 1NF. So where is the problem? In 3NF: many non-key columns depend on other non-key columns.

 row_id (PK)
   |-- student_email  -->  student_name
   |-- course_code    -->  course_title, listed_price, currency, instructor_email
   |                                                              |
   |                                                              +--> instructor_name
   +-- payment_id     -->  payment_status, paid_at

Each arrow from a non-key column to other non-key columns is a transitive dependency. Each one points to a table that should exist on its own:

That is exactly the set of tables built in section 4. The schema was not invented. It was read off the dependencies.

Deep Dive

Why these rules work. Every rule in this chapter points at the same goal: a fact is stored in one place, so it can be updated in one place, and it cannot disagree with itself.

Designing a schema: a short checklist

1. List the entities (things you store facts about).
2. List the attributes of each entity.
3. Decide the relationships and their cardinality (1:1, 1:N, N:M).
4. Put foreign keys on the side that repeats. Use a bridge table for N:M.
5. Choose keys: a surrogate primary key, plus unique constraints on real business identifiers.
6. Decide optionality (NOT NULL or nullable).
7. Add constraints (UNIQUE, CHECK, FOREIGN KEY) and choose CASCADE or RESTRICT for each.
8. Check the design against 1NF, 2NF, and 3NF.
9. Only then add indexes and, if really needed, convenience tables.

Foreign key details (MySQL)

• Foreign keys need the InnoDB storage engine.
• The two columns must have matching types.
• MySQL automatically creates an index on a foreign key column if you did not.
• RESTRICT and NO ACTION behave the same in MySQL.

Denormalization is allowed, with a reason. Sometimes a reporting table with pre-joined data or a stored total makes reads fast. That is fine when it is derived from the truth tables and can be rebuilt. It is a mistake when it is the only copy of the truth (the affiliate total example).

JSON and normalization (link to Day 34). A JSON column is fine for flexible, rarely filtered details (like an external payload). It is not a place for data you must join, constrain, or count. A JSON array of course ids inside students would break 1NF for the same reasons as courses_enrolled = [1, 4, 7].

Money and data types. Store money in DECIMAL, not FLOAT (as the base_price and amount columns do), and store the currency next to the amount.

Where do payments attach: student and course, or an enrollment?

Neither is wrong. The right choice depends on the business flow.

Beyond 3NF. Higher forms exist (for example, BCNF, which is a stricter version of 3NF). For most application databases, a clean 3NF design is the goal.

Cardinality summary

Real-world Use Cases

• E-learning platforms: students, courses, instructors, enrollments, payments (this chapter).
• E-commerce: customers, products, orders, order items (the bridge between orders and products), payments.
• Banking and finance: customers, accounts, transactions, each transaction a separate fact, balances computed from them.
• Affiliate and referral systems: one row per sale, earnings computed with SUM.
• HR systems: employees, departments, managers (a self-referencing foreign key), salaries.
• Booking systems: users, venues, bookings, payments, with unique constraints to stop double bookings.
• Reporting layers: summary tables built from truth tables, never the other way around.

Review Tips

Interviewers commonly ask:

• "What is normalization, and why do we do it?" — To reduce duplication and prevent update, insert, and delete anomalies. Each fact is stored once.
• "Explain 1NF, 2NF, and 3NF." — Atomic values; no partial dependency on a composite key; no transitive dependency between non-key columns.
• "What is the difference between a primary key, a candidate key, and a unique key?" — Primary: the chosen identifier (one per table, not NULL). Candidate: any column set that could identify a row. Unique: a constraint that blocks duplicates (many allowed).
• "Why use a surrogate key instead of email?" — Emails change. A stable id avoids cascading updates. Keep a UNIQUE constraint on email for the business rule.
• "Does a foreign key make a relationship one-to-one?" — No. A foreign key alone gives 1:N. Add UNIQUE (or use a shared primary key) for 1:1.
• "How do you model a many-to-many relationship?" — With a bridge table that holds both foreign keys, and a unique pair to stop duplicates.
• "Why not store a list of ids in a column?" — It breaks 1NF, indexing, joins, and consistency.
• "CASCADE or RESTRICT?" — CASCADE for owned data (a profile). RESTRICT for history and money (payments).
• "Why not store a running total?" — A derived value stored as truth gets corrupted by refunds, bugs, and rule changes. Store facts and compute the total.
• "When would you denormalize?" — For read performance, in convenience tables derived from truth tables.
• "What is an update anomaly?" — The same fact stored in two places and updated in only one.

Common Mistakes to Avoid

Mistake 1: Dumping a spreadsheet into one table

Why it happens: It is fast, and it looks complete.

Result: repeated and contradictory data (Asha's two names, SQL101's two prices).

Correct: Split by entity, one table per real-world thing.

Mistake 2: Using email (or phone) as the primary key

Why it happens: It is unique, so it feels like an identifier.

Result: when it changes, every foreign key must change too.

Correct: Use a surrogate key, and keep UNIQUE (email).

Mistake 3: Having a surrogate key and forgetting the UNIQUE constraint

Why it happens: The id feels like enough.

Result: duplicate emails are accepted.

Correct: Surrogate key for identity, unique constraint for the business rule.

Mistake 4: Storing lists in a column

Why it happens: courses_enrolled = '1,4,7' looks compact.

Result: no indexing, no clean joins, no integrity.

Correct: One row per fact, in a bridge table.

Mistake 5: Storing a derived total as the source of truth

Why it happens: Reading one number is easy and fast.

Result: refunds and bugs corrupt it, with no way to repair it.

Correct: Store each fact, compute the total with SUM, and use summary tables only as derived copies.

Mistake 6: Trusting the application to validate

Why it happens: "Our code checks that already."

Result: one bug, script, or manual fix writes bad data.

Correct: Add NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints in the database.

Mistake 7: Modeling N:M without a bridge table

Why it happens: It seems like extra work.

Result: lists in columns, or repeated rows with no rules.

Correct: Always add the third table, with UNIQUE on the pair.

Mistake 8: Thinking a foreign key is unique

Why it happens: It points to a unique key, so it feels unique.

Result: a "1:1" relationship that allows many rows.

Correct: Add UNIQUE on the foreign key (or use a shared primary key) for 1:1.

Mistake 9: Putting the foreign key on the wrong side

Why it happens: Confusion about which side is "one".

Result: the design cannot store the relationship.

Correct: The foreign key goes on the side that can repeat (the "many" side): instructor_id in courses.

Mistake 10: Using CASCADE on financial or history tables

Why it happens: It makes deletes "just work".

Result: deleting a student silently erases payment history.

Correct: Use RESTRICT for records that must survive. Use CASCADE only for owned data like a profile.

Mistake 11: Copying names "for convenience" into child tables

Why it happens: It saves a join.

Result: update anomalies when the name changes.

Correct: Store the id, and join to get the name.

Mistake 12: Using an external id as the primary key

Why it happens: The payment gateway already gives an id.

Result: you are tied to a format you do not control.

Correct: Your own payment_pk, plus a unique payment_id.

Mistake 13: Using FLOAT for money, or dropping the currency

Why it happens: Any number type looks fine.

Result: rounding errors, and amounts you cannot compare (INR vs USD).

Correct: DECIMAL for amounts, with a currency column.

Mistake 14: Assuming CHECK constraints always work

Why it happens: The syntax is accepted.

Result: on MySQL before 8.0.16, the CHECK is parsed but not enforced.

Correct: Use MySQL 8.0.16 or later, or confirm the rule is enforced.

Mistake 15: Splitting tables randomly

Why it happens: "More tables means more normalized."

Result: extra joins with no benefit.

Correct: Split only where a fact would otherwise be stored more than once.

Key Takeaways

• A flat table copies the same facts into many rows. That is the root of inconsistency, expensive updates, and hidden duplicates.
• Normalization means each fact is stored once, and everything else references it using keys.
• Design tables for truth, not convenience. One row = one fact.
• A truth table is the single source of truth. Change a fact in exactly one place. If you must update many rows, the model is wrong.
• Never store derived totals as truth. Store facts and compute totals. Convenience tables must be derived from truth tables.
• Entity = table, attribute = column, instance = row.
• Relationships are made by foreign keys. The foreign key goes on the "many" side.
• Cardinality: 1:1 needs a unique foreign key, 1:N needs a foreign key on the many side, and N:M needs a bridge table.
• Optionality is NOT NULL (mandatory) vs nullable (optional).
• Keys: one primary key per table, candidate keys are the options, UNIQUE blocks duplicates, FOREIGN KEY protects references, and composite keys combine columns.
• Use surrogate keys for identity, and unique constraints for business rules. Natural keys can change.
• Constraints (UNIQUE, CHECK, NOT NULL, FOREIGN KEY) make the database reject bad data. Never rely only on the application.
• Use CASCADE for owned data and RESTRICT for history and money.
• Anomalies: update (a copy is missed), insert (you must add redundant data or cannot add a fact alone), delete (you lose other facts).
• 1NF: one atomic value per cell, no lists, no repeating groups. 2NF: no partial dependency on a composite key. 3NF: no non-key column depends on another non-key column.