Session 02: Database Basics — SQL, NoSQL, and Schema

Part 1: Inside a database

Last session, you saw the server query the database to return data to the client. Today we open that "kitchen door" — how data gets stored, why changing its structure costs time, and the queries a PM needs to be able to read.

What a database is — and why it's not just Excel

Every product you're a PM for has a database behind it. The product catalog, orders, user info, transaction history — it all lives there.

Picture a database as a set of records managed by an extremely careful clerk. That clerk never lets you fill in the wrong box, never lets two people share the same record number, and refuses any file missing required information. Excel can't do that — you can happily type "abc" into the phone-number cell and Excel will store it without complaint.

A relational database organizes data into tables. Each table has a fixed structure: column names, data types, and constraints. Constraints are the most important part — they guarantee data integrity without a developer having to check it by hand.

For example: a UNIQUE constraint on the email column means the database automatically rejects anyone trying to create an account with an email that already exists. No validation logic needed in the code.

CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    email      VARCHAR(255) NOT NULL UNIQUE,
    name       VARCHAR(100) NOT NULL,
    phone      VARCHAR(20),
    created_at TIMESTAMP DEFAULT NOW()
);

Read this line by line: id auto-increments and is the primary key — every row has a unique id, like a sequence number that never repeats. email is required and can't be duplicated. name is required. phone can be left blank. created_at fills in the current time automatically if you don't pass one.

Relationships between tables

Real data is spread across multiple tables. A typical e-commerce order involves at least four:

• The users table: who placed the order
• The orders table: which order, total amount, status
• The order_items table: which products the order contains, and how many
• The products table: the details of each product

The links between tables are made with foreign keys. Simply put: the user_id column in the orders table is a reference slip — it points back to the id column in the users table and says "this order belongs to user X." The database guarantees you can't create an order for a user that doesn't exist — just as you can't write a customer's name on a contract if that customer isn't in the system yet.

When a PM asks for a feature like "send an order confirmation email with the list of products," the developer has to write a query that JOINs all four of those tables together. A feature that's simple on the user's side can be a complex query on the engineering side.

Part 2: SQL — the language a PM needs to read

The four core statements

SQL (short for Structured Query Language) is how we talk to a database. You don't need to write SQL, but you do need to read it when a dev or data analyst shares one.

SQL has four basic operations, usually abbreviated CRUD — Create, Read, Update, Delete:

-- CREATE: add new data
INSERT INTO products (name, price, stock)
VALUES ('iPhone 15', 22000000, 45);

-- READ: read data
SELECT name, price FROM products WHERE stock > 0;

-- UPDATE: change data
UPDATE products SET price = 21500000 WHERE id = 123;

-- DELETE: remove data
DELETE FROM products WHERE id = 123;

A PM needs to understand that DELETE is irreversible without a backup. When an incident is "we accidentally deleted data," this is usually the statement behind it. That's why every serious system has a "soft delete" — instead of deleting for real, it just marks the record as deleted and hides it.

The analytics query a PM runs into

Say you want to see daily revenue over the last 30 days. The query looks like this:

-- Revenue by day over the last 30 days
SELECT
    DATE(created_at)    AS ngay,
    COUNT(id)           AS so_don,
    SUM(total_price)    AS doanh_thu
FROM orders
WHERE
    status = 'completed'
    AND created_at >= NOW() - INTERVAL '30 days'
GROUP BY ngay
ORDER BY ngay DESC;

In plain English: "From the orders table, take the creation date + count the orders + sum the total. Keep only orders with status 'completed' that were created in the last 30 days. Group the results by day. Sort from most recent day first."

When a data analyst or dev shares this query in a meeting, you can read it and confirm the logic actually matches the business requirement — instead of nodding along without understanding and then getting the wrong result.

Part 3: SQL vs NoSQL — when to use which

SQL: right for structured data

SQL databases (PostgreSQL, MySQL, SQLite) are the best default choice for most products. They fit best when:

• The data has clear relationships — for example, users, orders, and products are tightly linked
• You need to guarantee integrity — there can't be an order not tied to any user
• You need transactions (atomic operations): for example, a money transfer has to debit account A and credit account B at the same time. If either step fails, the whole transaction is cancelled — you can't debit and then fail to credit
• The team knows SQL and the tooling is mature

NoSQL: right for flexible data or high throughput

NoSQL databases (MongoDB, Redis, DynamoDB) solve a different problem. They fit best when:

• The data structure changes often and can't be predicted in advance — for example, product configs where each type has different fields
• You need very high write/read throughput: system logs, login sessions, caches
• You need to scale horizontally — add many small servers instead of one ever-more-powerful one

Redis is the classic example of a purpose-built NoSQL store: you wouldn't use it to hold orders, but it's great for login sessions and temporary carts — because those need extremely fast reads/writes, and the data can be lost without serious consequences.

The cost of switching

Here's what PMs often overlook: once you've picked a database type and you have real data, switching between SQL and NoSQL mid-production is a major engineering project — typically 2–6 months with a team of 3–5. The data has to be migrated, every query in the codebase has to be rewritten, and both systems have to run in parallel during the transition so nothing is lost.

Ask this question at the very start, before writing the first line of code.

Part 4: Schema, Migration, and Index

Schema — a blueprint you can't change on a whim

Schema is the database's blueprint: which tables, which columns, which data types, which constraints. Picture the schema as the architectural drawings of a building that people already live in. You can't just knock down a wall or add a load-bearing column while people are inside — you need to plan carefully, give notice, and have a fallback.

Once there's data in production, changing the schema becomes a migration — and migrations carry real risk.

Why adding a column isn't as simple as it sounds:

When you ask to "add a loyalty_tier field to the user profile," the developer has to do more than "add a column":

1. Write a migration script to add the column to the table
2. Decide the default value for all 5 million existing users — what tier are they?
3. Backfill the old data: update 5 million rows with the default — this can take hours
4. Test on a staging environment with production-like data
5. Plan a maintenance window, or use a zero-downtime migration technique
6. Prepare a rollback plan in case something goes wrong midway

Questions a PM should ask before any data-related feature:

• Does this feature change the schema?
• If so, do we need to backfill old data?
• Does the migration need downtime? How long?
• What's the rollback plan if the migration fails?

Index — why queries get slower as you grow

This is one of the most common production problems whose cause a PM rarely knows: the feature runs fine at 10,000 users, slows down past 100,000, and times out entirely at 1 million. The cause is usually a missing index.

Imagine you need to find the name "Nguyễn An" in a 10-million-page directory. Without an index, you flip through every page from the start — that's a full table scan, how a database works with no index. But with an alphabetical index, you jump straight to the "N" section and find it in seconds — that's how an index works.

-- Add an index to speed up queries by user_id
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Add a composite index for a common filter + sort query
CREATE INDEX idx_orders_status_date ON orders(status, created_at DESC);

Indexes aren't free: they take up extra storage and slow down INSERT/UPDATE a little, because the database has to update the index too whenever new data arrives. It's like having to update the directory's index every time you add a new name. So adding an index needs a specific reason — you don't slap one on every column.

A sign an index is needed that a PM can recognize: a query filters (WHERE), sorts (ORDER BY), or joins (JOIN) on a column that has no index, and that table already holds millions of rows.

Further reading

• PostgreSQL Documentation — Data Types: The official reference on PostgreSQL data types — helps you understand why a dev picks VARCHAR(255) vs TEXT, or INT vs BIGINT.
• Use The Index, Luke — A Guide to Database Performance for Developers: An in-depth guide to indexing written for non-DBAs. The "The Where Clause" section is especially useful for a PM who wants to understand why a query is slow.
• Supabase Docs — Database: The Supabase docs — the tool you'll use in the hands-on part. Read "Tables and Data" and "Database Functions."
• SQLBolt — Learn SQL with simple, interactive exercises: A free, interactive SQL course with nothing to install. Lessons 1–6 are enough for a PM.
• MongoDB vs PostgreSQL: When to Use Each: MongoDB's official comparison — read it to understand the perspective of both the SQL and NoSQL camps.