System Design: Databases Fundamentals
Learn how relational and non-relational databases store data, guarantee correctness through transactions, and stay fast at scale through indexing and query optimization.
1. Relational Databases (SQL) Overview
A relational database organizes data into tables made of rows and columns. Every table follows a fixed schema, meaning each row in a table has the same set of columns with defined data types. Related tables link to each other through key values instead of duplicating data.
Structured Query Language (SQL) is the language used to define tables, insert data, and read data back out. Popular relational databases include PostgreSQL, MySQL, and SQLite.
Tables, Rows, and Columns
A table (like users or orders) stores records as rows. Each column defines a specific attribute, such as name or email, with a fixed data type like text or integer.
Joins
Because relational data is split across tables, reading related information requires a JOIN, which matches rows from one table to rows in another using a shared key value.
The schema below defines a users table and an orders table, then reads both together with a JOIN.
SQL: Defining a Relational Schema and Joining Tables
Two related tables linked through a foreign key, queried together
Press Run to execute the code and see output here.
2. Non-Relational Databases (NoSQL) Overview
Non-relational databases (commonly called NoSQL) store data without a fixed table schema. Instead of splitting related data across multiple tables, a NoSQL document database can keep related information together in a single record. This trades some structure for flexibility and faster reads on nested data.
Relational Tables vs Non-Relational Documents
System DesignComparing normalized SQL tables against a self-contained NoSQL document
Common NoSQL Categories
- Document Stores:Store JSON-like documents (MongoDB, Couchbase). Good for nested, evolving data shapes.
- Key-Value Stores:Store simple key to value pairs (Redis, DynamoDB). Good for caching and lookups.
- Wide-Column Stores:Store rows with flexible columns per row (Cassandra). Good for huge write volumes.
- Graph Databases:Store nodes and relationships directly (Neo4j). Good for deeply connected data.
When to Choose NoSQL
NoSQL fits well when data does not have a strict shape, when horizontal scale across many servers matters more than complex joins, or when read patterns mostly need one record at a time.
MongoDB: Storing Nested Data in One Document
A user document embeds its own order history, no join needed to read it
Press Run to execute the code and see output here.
3. ACID Properties
ACID is a set of four guarantees that relational databases provide for transactions, groups of operations that must succeed or fail together. These guarantees keep data correct even when a system crashes mid-operation or many transactions run at the same time.
Atomicity
A transaction is treated as a single unit. Either every operation inside it succeeds, or none of them apply. There is no partial result left behind.
Consistency
A transaction moves the database from one valid state to another, never breaking defined rules such as constraints or data types.
Isolation
Concurrent transactions do not see each other's incomplete changes. Each transaction behaves as though it is running alone.
Durability
Once a transaction commits, its changes are permanently saved, even if the server crashes or loses power immediately afterward.
A bank transfer is the classic example: money must leave one account and arrive in another as a single, all-or-nothing operation.
SQL: A Transaction That Must Be All-or-Nothing
Both balance updates commit together, or neither one applies
Press Run to execute the code and see output here.
4. BASE Properties
Many distributed NoSQL databases favor BASE instead of strict ACID guarantees. BASE trades immediate consistency for higher availability and easier scaling across many servers, accepting that data might be briefly out of date.
Basically Available
The system guarantees a response to every request, even during a partial failure, rather than blocking until every node agrees.
Soft State
The stored state may change over time even without new input, as replicas gradually sync data between themselves.
Eventual Consistency
Given enough time without new writes, all replicas of the data will converge to the same value, but not necessarily instantly.
ACID vs BASE
ACID prioritizes correctness on every read, which fits financial records and inventory counts. BASE prioritizes uptime and scale, which fits social media likes, view counts, or activity feeds where a short delay is acceptable.
5. Normalization and Denormalization
Normalization is the process of organizing tables to reduce duplicate data. Each fact is stored in exactly one place, and related facts are linked through keys. Denormalization is the opposite: intentionally duplicating data to avoid joins and speed up reads.
Normalization Benefits
- No Duplicate Data:A customer email is stored once, in the users table.
- Easier Updates:Changing an email updates a single row, not thousands.
- Trade-off:Reading combined data requires JOINs across multiple tables.
Denormalization Benefits
- Fewer Joins:A product name copied onto every order row avoids a join.
- Faster Reads:Read-heavy dashboards and reports load with fewer table lookups.
- Trade-off:Updating a duplicated value now means updating many rows.
Choosing Between Them
Start normalized to keep data consistent and easy to maintain. Denormalize specific hot paths later, once profiling shows a particular read query is slow because of repeated joins.
6. Indexing (B-Tree, Hash, Composite Index)
Without an index, finding a row means scanning every row in a table one by one. An index is a separate data structure that lets the database jump almost straight to the matching rows, similar to how a book index points to a page number instead of reading the whole book.
B-Tree Index Structure
System DesignA shallow tree of sorted key ranges leads to the exact matching rows
B-Tree Index
Keeps values sorted in a balanced tree. Supports exact matches, ranges, and sorting (WHERE, BETWEEN, ORDER BY). The default index type in most relational databases.
Hash Index
Maps each value to a bucket using a hash function. Extremely fast for exact equality lookups, but cannot support range queries or sorting.
Composite (Multi-Column) Index
Indexes two or more columns together, in a defined column order. Speeds up queries that filter on that same combination of columns, such as customer email and order status together.
SQL: Creating B-Tree, Composite, and Hash Indexes
Speeding up lookups on the orders table for common query patterns
Press Run to execute the code and see output here.
7. Primary Key, Foreign Key, Unique Constraints
Constraints are rules the database enforces automatically, rejecting any write that would break them. They keep relational data trustworthy without relying on application code to check every rule manually.
Primary Key
Uniquely identifies each row in a table. A table can only have one primary key, and its value can never be NULL or duplicated.
Foreign Key
Points to a primary key in another table, enforcing that a referenced row must actually exist. Prevents orphaned records, such as an order pointing to a deleted customer.
Unique Constraint
Ensures every value in a column (or set of columns) is distinct across the table, such as guaranteeing no two products share the same SKU.
SQL: Primary Key, Unique, and Foreign Key Constraints
The database rejects a foreign key insert that points to a missing category
Press Run to execute the code and see output here.
8. Database Transactions
A transaction groups multiple statements into one unit of work. Every statement inside it happens together, or the whole group is rolled back as if none of it happened. Transactions are started with BEGIN, made permanent with COMMIT, and undone with ROLLBACK.
BEGIN
Marks the start of a transaction block. All following statements are held in a pending state until committed or rolled back.
COMMIT
Makes every change inside the transaction permanent and visible to other connections. Cannot be undone once completed.
ROLLBACK
Discards every change made since BEGIN, returning the database to its state before the transaction started. Commonly triggered when an error occurs mid-transaction.
9. Isolation Levels
Isolation levels control how much one transaction can see of another transaction's uncommitted or concurrent changes. Stricter isolation prevents more anomalies but reduces concurrency, since the database must do more work to keep transactions from interfering with each other.
Read Uncommitted
Allows reading changes from other transactions that have not committed yet (a dirty read). Weakest isolation, rarely used in practice.
Read Committed
Only sees data that has already been committed by other transactions. The default in PostgreSQL and many production systems.
Repeatable Read
Guarantees the same query run twice inside one transaction returns the same rows, even if other transactions commit changes in between.
Serializable
The strictest level. Transactions behave exactly as if they ran one after another, with zero overlap, fully preventing concurrency anomalies.
SQL: Setting a Transaction Isolation Level
Choosing how strictly a transaction is shielded from concurrent changes
Press Run to execute the code and see output here.
10. Query Optimization Basics
A slow query usually has a specific, findable cause: a missing index, an unnecessary full table scan, or a query pulling far more columns or rows than the application actually needs. Optimization starts with measuring, not guessing.
EXPLAIN ANALYZE
Shows the actual execution plan the database chose for a query, including whether it used an index scan or a slower sequential scan, and how long each step took.
Select Only What You Need
Requesting SELECT * pulls every column, including large ones the application will discard. Naming only the required columns reduces data transferred and memory used.
Index the Right Columns
Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Too many indexes slow down writes, so index based on measured query patterns.
Limit Result Sets
Use LIMIT and pagination for large result sets instead of returning millions of rows the application will trim down anyway.
Compare the execution plan before and after adding an index on the searched column.
SQL: Reading an EXPLAIN ANALYZE Execution Plan
A sequential scan becomes an index scan once the right index exists
Press Run to execute the code and see output here.
Databases Fundamentals Knowledge Verification
1. What is the core difference between SQL and NoSQL databases?
2. What does the "I" in ACID stand for?
3. How does denormalization typically improve read performance?
4. What is the primary benefit of a B-Tree index?
5. What does a Foreign Key constraint enforce?
6. Which isolation level offers the strongest consistency guarantee?
7. What is the first step in optimizing a slow query?