Relational Database

Explanation

A relational database organizes data into tables (also called relations — hence "relational"). Each table has a fixed schema: defined columns with specific data types. Each row represents one record. Columns can have constraints: NOT NULL (required), UNIQUE, CHECK, DEFAULT, or foreign key references to other tables. The relational model's power comes from relationships between tables: a users table and an orders table are linked by orders.user_id referencing users.id. This foreign key relationship allows JOINs — queries that combine data from multiple tables. Instead of duplicating user information in every order record, you store it once in users and reference it with an ID. Normalization is the process of organizing tables to reduce data redundancy and avoid update anomalies. First Normal Form (1NF): each column stores a single value, no repeating groups. Second Normal Form (2NF): no partial dependencies on the primary key. Third Normal Form (3NF): no transitive dependencies. In practice, "properly normalized" typically means 3NF. Over-normalization creates too many small tables requiring complex JOINs; denormalization strategically duplicates data for read performance. Popular relational databases: PostgreSQL (open source, feature-rich, excellent for production web apps), MySQL (widely deployed, solid performance), SQLite (serverless, embedded, file-based — perfect for development), Microsoft SQL Server, and Oracle (enterprise). PostgreSQL is the recommended default for new projects in 2025.

Code Example

-- Relational database design: users, products, orders

CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) UNIQUE NOT NULL,
  name       VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE products (
  id          SERIAL PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents > 0),
  stock       INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total_cents INTEGER NOT NULL,
  status     VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  id         SERIAL PRIMARY KEY,
  order_id   INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL CHECK (quantity > 0),
  price_cents INTEGER NOT NULL  -- snapshot of price at time of order
);

-- Index on foreign key columns (essential for JOIN performance)
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

Why It Matters for Engineers

Relational databases are the backbone of most production applications. Understanding their design principles — normalization, foreign keys, constraints — means you can design schemas that prevent data corruption, write efficient queries, and scale appropriately. A well-designed relational schema is significantly easier to maintain than a poorly-designed one. Database design also has direct performance implications: a schema without appropriate indexes is slow, a schema with improper normalization wastes storage and creates update anomalies, and a schema with wrong data types (storing numbers as strings) breaks sorting and arithmetic. These are engineering decisions with long-term consequences.