MySQL Database Fundamentals & Design

TL;DR

You'll walk into the interview able to explain normalization with real examples, defend index choices using how B+trees actually work, and reason about transactions and isolation levels without hand-waving. You'll know the difference between a clustered and secondary index in InnoDB. You'll be able to write and explain a schema, not just recite definitions.

1. The Mental Model

A database is a set of promises about your data: no duplicates you didn't intend, no invalid states, and predictable behavior when multiple people touch it at once. Normalization is about keeping those promises structurally. Indexes are about keeping queries fast without breaking those promises. Transactions are about keeping promises when things go wrong mid-operation. A well-designed database is just a set of guarantees enforced by structure instead of by hope.

2. The Core Material

Normalization: Removing Redundancy Without Losing Information

Photo by Ron Lach on Pexels

Normalization is a series of rules that stop your data from lying to itself. Each "normal form" fixes a specific kind of redundancy.

1NF (First Normal Form): Every column holds a single atomic value — no comma-separated lists, no repeating groups.

Bad:

Orders(order_id, customer_name, products)
1, "Sam", "Pen, Notebook, Eraser"

Fixed — split into a child table:

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_name VARCHAR(100));
CREATE TABLE order_items (
  item_id INT PRIMARY KEY AUTO_INCREMENT,
  order_id INT,
  product VARCHAR(100),
  FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

2NF: Applies to tables with composite primary keys. Every non-key column must depend on the whole key, not just part of it.

Bad — order_id + product_id is the key, but product_name only depends on product_id:

OrderItems(order_id, product_id, product_name, quantity)

Fixed — pull product_name into its own products table keyed by product_id alone.

3NF: No non-key column depends on another non-key column (no transitive dependency).

Bad — zip_code determines city, so city doesn't belong here:

Customers(customer_id, zip_code, city)

Fixed:

CREATE TABLE zip_codes (zip_code VARCHAR(10) PRIMARY KEY, city VARCHAR(100));
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  zip_code VARCHAR(10),
  FOREIGN KEY (zip_code) REFERENCES zip_codes(zip_code)
);

BCNF is a stricter version of 3NF for edge cases with overlapping candidate keys — mention it exists, but 3NF is what most interviews actually probe.

When to denormalize on purpose: read-heavy analytics dashboards, reporting tables, caching aggregate counts. Say this in the interview — it shows judgment, not just rule-following. Denormalization trades write complexity and redundancy for read speed.

flowchart TD
    A["Unnormalized: repeating groups, comma lists"] -->|"split repeating data into rows"| B["1NF: atomic values"]
    B -->|"remove partial key dependency"| C["2NF: non-key cols depend on whole key"]
    C -->|"remove transitive dependency"| D["3NF: non-key cols depend only on key"]
    D -->|"deliberate tradeoff for read speed"| E["Denormalized: cached/aggregated for performance"]

Indexing: How MySQL Actually Finds Rows

Photo by Element5 Digital on Pexels

InnoDB (MySQL's default engine) stores tables as clustered indexes. That means the primary key IS the table — rows are physically stored in primary-key order inside a B+tree. Every other index (a "secondary index") stores the indexed column(s) plus the primary key value, then does a second lookup ("bookmark lookup") into the clustered index to fetch the full row.

This matters practically: if your primary key is a random UUID, every insert causes page splits all over the B+tree because rows aren't inserted in order — this is a real, common production performance bug. An auto-incrementing integer PK keeps inserts sequential and fast.

-- Composite index — order matters!
CREATE INDEX idx_lastname_firstname ON employees(last_name, first_name);

-- This query USES the index (leftmost prefix rule)
SELECT * FROM employees WHERE last_name = 'Smith';

-- This query does NOT use the index efficiently
SELECT * FROM employees WHERE first_name = 'John';

The leftmost prefix rule: a composite index on (a, b, c) can be used for lookups filtering on a, a+b, or a+b+c, but not b alone or c alone, because the B+tree is sorted by a first.

EXPLAIN is your best friend in an interview and in real debugging:

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

Watch for type: ALL (full table scan — bad) versus type: ref or range (index used — good), and check rows (estimated rows scanned).

Transactions & ACID

Photo by Pineapple Supply Co. on Pexels

ACID = Atomicity, Consistency, Isolation, Durability. Say what each means in one breath if asked:
- Atomicity: all statements in a transaction succeed or none do.
- Consistency: the database moves from one valid state to another (constraints hold).
- Isolation: concurrent transactions don't see each other's half-finished work.
- Durability: once committed, data survives a crash.

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK if either UPDATE fails

Isolation levels control what "seeing each other's work" means, trading consistency for concurrency:

*MySQL's REPEATABLE READ prevents phantom reads via next-key locking, which is stricter than the SQL standard requires — a great interview flex fact.

3. Worked Example

Scenario: design a schema for a blog with users, posts, and tags (many-to-many), then write a query that needs an index.

CREATE TABLE users (
  user_id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) UNIQUE NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE posts (
  post_id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT NOT NULL,
  title VARCHAR(200) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE tags (
  tag_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) UNIQUE NOT NULL
);

-- Junction table resolves the many-to-many relationship (this IS 3NF in action)
CREATE TABLE post_tags (
  post_id INT,
  tag_id INT,
  PRIMARY KEY (post_id, tag_id),
  FOREIGN KEY (post_id) REFERENCES posts(post_id),
  FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);

Why a junction table instead of a tags column on posts? Because a comma-separated tag list violates 1NF — you couldn't efficiently query "all posts tagged 'mysql'" or enforce tag uniqueness.

Now the query an interviewer loves: "find the 5 most recent posts by a specific user, with their tags."

SELECT p.post_id, p.title, p.created_at, GROUP_CONCAT(t.name) AS tags
FROM posts p
LEFT JOIN post_tags pt ON pt.post_id = p.post_id
LEFT JOIN tags t ON t.tag_id = pt.tag_id
WHERE p.user_id = 42
GROUP BY p.post_id
ORDER BY p.created_at DESC
LIMIT 5;

Without an index, WHERE p.user_id = 42 triggers a full scan of posts if the table is large. Fix:

CREATE INDEX idx_posts_user_created ON posts(user_id, created_at);

This composite index lets MySQL jump straight to user_id = 42 rows (already sorted by created_at within that group thanks to leftmost-prefix ordering), avoiding a separate filesort for the ORDER BY. Run EXPLAIN before/after and you'd see type change from ALL to ref, and Extra drop Using filesort. That before/after story is exactly what an interviewer wants to hear — not just "add an index," but why this specific composite index and column order solves this specific query pattern.

4. Production Pitfalls & Best Practices

4.1 Real-World Best Practices

Photo by Following NYC on Pexels

• Always index foreign key columns. MySQL doesn't do this automatically for InnoDB the way some engines do — an unindexed FK means every join or cascade delete does a full scan.
• Use EXPLAIN before shipping any non-trivial query. Catching a full table scan in code review is free; catching it in production is an incident.
• Prefer AUTO_INCREMENT integer PKs over UUIDs for InnoDB tables with heavy writes — sequential inserts avoid page-split thrashing. If you need UUIDs for distributed uniqueness, consider UUID v7 (time-ordered) instead of v4 (random).
• Wrap multi-step writes in explicit transactions, even in application code using an ORM — don't rely on autocommit for anything that touches more than one table.
• Use utf8mb4, not utf8, for character sets — MySQL's utf8 is a legacy 3-byte encoding that can't store emoji or some CJK characters.
• Set appropriate NOT NULL and DEFAULT constraints at the schema level — don't push all validation into application code where it can be bypassed.

4.2 Common Bugs & Anti-Patterns

N+1 queries in a loop:

-- BAD: one query per post, run in app code
SELECT * FROM posts WHERE user_id = 42;
-- then for each post: SELECT * FROM post_tags WHERE post_id = ?;

-- FIXED: one query with a join
SELECT p.*, t.name FROM posts p
LEFT JOIN post_tags pt ON pt.post_id = p.post_id
LEFT JOIN tags t ON t.tag_id = pt.tag_id
WHERE p.user_id = 42;

Leading wildcard kills index use:

-- BAD: can't use an index, forces full scan
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- FIXED: index-friendly if searching prefixes
SELECT * FROM users WHERE email LIKE 'john%';
-- For suffix search, use a FULLTEXT index or a reversed-string column instead

Storing money as FLOAT:

-- BAD: floating point rounding errors compound
CREATE