Week 6: MySQL Fundamentals & Schema Design

1. The Relational Model

A relational database stores data in tables — rows and columns, like a strict spreadsheet where every row in a table has the same set of typed columns. Tables relate to each other through keys: a orders table doesn't repeat a customer's full details on every row; it stores a customer_id that points back to a row in a customers table.

# Install MySQL (or MariaDB, a drop-in-compatible fork)
# macOS
brew install mysql && brew services start mysql

# Ubuntu/Debian
sudo apt install mysql-server

# Connect with the CLI client
mysql -u root -p

CREATE DATABASE week06_shop;
USE week06_shop;

This week uses the MySQL CLI directly — no PHP yet. The goal is thinking in tables and relationships before writing any application code against them.

2. Data Types & Keys

Common MySQL column types you'll reach for constantly:

• INT / BIGINT — whole numbers, typically for IDs and counts
• DECIMAL(10,2) — exact fixed-point numbers, essential for money (never use FLOAT for currency — rounding errors accumulate)
• VARCHAR(n) — variable-length text up to n characters
• TEXT — longer, unbounded text (descriptions, comments)
• DATETIME / TIMESTAMP — date and time values
• BOOLEAN — actually stored as TINYINT(1) under the hood

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock_count INT NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

A primary key (PRIMARY KEY) uniquely identifies each row in a table — AUTO_INCREMENT means MySQL assigns the next integer automatically. A foreign key (FOREIGN KEY ... REFERENCES) points from one table to another's primary key, and MySQL enforces that the referenced row must actually exist — you can't insert an order with a customer_id that doesn't correspond to a real customer.

3. CRUD in SQL

The four basic operations, directly in SQL — this is exactly what PDO will wrap in PHP next week:

-- Create
INSERT INTO customers (name, email) VALUES ('Ada Lovelace', 'ada@example.com');
INSERT INTO products (name, price, stock_count) VALUES ('Mechanical Keyboard', 89.99, 12);

-- Read
SELECT * FROM products WHERE price < 100 ORDER BY price ASC;
SELECT name, price FROM products WHERE stock_count > 0 LIMIT 10;

-- Update
UPDATE products SET stock_count = stock_count - 1 WHERE id = 1;

-- Delete
DELETE FROM products WHERE stock_count = 0 AND id NOT IN (
    SELECT DISTINCT product_id FROM order_items
);

4. Joins

A join combines rows from two or more tables based on a related column — this is how you turn a normalized schema (data split across tables) back into one combined result:

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- Every order with the customer's name attached
SELECT orders.id, orders.status, customers.name AS customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

-- Every order item with product details
SELECT order_items.quantity, products.name, products.price,
       (order_items.quantity * products.price) AS line_total
FROM order_items
INNER JOIN products ON order_items.product_id = products.id
WHERE order_items.order_id = 1;

-- All customers, including those with zero orders
SELECT customers.name, COUNT(orders.id) AS order_count
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
GROUP BY customers.id;

INNER JOIN only returns rows that match on both sides. LEFT JOIN returns every row from the left table regardless of whether a match exists on the right — customers with no orders still appear, with NULLs (or 0, via COUNT) for the missing order data.

5. Normalization

Normalization is the discipline of splitting data into tables so each fact is stored exactly once. Consider this un-normalized alternative to the schema above:

CREATE TABLE orders_bad (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100),   -- repeated on every order from the same customer
    customer_email VARCHAR(255),  -- repeated, and now can drift out of sync
    product_name VARCHAR(150),    -- one order, one product -- can't have 2 items
    product_price DECIMAL(10,2)
);

This design repeats customer data on every order (update their email, and you must update every historical row or leave it inconsistent), and can't represent an order with multiple products at all. Splitting into customers, products, orders and order_items — as built above — fixes both problems: each customer's data lives in exactly one row, and an order can reference any number of products through order_items.

6. Hands-on Exercise

7. Knowledge Check

Four quick questions. Expand each to check your answer.