All bites
The whole library, newest first. Filter by what you are here for, or pick a topic if you already know.
4247 bites
Page 206
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Second Normal Form (2NF)
2NF ensures every non-prime attribute depends on the entire candidate key, not just part of it. It only matters when a relation has a composite key. The footgun is assuming single-attribute keys automatically satisfy 2NF.
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.
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.
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.
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.
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.
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.
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.
Database Transaction Log: Your System's Safety Net
A transaction log is your database's safety journal. Before changing data, it records the intended action in a durable file. This is vital for crash recovery, ensuring data isn't left corrupt. The footgun: its primary role is integrity, not just auditing.
Shared & Exclusive Locks: The Read vs. Write Rule
A Shared (S) lock is like many people reading a library book at once; an Exclusive (X) lock is one person writing in it alone. Databases use S/X locks to manage concurrency, preventing writes from corrupting reads.