Designing a Real Schema for a Small App

You've learned tables, types, relationships, joins, aggregates, indexes, normalization, transactions, and how to inspect a query plan. This final lesson pulls everything together by designing a complete, realistic schema from scratch.

Naming The Real World Things

Let's design a small app: a simple online bookstore. Start by listing the real-world "things" involved, in plain English, before writing any SQL: customers, books, orders, and the individual items within an order. This naming step matters more than it looks; rushing straight to SQL syntax before nailing down what each table actually represents is how messy schemas happen.

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

CREATE TABLE books (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    author VARCHAR(150) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    stock INT UNSIGNED NOT NULL DEFAULT 0
);

Notice UNIQUE on email, a constraint you haven't used explicitly before: it means MySQL rejects any insert that would create a duplicate email, enforcing "one account per email" directly at the database level, rather than only trusting your application code to check first, the same defense-in-depth philosophy behind foreign keys from earlier lessons.

Orders And Order Items

Next, orders and order_items, following the one-to-many and many-to-many patterns from the relationships lesson:

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

CREATE TABLE order_items (
    order_id INT NOT NULL,
    book_id INT NOT NULL,
    quantity INT UNSIGNED NOT NULL DEFAULT 1,
    price_at_purchase DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, book_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (book_id) REFERENCES books(id)
);

Notice price_at_purchase, deliberately duplicated from books.price, exactly the intentional exception discussed in the normalization lesson: an order placed last year should always show last year's price, even if the book's price has since changed. This isn't sloppy design, it's a conscious, correct decision.

Also notice we didn't store total on the orders table itself. It can always be computed on demand with SUM(quantity * price_at_purchase) across order_items, so storing it separately would just create a second place that could drift out of sync with reality, exactly the kind of redundancy normalization warns against. (In a genuinely high-traffic system, you might cache a computed total for speed, but that's a deliberate performance tradeoff made later, not a default starting choice.)

A Realistic Transaction

Now let's put the whole system to work with a realistic, multi-step scenario, wrapped in a transaction exactly as covered earlier, since placing an order touches three tables at once and must succeed or fail as one unit:

START TRANSACTION;

INSERT INTO customers (name, email) VALUES ('Vikesh Singh', 'vikesh@example.com');

INSERT INTO orders (customer_id, status) VALUES (LAST_INSERT_ID(), 'pending');

INSERT INTO order_items (order_id, book_id, quantity, price_at_purchase)
VALUES (LAST_INSERT_ID(), 3, 2, 499.00);

UPDATE books SET stock = stock - 2 WHERE id = 3;

COMMIT;

A Realistic Reporting Query

And a realistic reporting query pulling together everything you've learned, joins, aggregates, and a readable CTE:

WITH order_totals AS (
    SELECT order_id, SUM(quantity * price_at_purchase) AS total
    FROM order_items
    GROUP BY order_id
)
SELECT customers.name, orders.id AS order_id, order_totals.total
FROM orders
JOIN customers ON customers.id = orders.customer_id
JOIN order_totals ON order_totals.order_id = orders.id
ORDER BY order_totals.total DESC
LIMIT 5;

That single query, "top 5 highest-value orders with customer names," uses a CTE, a JOIN, an aggregate SUM, and ORDER BY with LIMIT, every major tool from this entire course working together naturally, because they were always meant to be combined, not used in isolation.

A closing practical tip, more mindset than syntax: good schema design rarely comes from getting it perfect on the first attempt. It comes from starting with honest, simple tables that match reality, using constraints (NOT NULL, UNIQUE, foreign keys) to let the database itself catch mistakes early, and being willing to adjust the design as you genuinely learn more about how the app is actually used in practice.

Try It Yourself

Worth trying right now: extend this bookstore schema with a reviews table, where customers can leave a rating and comment on books they've purchased, deciding deliberately which columns are required, which relationships need foreign keys, and whether a customer should be allowed to review a book they never actually ordered.