More in Backend Dev — page 54
Write-Ahead Logging (WAL): Survive Crashes by Journaling First
Think of it as a journal of intentions. Before changing data, a database writes the intended change to a log file first. This ensures that if the system crashes, it can recover by replaying the log, guaranteeing no writes are lost.
Lock Now or Check Later: Optimistic vs. Pessimistic Concurrency
Pessimistic concurrency locks data first, assuming conflict is likely ('ask permission'). Optimistic concurrency proceeds without locks and checks for conflicts before saving, assuming they're rare ('ask forgiveness'). Use pessimistic for high-contention.
Fourth Normal Form (4NF): Isolating Independent Facts
4NF prevents storing independent, multi-valued facts in one table. It applies when a key relates to two unrelated lists, like a restaurant's pizza types and its delivery areas.
Materialized Views: Pre-computing Slow Queries
A materialized view trades data freshness for query speed by storing the result of a slow query as a physical table. It's ideal for dashboards that run heavy aggregations, making them load instantly. The footgun is stale data: users see old results.
Denormalization: Trading Write Speed for Faster Reads
Denormalization speeds up database reads by intentionally adding redundant data, trading write-speed for read-performance. Use it for read-heavy systems like reporting dashboards where joins are too slow.
Surrogate Keys: Stable IDs for Unstable Data
A surrogate key is a meaningless, system-generated ID that never changes, unlike a 'natural' key (like an email) which can. Use it for stable joins between tables. The footgun is exposing these internal IDs in public URLs, which leaks data structure.
Third Normal Form (3NF): Nothing But The Key
Third Normal Form (3NF) dictates that every non-key column must describe 'nothing but the key.' This prevents transitive dependencies, where one non-key column determines another.
Functional Dependency: The Rules Behind Your Data
A functional dependency is a rule: if you know column A, you uniquely know column B. It's the logic behind primary keys and is used for database normalization to prevent data duplication.
Junction Table: Connecting Many to Many
A junction table is a bridge between two other tables, enabling a many-to-many relationship. It's used to connect students to classes or tags to articles. The common footgun is forgetting a unique constraint, allowing duplicate connections.
First Normal Form (1NF): No Nested Data
First Normal Form (1NF) means every table cell holds one single value, not a list. It's the first rule of relational databases, stopping you from storing comma-separated strings. The footgun is stuffing multiple values into one field, which breaks SQL queries.
Entity-Relationship Diagrams: A Blueprint for Your Data
An Entity-Relationship Diagram (ERD) is a blueprint for a database, showing what you're storing and how it connects. It's used before writing code to design a relational database schema. The main footgun is designing the model to match the UI, not the data.
Boyce-Codd Normal Form (BCNF): Stricter Than 3NF
BCNF is a database design rule stricter than 3NF, ensuring no attribute is determined by anything but a superkey. It's used to eliminate all data redundancies from functional dependencies, but can force trade-offs with dependency preservation.
Codd's 12 Rules: The Relational Database Litmus Test
Codd's 12 Rules are a litmus test for whether a database is truly "relational." They demand that all data and operations be managed purely through the relational model, not through lower-level system access.
Relational Algebra: The Math Behind SQL Queries
Relational algebra is the formal logic behind SQL, treating tables as mathematical sets. It provides a grammar for operations like joins and filters, allowing a database to translate your declarative query into a precise, optimizable execution plan.
Transaction Isolation Levels: The Concurrency vs. Correctness Dial
Think of isolation levels as a database dial trading transaction correctness for raw concurrency. You tune this when balancing performance against the risk of data anomalies. The footgun: the default level isn't always the safest; you must know its guarantees.
Database Views: A Saved Query That Acts Like a Table
A database view is a saved query you interact with like a real table. It simplifies complex joins for applications or restricts data access for security, showing only specific rows or columns.
Database Index: The Phonebook for Your Data
An index is like a book's index, letting you jump to the right row without scanning the whole table. It's essential for columns used in WHERE clauses, but over-indexing slows writes since every index must be updated on data changes.
Foreign Keys: The Glue of Relational Databases
A foreign key is a pointer from one table to another, linking related data like a user to their posts. It enforces referential integrity, ensuring a post can't exist without a valid user. The footgun is forgetting this blocks deletions of referenced records.
Database Normalization: Tidy Tables, Less Redundancy
Database normalization organizes data into smaller, related tables to eliminate redundancy, like separating addresses from orders. It's the standard for transactional (OLTP) systems where data integrity is critical.
ACID: The Four Guarantees of Database Transactions
ACID is a contract for database transactions: all-or-nothing guarantees for data validity, even during crashes. It's critical for relational databases in finance or e-commerce. The footgun is assuming all databases offer this; many NoSQL systems don't.