Students enroll in multiple courses, courses have multiple students. Products belong to multiple categories. Users have multiple roles. Orders contain multiple products. Every complex system has many-to-many relationships.

E-learning Platform - Course Enrollment System

Online learning platform where students can enroll in multiple courses, and courses can have multiple students. Need to track enrollment dates, completion status, and grades.

Junction table with metadata for enrollment tracking and progress monitoring

-- Junction table with rich metadata

CREATE TABLE enrollments (

student_id INT,

course_id INT,

enrollment_date DATE,

completion_status ENUM('enrolled', 'in_progress', 'completed', 'dropped'),

grade DECIMAL(3,2),

last_accessed TIMESTAMP,

PRIMARY KEY (student_id, course_id)

);

-- Query: Students with their course progress

SELECT

s.student_name,

c.course_name,

e.enrollment_date,

e.completion_status,

e.grade,

DATEDIFF(CURRENT_DATE, e.last_accessed) as days_since_access

FROM students s

INNER JOIN enrollments e ON s.student_id = e.student_id

INNER JOIN courses c ON e.course_id = c.course_id

WHERE e.completion_status = 'in_progress'

ORDER BY days_since_access DESC;

Enables personalized learning paths, progress tracking, and identification of at-risk students who haven't accessed courses recently.

All

E-commerce Product Categorization

Products can belong to multiple categories (e.g., iPhone in Electronics, Mobile Phones, Apple Products). Need to manage category hierarchies and featured product placements.

Junction table with priority and status for flexible product categorization

-- Junction table for flexible categorization

CREATE TABLE product_categories (

product_id INT,

category_id INT,

priority INT DEFAULT 1,

is_featured BOOLEAN DEFAULT FALSE,

status ENUM('active', 'inactive') DEFAULT 'active',

created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,