Intermediate Data Modeling

What is a Many-to-Many Relationship?

• A many-to-many (M:N) relationship happens when:

  • One record in Table A can relate to many records in Table B.

  • One record in Table B can relate to many records in Table A.

  • Real life examples:

    • A student can enroll in many courses.

    • A course can have many students.

    • An author can write many books.

    • A book can have many authors.

tip

• Relational databases cannot directly store a many-to-many relationship, so we use a junction table.

What is a Junction Table (Join Table)?

• A junction table is a table whose job is to "join" the two tables.

• It usually contains:

  • Foreign key to Table A.

  • Foreign key to Table B.

  • These two columns together form the composite primary key.

Students and Courses

• Tables:

  • students

  • courses

  • students_courses (Junction table)

students table

id	name
1	Alice
2	Bob

courses table

id	name
100	Math
200	Biology

students_courses (Junction table)

student_id	course_id
1	100
1	200
2	100

• Table data reveals that:

  • Alice is taking Math and Biology

  • Bob is taking Math

-- Create tables
-- Students
CREATE TABLE students (
    id AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Courses
CREATE TABLE courses (
    id AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Junction Table
CREATE TABLE students_courses (
    student_id INT REFERENCES students(id),
    course_id  INT REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);

-- Inserting Data
INSERT INTO students (name) VALUES ('Alice'), ('Bob');
INSERT INTO courses (name) VALUES ('Math'), ('Biology');
INSERT INTO students_courses (student_id, course_id)
VALUES
  (1, 1),  -- Alice => Math
  (1, 2),  -- Alice => Biology
  (2, 1);  -- Bob => Math

-- Get all courses for a student
SELECT s.name AS student, c.name AS course
FROM students s
JOIN students_courses sc ON sc.student_id = s.id
JOIN courses c ON c.id = sc.course_id
WHERE s.id = 1;

• Result:

student	course
Alice	Math
Alice	Biology

-- Get all students in a course
SELECT c.name AS course, s.name AS student
FROM courses c
JOIN students_courses sc ON sc.course_id = c.id
JOIN students s ON s.id = sc.student_id
WHERE c.id = 1;

• Result:

course	student
Math	Alice
Math	Bob

Authors and Books

• Tables:

  • authors

  • books

  • authors_books (junction table)

authors table

id	name
1	Alice
2	Bob

books table

id	name
100	Math
200	Biology

authors_books (junction table)

author_id	book_id
1	100
1	200
2	200

• Table data reveals that:

  • Alice is the author of Math and Biology

  • Bob is the author of Math

-- Create tables
-- Students
CREATE TABLE students (
    id AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Courses
CREATE TABLE courses (
    id AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

-- Junction Table
CREATE TABLE students_courses (
    author_id INT REFERENCES authors(id),
    book_id  INT REFERENCES books(id),
    PRIMARY KEY (author_id, book_id)
);

-- Inserting Data
INSERT INTO authors (name) VALUES ('Alice'), ('Bob');
INSERT INTO books (name) VALUES ('Math'), ('Biology');
INSERT INTO authors_books (author_id, book_id)
VALUES
  (1, 1),  -- Alice => Math
  (1, 2),  -- Alice => Biology
  (2, 1);  -- Bob => Math

-- Get all books for a author
SELECT a.name AS author, b.name AS book
FROM authors a
JOIN authors_books ab ON ab.author_id = a.id
JOIN books b ON b.id = ab.book_id
WHERE a.id = 1;

• Result:

author	book
Alice	Math
Alice	Biology

-- Get all students in a course
SELECT b.name AS book, a.name AS author
FROM books b
JOIN authors_courses ac ON ac.book_id = b.id
JOIN authors a ON a.id = ac.author_id
WHERE c.id = 1;

• Result:

book	author
Math	Alice
Math	Bob

Use a Junction Table?

• It avoids duplicating data.

• It correctly models M:N relationships.

• It is scalable (many students + many courses).

What is a Composite Primary Key?

• A composite primary key is a primary key made up of two or more columns.

• The combination of those columns must be unique, even if the individual columns are not.

Why Use a Composite Primary Key?

• You use it when:

  • No single column uniquely identifies a row.

  • But the combination of columns does uniquely identify a row.

  • It is especially common in:

    • Junction tables (many-to-many relationships).

    • Bank transactions (account number + transaction number).

    • Class schedules (course_id + semester).

Junction Table (Students and Courses)

• A student can take many courses.

• A course can have many students.

• But one student cannot enroll in the same course twice.

CREATE TABLE students_courses (
student_id INT NOT NULL,
course_id INT NOT NULL,
PRIMARY KEY (student_id, course_id)
);

Order Items (E-commerce)

• An order can have many items.

• An item can appear multiple times across orders.

• But within one order, the combination of order_id and product_id is unique.

CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
price DECIMAL(10,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);

Class Offered in Terms

• A course can be offered in multiple terms.

• Each term has multiple courses.

CREATE TABLE course_offerings (
course_id INT NOT NULL,
term VARCHAR(10) NOT NULL,
instructor VARCHAR(255),
PRIMARY KEY (course_id, term)
);

• The same course appears twice, but not in the same term.

course_id	term	instructor
101	2025-Fall	John
101	2025-Winter	Ana

Warehouse Stock

• Each warehouse stores many products.

• A product exists in many warehouses.

• But the stock record is uniquely identified by: warehouse_id + product_id.

CREATE TABLE warehouse_stock (
warehouse_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
PRIMARY KEY (warehouse_id, product_id)
);

Employee Assignment

• Employees can work on many projects.

• Projects have many employees.

• But an employee cannot be assigned to the same project twice.

CREATE TABLE employee_project (
employee_id INT NOT NULL,
project_id INT NOT NULL,
assigned_on DATE NOT NULL,
PRIMARY KEY (employee_id, project_id)
);

Composite Key Rules

Rule	Explanation
Must be unique together	Student 1 + Course 100 appears only once
Each column may repeat	student_id 1 can appear many times
All columns are NOT NULL	Because PK can't contain NULL
Better for junction tables	Prevents duplicates

What Are Surrogate Keys?

• A surrogate key is an artificially created primary key that does not come from real-world data.

• Examples: Auto-increment numbers, UUIDs, and Database sequences.

• They exist only to uniquely identify a row, not to represent business logic.

Why Use Surrogate Keys?

• They solve common problems:

  • Natural keys may change like emails and usernames.

  • Natural keys may not be guaranteed unique.

  • Natural keys may be large or composite, for instance student_id and course_id.

  • Surrogate keys simplify foreign keys and joins.

  • Surrogate keys make ORMs like Hibernate / JPA easier because identity tracking needs a stable and immutable key.

  tip

ORM or Object-Relational Mapping is a framework which allows a developer to work on a relational database by using object based programming instead of using SQL statements. Instead of the developer writing boilerplate code, it handles SQL statement generation, mapping columns to object fields, and converting the resultset into an object.

:::

Types of Surrogate Keys

AUTO_INCREMENT

• These generate an increasing integer automatically.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

Pros

• Very fast

• Easy to read

Cons

• Not safe in distributed systems.

• Not globally unique across databases.

Sequences

• Explicit sequence objects that generate numbers

• Common in PostgreSQL and Oracle.

-- TODO: MYSQL code to create sequence

• When to use:

  • When you want full control over id generation.

  • When multiple tables share the same sequence.

  • When you want gaps or custom patterns.

UUID (Universally Unique Identifier)

• A 128-bit identifier, globally unique across:

  • Servers, Databases, Regions and Offline systems.

• Used heavily in microservices and distributed systems.

CREATE TABLE sessions (
id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
user_id INT,
created_at TIMESTAMP DEFAULT NOW()
);

Pros

• Unique across servers

• Great for APIs

• No collisions

Cons

• Larger storage

• Harder to index efficiently than integers

When to Use Which Type?

Use Case	Best Type
Simple app, single DB	AUTO_INCREMENT
Large enterprise DB	Sequences
Distributed systems / Microservices	UUID
External APIs where ids must not reveal count or sequence	UUID
Legacy systems	Integer-based keys

-- With a SURROGATE KEY
CREATE TABLE products (
id AUTO_INCREMENT PRIMARY KEY,
sku VARCHAR(50) UNIQUE,
name VARCHAR(255)
);

-- With a NATURAL KEY
CREATE TABLE products (
sku VARCHAR(50) PRIMARY KEY,
name VARCHAR(255)
);

The natural key works but...

• If SKU format changes, it might break your PK.

• Hard to link foreign keys.

• Larger PK = slower joins

Surrogate key Summary

Surrogate Key Type	Pros	Cons
AUTO_INCREMENT	Fast, simple	Not globally unique
Sequence	More control, flexible	Slightly more complex
UUID	Globally unique, safe for distributed systems	Bigger, slower to index

Star vs Snowflake Schemas

• Data warehouses use special database designs to organize large amounts of analytical data efficiently. Two of the most common designs are:

  • Star Schema.

  • Snowflake Schema.

• Both are used in Business Intelligence (BI), reporting, and OLAP systems but they differ in structure and normalization.

Star Schema

• A Star Schema has:

  • One central FACT table.

  • Multiple dimension tables directly connected to the fact table.

  • Dimension tables are denormalized (Contain redundant data for simplicity and speed).

• Best for dashboards, BI tools, and fast analytics.

TODO Star Schema

FACT_Sales

sale_id	date_id	product_id	store_id	customer_id	quantity	total_price

DIM_Product

product_id	name	category	brand

DIM_Store

store_id	store_name	region

DIM_Date

date_id	date	month	year

DIM_Customer

customer_id	first_name	last_name	loyalty_level

Star Schema Pros

• Very fast for reporting.

• Simple to understand.

• Fewer joins.

• Most common in BI tools (Power BI, Tableau, Looker).

Star Schema Cons

• Dimension tables may contain redundant data (Denormalized).

Snowflake Schema

• A Snowflake Schema is a more normalized version of the Star Schema.

• Dimension tables are broken into sub-tables.

• Reduces redundancy but increases number of joins.

• Best when storage cost matters, data integrity is crucial, or dimensions are large and complex.

FACT_Sales

sale_id	date_id	product_id	store_id	customer_id	quantity	total_price

DIM_Product (Normalized)

product_id	name	category_id	brand_id

DIM_Category

category_id	category_name

DIM_Brand

brand_id	brand_name

DIM_Store

store_id	store_name	region_id

DIM_Region

region_id	region_name

DIM_Date

date_id	day	month_id	year

DIM_Month

month_id	month_name	quarter

Snowflake Schema Pros

• Removes redundancy (Normalized).

• Saves storage.

• Maintains data integrity.

Cons

• More complex.

• Slower for reporting due to additional joins.

• Harder for beginners to understand.

Star vs Snowflake Comparison

Feature	Star Schema	Snowflake Schema
Dimension tables	Denormalized	Normalized
Number of joins	Few	Many
Query performance	Faster	Slower
Storage usage	Higher	Lower
Complexity	Simple	Complex
Best for	BI tools, dashboards	Advanced data warehouses