MySQL Mastery: Relational Fundamentals and Setup

Mental Model

A relational database is like a collection of highly organized, interlinked spreadsheets where data integrity and structure are mathematically guaranteed.

In a world increasingly obsessed with NoSQL, MySQL remains the undisputed king of the backend. It powers everything from WordPress blogs to massive platforms like Uber and GitHub. Why? Because when money is on the line, you need the strict mathematical guarantees of the Relational Model.

1. The Core Philosophy of Relational Databases

At its core, MySQL organizes data into Tables (relations). Each table has Columns (attributes) and Rows (tuples). The power of MySQL comes from Relationships: you don't store a user's address directly inside their profile if multiple users can share an address. You store it in an addresses table and link them. This is called Normalization.

2. Choosing the Right Data Types

One of the most common mistakes junior engineers make is using the wrong data types. This wastes memory and slows down indexes.

• Numbers: TINYINT (1 byte, max 255), INT (4 bytes, max 2.1B), BIGINT (8 bytes, max 9 Quintillion). Always use the smallest one that fits.
• Text: VARCHAR(255) (variable length, up to 255 chars, indexed easily). TEXT (massive strings, cannot be easily indexed).
• Time: DATETIME (8 bytes, stores absolute time) vs TIMESTAMP (4 bytes, automatically converts to UTC based on server timezone).

3. Creating Your First Production Schema

Let's design a simple E-commerce schema. We need Users and Orders.

-- Create the Users table
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create the Orders table
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('PENDING', 'SHIPPED', 'DELIVERED') DEFAULT 'PENDING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Notice the Details:

1. BIGINT AUTO_INCREMENT: We use BIGINT because an e-commerce site will easily surpass 2.1 billion orders eventually.
2. DECIMAL(10, 2): Never use FLOAT or DOUBLE for money. DECIMAL ensures exact mathematical precision.
3. FOREIGN KEY ... ON DELETE CASCADE: If a user deletes their account, the database will automatically delete all their orders, preventing orphaned records.

4. Basic CRUD Operations

Create (Insert):

INSERT INTO users (email) VALUES ('john@example.com');

Read (Select):

SELECT id, email FROM users WHERE email = 'john@example.com';

Update:

UPDATE orders SET status = 'SHIPPED' WHERE id = 1;

Delete:

DELETE FROM users WHERE id = 1;

Practice Question

Scenario: You are designing a table to store blog post views. A post can have billions of views. Question: What data type should you use for the views_count column?

Technical Trade-offs: Architectural Decision

Production Readiness Checklist

Before deploying this architecture to a production environment, ensure the following Staff-level criteria are met:

• High Availability: Have we eliminated single points of failure across all layers?
• Observability: Are we exporting structured JSON logs, custom Prometheus metrics, and OpenTelemetry traces?
• Circuit Breaking: Do all synchronous service-to-service calls have timeouts and fallbacks (e.g., via Resilience4j)?
• Idempotency: Can our APIs handle retries safely without causing duplicate side effects?
• Backpressure: Does the system gracefully degrade or return HTTP 429 when resources are saturated?

Verbal Interview Script

Interviewer: "Why did you choose DECIMAL instead of FLOAT for the price column?"

Candidate: "Primitive floating-point types like FLOAT and DOUBLE use IEEE 754 binary approximations, which means they cannot accurately represent base-10 fractions like $0.10. Over thousands of transactions, this precision loss compounds, leading to financial inaccuracies. DECIMAL stores the exact numeric value as a string representation internally, guaranteeing 100% precision for currency."