Data and Storage

A data model describes the facts an application stores and the relationships or rules among them. Storage is the mechanism that preserves encoded data beyond one process or request. The model exists to turn a changing real-world domain into explicit records the software can validate, search, and update. Durable storage exists because process memory disappears during restarts and is not a safe owner for important facts.

Modeling starts by naming entities, their attributes, and stable identities. Next, define which attributes are required, which values are valid, and how records relate. The storage system encodes those records on durable media and maintains structures that locate them later. Reads reconstruct useful values; writes create, replace, or delete them according to the model's rules.

For a library, Book, Member, and Loan are separate concepts. A loan links one book to one member and records checkout and due dates. In practice, model product facts rather than screen layouts, distinguish durable source-of-truth data from rebuildable cache data, and plan how the model can evolve when a new requirement appears.

A relational database stores data in tables with defined columns. Each row represents one occurrence of a concept, and a primary key uniquely identifies it. A foreign key stores the primary key of a related row and can enforce that the referenced row exists. These structures reduce ambiguity and let the database protect relationships shared by many application paths.

Design begins with one table per distinct kind of fact. Give each row a stable primary key, assign column types and nullability, then connect related rows with foreign keys. A one-to-many relationship places the parent key on the child. A many-to-many relationship uses a linking table containing keys from both sides, often with a uniqueness rule to prevent duplicates.

An orders table may have id and customer_id, with customer_id referencing customers.id. An order_items table can connect orders and products while recording quantity and price at purchase time. Practical designs use constraints for rules the database can guarantee, avoid repeating mutable customer details in every order, and preserve historical values that intentionally belong to the transaction.

A query describes data to read or change. A read query filters rows, selects columns, combines related tables, orders results, and may aggregate values. An index is an additional ordered lookup structure built from selected columns. It exists to avoid examining every row when the query can use indexed values to find a small portion of a table.

On a read, the database planner considers available access paths and estimates their cost. It may scan the table or navigate an index to candidate row locations and then fetch remaining columns. A composite index orders multiple columns from left to right, so column order matters. Every index consumes space and makes writes more expensive because the database must update both table data and index entries.

For SELECT id FROM orders WHERE customer_id = ? ORDER BY created_at DESC, an index on customer_id and created_at can support filtering and ordering. Practical work starts with real slow queries and their execution plans, indexes common selective access patterns, requests only needed columns, and removes redundant indexes after measuring usage.

A transaction is a set of database operations committed or rejected as one unit. ACID summarizes useful properties: atomicity prevents partial commitment, consistency preserves declared rules, isolation controls interference between concurrent transactions, and durability keeps committed results after failure. Transactions exist because crashes and simultaneous requests can occur between any two statements.

The application begins a transaction, reads or changes rows, and asks the database to commit. The database coordinates locks or row versions, checks constraints, records enough recovery information, and makes the commit durable. If an operation fails, the application rolls back. Isolation levels choose which concurrent effects may be visible; stronger isolation can simplify reasoning but can create more waiting or retries.

Transferring account credit requires subtracting from one balance and adding to another. Both changes must commit together, and a rule should prevent an invalid balance. In practice, keep transactions focused and short, include every invariant-related change, handle deadlock or serialization retries, and never assume a transaction automatically includes an external email or payment call.

Consistency describes guarantees about which values reads may return when writes happen over time. Strong consistency commonly means a completed write is visible to later reads as if operations occurred in one order. Eventual consistency allows replicas or derived views to lag but promises convergence when updates stop. The right requirement depends on the product consequence of reading an older value.

A write may reach one authoritative node first and propagate to other nodes asynchronously. A read from a lagging replica can then return the previous value. Systems can route important reads to the leader, wait for replication, attach a version and reject stale results, or accept lag and reconcile later. Each mechanism exchanges some latency, availability, or complexity for a stronger observation guarantee.

After a user changes a profile photo, briefly seeing the previous photo may be tolerable. Reading an old account balance before approving a withdrawal is not. Practical design labels consistency per operation, offers read-your-writes behavior where user trust needs it, makes delayed states visible, and tests conflict or retry behavior rather than relying on the vague claim that the database is consistent.

Replication keeps copies of the same logical data on multiple nodes. It exists to survive a node failure, place reads nearer to users, and distribute read traffic. In a common leader-replica arrangement, one leader accepts writes and replicas apply its ordered change stream. Other systems permit multiple writers but must resolve concurrent updates.

For asynchronous replication, the leader commits locally and sends changes afterward, giving fast writes but risking lag or a small loss during sudden failover. Synchronous replication waits for another node before acknowledging, improving durability at the cost of latency and possible write unavailability. Failover promotes a suitable replica, redirects traffic, and carefully prevents two leaders from accepting conflicting writes.

A reporting dashboard can read yesterday's figures from replicas while purchases write to the leader. If a replica falls behind, its dashboard may be stale but checkout remains correct. Practical operations monitor lag, regularly test promotion and restoration, keep replicas in separate failure domains, and remember that replication copies accidental deletion too, so it is not a backup.

Sharding divides a data set across independent storage partitions, usually by a shard key. Each shard owns only part of the records, allowing aggregate storage and write capacity to exceed one node. Sharding exists for workloads that no suitable single system can handle, but it makes cross-shard queries, transactions, balancing, and recovery more difficult.

A router applies the shard function to a key such as customer_id and sends the operation to the owning shard. An effective key spreads load while keeping commonly accessed related data together. Range keys support nearby scans but can create a hot newest range; hashed keys distribute load but scatter range queries. Resharding moves ownership and data without losing or double-writing updates.

A global event service might hash tenant_id across shards so each tenant's events remain together. One unusually large tenant can still create a hotspot, requiring dedicated placement or finer partitioning. In practice, first optimize models and queries, choose storage from access patterns and guarantees, prefer mature operational knowledge, and shard only after metrics show a concrete capacity boundary.