Every database has relationships: customers→orders (1:N), students↔courses (M:N), employees→manager (self-referencing). Proper design prevents data inconsistencies.

E-Commerce Multi-Table Relationships

Amazon-scale e-commerce with complex relationships:

CUSTOMERS (customer_id PK, name, email)

ORDERS (order_id PK, customer_id FK, order_date, status)

PRODUCTS (product_id PK, name, price, inventory)

ORDER_ITEMS (order_item_id PK, order_id FK, product_id FK, quantity)

REVIEWS (review_id PK, customer_id FK, product_id FK, rating, comment)

CATEGORIES (category_id PK, name)

PRODUCT_CATEGORIES (product_id FK, category_id FK)

Proper relationships enable complex queries. CASCADE on orders → order_items ensures data integrity. M:N relationship (products ↔ categories) via junction table allows flexible categorization.

Get customer order history with items

SELECT o.order_id, o.order_date, p.name, oi.quantity, oi.quantity * p.price AS subtotal

FROM orders o

JOIN order_items oi ON o.order_id = oi.order_id

JOIN products p ON oi.product_id = p.product_id

WHERE o.customer_id = 123

ORDER BY o.order_date DESC;

Find products with high ratings

SELECT p.name, AVG(r.rating) as avg_rating, COUNT(r.review_id) as review_count

FROM products p

JOIN reviews r ON p.product_id = r.product_id

GROUP BY p.product_id, p.name

HAVING AVG(r.rating) >= 4.5

ORDER BY review_count DESC;

All

University Course Registration System

Many-to-many relationship between students and courses:

STUDENTS (student_id PK, name, email, major)

COURSES (course_id PK, course_code, name, credits)

PROFESSORS (professor_id PK, name, department)

ENROLLMENTS (enrollment_id PK, student_id FK, course_id FK, grade, semester, UNIQUE(student_id, course_id, semester))

COURSE_PROFESSORS (course_id FK, professor_id FK, semester)

Junction table ENROLLMENTS handles M:N relationship. UNIQUE constraint prevents duplicate enrollments. Can track grades, semesters. Easy to add/drop courses without data loss.

Find all courses for a student

SELECT c.course_code, c.name, e.grade, e.semester

FROM enrollments e

JOIN courses c ON e.course_id = c.course_id

WHERE e.student_id = 12345

ORDER BY e.semester DESC;