Database Concepts

A relational database stores data in tables of rows and columns. Keys connect the tables, so each fact lives in one place and queries join them back together.

• Primary key: a column that uniquely identifies each row.
• Foreign key: a column that points at a row in another table. The database can reject values with no match.
• One-to-many: one customer has many orders, so orders hold the customer id.
• Many-to-many: students and courses, which need a join table in the middle.

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Normalization organises tables so each fact is stored once. That prevents anomalies like changing a customer's email in one row and missing the other twenty.

• 1NF: each cell holds one value, with no lists or repeating groups.
• 2NF: every non-key column depends on the whole primary key, not part of a composite key.
• 3NF: non-key columns depend only on the key, not on each other.
• Denormalization: copying data on purpose to speed up reads. Fair enough once you have measured a need and accept keeping the copies in sync.

A transaction groups several steps into one unit that either fully happens or never happened. Moving money is the classic example: the debit and the credit must succeed together.

• Atomicity: all the steps commit, or all of them roll back.
• Consistency: constraints that held before the transaction still hold after it.
• Isolation: concurrent transactions do not trip over each other, to the degree the isolation level promises.
• Durability: once committed, the data survives a crash, usually thanks to a write-ahead log.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

Stronger isolation gives you fewer surprises but allows less concurrency. The SQL standard defines each level by the anomalies it rules out.

• Dirty read: you see another transaction's uncommitted change.
• Non-repeatable read: reading the same row twice gives different values.
• Phantom read: running the same query twice returns new rows.
• Real engines differ: PostgreSQL defaults to read committed and MySQL InnoDB to repeatable read, and each can be stricter than the standard requires.

An index is a sorted lookup structure, like the index at the back of a book. Without one the database scans every row. With one it jumps to the right place.

• B-tree: the usual default. Handles equality, ranges and sorting.
• Hash: very fast for equality, no help with ranges.
• Composite: an index on (country, city) helps filters on country or on both, but usually not on city alone.
• The cost: every index uses disk space and slows down writes.

CREATE INDEX idx_orders_customer ON orders (customer_id);

Replication keeps copies of your data on several servers. You gain availability and read capacity, at the cost of copies that can lag behind.

• Primary and replicas: one node takes writes, the others follow it and serve reads.
• Synchronous: the primary waits for a replica to confirm. Safer, slower.
• Asynchronous: the primary confirms straight away. Faster, but a crash can lose the latest writes.
• Replication lag: a user may not see their own change on a replica, so read from the primary when it matters.

When one server cannot hold the data or keep up with the writes, you split the data. Partitioning divides a table into pieces, and sharding puts those pieces on different servers.

• Range: A to H here, I to P there. Great for range queries, but risks hot spots.
• Hash: hash the key to choose a shard. Spreads data evenly, but a range query touches every shard.
• Shard key: the most important choice, and painful to change later.
• The cost: joins and transactions across shards become slow or hard.

In a distributed database, a network partition will happen sooner or later. CAP says that during one you must choose: refuse some requests to stay consistent, or answer them and risk stale data.

• Consistency: every read sees the latest write.
• Availability: every request to a working node gets a response.
• Partition tolerance: the system keeps running when nodes cannot reach each other.
• Eventual consistency: replicas converge once writes stop. Fine for like counts, not for bank balances.
• PACELC: even with no partition, you still trade latency against consistency.

NoSQL is not one thing. Each family gives up some relational features to be very good at one shape of data.

• Key-value: Redis, DynamoDB. Fast lookups by key for caches and sessions.
• Document: MongoDB. JSON-like records for data that varies in shape.
• Wide-column: Cassandra. Huge write volumes, read by a known partition key.
• Graph: Neo4j. Relationships come first, for networks and recommendations.