Stage 3 · Build

Relational Databases (PostgreSQL)

The Relational Model

Tables, rows, columns, primary keys, foreign keys, and normalization basics.

7 min readDatabases for SREBuild

What Is the Relational Model?

The relational model organizes data into tables (relations) with rows (tuples) and columns (attributes). Proposed by Edgar F. Codd in 1970, it remains the foundation of PostgreSQL, MySQL, and most production databases. The model enforces data integrity through constraints and supports powerful query languages like SQL.

Unlike file-based storage, the relational model separates logical data organization from physical storage. You define what data means; the database engine decides how to store and retrieve it efficiently.

Tables, Rows, and Columns

A table represents an entity type — users, orders, transactions. Each row is a single instance of that entity. Each column defines a specific attribute with a declared data type.

Creating a users table


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

INSERT INTO users (email, name) VALUES
  ('alice@example.com', 'Alice Chen'),
  ('bob@example.com', 'Bob Smith');

The SERIAL type creates an auto-incrementing integer. UNIQUE and NOT NULL are constraints that enforce data integrity at the database level.

Tables are sets

In the relational model, a table is a set of rows — order is not guaranteed. Never rely on row ordering unless you use an explicit ORDER BY clause.

Primary Keys

A primary key uniquely identifies each row in a table. PostgreSQL automatically creates an index on the primary key, making lookups fast. Primary keys cannot be NULL and must be unique.

Primary key strategies


-- Auto-incrementing integer (most common)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total DECIMAL(10, 2) NOT NULL
);

-- UUID for distributed systems
CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INTEGER NOT NULL,
    expires_at TIMESTAMP NOT NULL
);

-- Composite primary key
CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Choose SERIAL for single-database systems. Use UUIDs when you need to generate IDs across multiple services without coordination. Composite keys work well for many-to-many relationships.

Foreign Keys

Foreign keys create explicit relationships between tables. They enforce referential integrity — you cannot insert a row that references a non-existent parent row. Foreign keys also enable JOIN queries across related data.

Foreign key relationships


CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    author_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    body TEXT,
    published_at TIMESTAMP
);

-- This fails because user 999 does not exist
INSERT INTO posts (author_id, title) VALUES (999, 'Hello');

-- This succeeds
INSERT INTO posts (author_id, title) VALUES (1, 'My First Post');

ON DELETE CASCADE means deleting a user also deletes all their posts. Other options include ON DELETE RESTRICT (prevent deletion) and ON DELETE SET NULL.

Foreign keys have performance implications

Foreign keys require index lookups on both the parent and child table during writes. In high-throughput write-heavy systems, some teams defer foreign key checks or omit them entirely, relying on application-level integrity instead.

Normalization Basics

Normalization organizes tables to reduce data redundancy and improve integrity. The most common forms are First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF).

Form	Rule	Example Violation
1NF	Each column holds atomic values	Storing multiple emails in one column
2NF	No partial dependencies on composite keys	Storing product name alongside order_item composite key
3NF	No transitive dependencies	Storing customer zip code which determines city

Normalization in practice


-- Denormalized: city stored with every order
CREATE TABLE orders_bad (
    id SERIAL PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_city VARCHAR(100)  -- transitive dependency
);

-- Normalized: city lives in customers table
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(100)
);

CREATE TABLE orders_good (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    total DECIMAL(10, 2)
);

Normalization eliminates update anomalies. If a customer moves, you update one row in the customers table instead of every order they placed.

Choosing Data Types

PostgreSQL offers rich data types. Choosing the right type affects storage, performance, and query flexibility. Use the smallest type that fits your data — it directly impacts index size and memory usage.

Type	Use Case	Storage
INTEGER	IDs, counts, small numbers	4 bytes
BIGINT	Large counters, timestamps as epoch	8 bytes
DECIMAL(10,2)	Money, precise numbers	Variable
VARCHAR(255)	Variable-length text with limit	Actual length + overhead
TEXT	Unlimited text, descriptions	Actual length + overhead
TIMESTAMP	Dates with time, audit columns	8 bytes
BOOLEAN	Flags, status indicators	1 byte

Use TEXT over VARCHAR when length is unbounded

In PostgreSQL, TEXT and VARCHAR(255) have identical performance. Use TEXT when you do not need a length constraint — it avoids arbitrary limits and is more idiomatic in PostgreSQL.

Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.