Databases
RDBMS — Relational Databases
Relational database fundamentals — tables, keys, relationships, normalization, and SQL — with schema examples and comparison tables.
- RDBMS
- SQL
- Database Design
What is an RDBMS?
A Relational Database Management System (RDBMS) stores data in tables (relations) made of rows and columns, where relationships between tables are defined through keys rather than nested documents. PostgreSQL, MySQL, SQLite, and Oracle are all RDBMS engines.
Core Building Blocks
| term | meaning |
|---|---|
| Table | A collection of rows with the same columns (schema) |
| Row / Record | A single entry in a table |
| Column / Field | A named, typed attribute shared by every row |
| Primary Key | Uniquely identifies each row in a table |
| Foreign Key | A column referencing another table's primary key |
| Index | A structure that speeds up lookups on a column |
A Simple Schema
Two related tables — authors and books — connected by a foreign key:
Relationship Types
- One-to-Many — one author can write many books (shown above).
- Many-to-Many — many books can have many tags; needs a junction table (
book_tags) with two foreign keys. - One-to-One — a user and their profile settings, split for organization or optional data.
Normalization, Briefly
Normalization organizes columns and tables to minimize redundancy:
| form | rule | fixes |
|---|---|---|
| 1NF | Atomic columns, no repeating groups | Multi-valued columns |
| 2NF | 1NF + no partial dependency on a composite key | Partial key dependency |
| 3NF | 2NF + no transitive dependency on non-key columns | Derived/indirect data |
| BCNF | Every determinant is a candidate key | Edge cases 3NF misses |
Denormalization (intentionally duplicating data) is sometimes used for read-heavy systems to trade storage for query speed — it’s a deliberate trade-off, not a mistake.
ACID Properties
RDBMS transactions guarantee:
- Atomicity — a transaction fully succeeds or fully rolls back.
- Consistency — the database moves between valid states only.
- Isolation — concurrent transactions don’t interfere with each other.
- Durability — once committed, data survives crashes.
When to Reach for an RDBMS
Structured data with clear relationships, a need for strong consistency (banking, inventory, orders), and complex queries joining multiple entities — an RDBMS is usually the right default before reaching for something more specialized.