Databases &amp; Architecture

DBMS: The Software That Runs Your Database

A DBMS is the software engine that manages a database, separating your app from raw data files. You use it to query, update, and administer data, like with PostgreSQL or MySQL. The common footgun is confusing the DBMS with the database itself.

DDL: The Blueprint for Database Objects

DDL is the blueprint for your database. You reach for it when spinning up new tables, indexes, or user permissions, not when querying rows. The footgun is running DROP thinking you are deleting data, not vaporizing the entire table structure.

DML: Insert, Update, and Delete

DML is the change-focused subset of database languages like SQL that adds, modifies, and removes data. It appears in every write to a table. The footgun is assuming SELECT belongs in DML; read-only querying is sometimes split out as DQL instead.

Primary Keys: The Unique ID for Every Row

A primary key is like a social security number for a database row, uniquely identifying it. It's used to reliably fetch records and link related data across tables. The footgun is using a natural key, like an email address, that might change over time.

intermediate1 min read

The Relational Model: Data as Simple Tables

The relational model organizes data into simple tables (relations) of rows (tuples), forming the foundation of SQL databases. It's used for structured data where consistency is critical, like in banking.

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.

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.

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 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.