SQL and Relational Databases: A Practical Guide to Design, Queries, and PostgreSQL

A relation is mathematically a set of tuples defined over named attributes and their domains. In everyday database terminology, these roughly correspond to tables, rows, columns, and permitted values. A candidate key is a minimal set of attributes that uniquely identifies a tuple; one candidate key can be designated the primary key. Foreign keys express references between tables. For example, an order’s customer_id can reference a customer’s primary key, preventing an order from pointing to a nonexistent customer.

Relational algebra describes operations that transform relations into other relations. Selection filters tuples, projection chooses attributes, and joins combine related tuples. SQL expresses closely related operations: SELECT customer_id FROM orders WHERE status = 'paid'; filters orders before returning customer identifiers. However, SQL does not exactly reproduce mathematical set semantics. Query results normally retain duplicates unless DISTINCT removes them, and result order is unspecified without ORDER BY. Confusing these details produces incorrect reports even when queries execute successfully.

SQL also introduces NULL to represent missing or unknown information. Comparisons involving NULL generally evaluate to UNKNOWN rather than TRUE or FALSE, and WHERE retains only TRUE conditions. Consequently, discount = NULL does not find missing discounts; discount IS NULL does. COUNT(*) counts rows, whereas COUNT(discount) counts non-NULL values. These rules matter when translating business questions into precise expressions.

Start database design by identifying the facts the application must preserve. Customers have identities and contact details; orders belong to customers; order lines record purchased quantities and prices. Avoid putting every fact into one wide table. If customer email appears on every order line, changing an email requires multiple updates and risks contradictions. Database normalization reduces such anomalies by decomposing relations according to functional dependencies: statements that one attribute set determines another.

First normal form requires values drawn from the intended domains rather than repeating column groups. Second normal form removes partial dependencies of non-prime attributes on candidate keys; third normal form further restricts dependencies. Formally, for each nontrivial dependency X → A, third normal form requires X to be a superkey or A to be prime, meaning part of a candidate key. BCNF is stricter: every determinant of a nontrivial functional dependency must be a superkey.

Suppose enrollment(student, course, instructor) follows two rules: each student-course pair determines an instructor, and each instructor teaches exactly one course. Both (student, course) and (student, instructor) are candidate keys. The dependency instructor → course violates BCNF because instructor alone is not a superkey. Decomposing into instructor_course and student_instructor gives a lossless decomposition, but may make the student-course-to-instructor rule harder to enforce directly. Normalization decisions must consider dependency preservation, not just smaller tables.

A useful schema captures invariants in the database rather than relying exclusively on application code. Here is a compact starting point: CREATE TABLE customers (customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE); CREATE TABLE orders (order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint NOT NULL REFERENCES customers(customer_id), ordered_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP, status text NOT NULL CHECK (status IN ('pending', 'paid', 'cancelled'))); Every order now requires an existing customer and a permitted status.

Order lines need their own key and validation: CREATE TABLE order_items (order_id bigint NOT NULL REFERENCES orders(order_id), line_no integer NOT NULL CHECK (line_no > 0), quantity integer NOT NULL CHECK (quantity > 0), unit_price numeric(12,2) NOT NULL CHECK (unit_price >= 0), PRIMARY KEY (order_id, line_no)); The composite primary key permits multiple lines per order but forbids duplicate line numbers within one order. NOT NULL is necessary because a CHECK constraint alone does not reject an UNKNOWN result caused by NULL.

Choose types for semantics, not convenience. Numeric provides exact decimal arithmetic suitable for monetary amounts; real and double precision are approximate. A production money model also needs an explicit currency policy. PostgreSQL timestamptz represents an instant and displays it in the session time zone; it does not preserve the original zone name. Store additional zone information when local scheduling rules require it. Likewise, decide explicitly whether email uniqueness should ignore case.

To calculate revenue per customer, join orders to their lines and aggregate the line amounts: SELECT o.customer_id, SUM(i.quantity * i.unit_price) AS revenue FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id WHERE o.status = 'paid' GROUP BY o.customer_id ORDER BY revenue DESC, o.customer_id; If Ada has paid orders totaling 150 and 50, while Ben has one totaling 80, the result contains Ada with 200 and Ben with 80. Pending orders contribute nothing.

The central correctness question is the query’s grain: what does one intermediate row represent? After this join, each row represents an order line, not an order. COUNT(*) therefore counts lines. Use COUNT(DISTINCT o.order_id) to count orders, or aggregate lines to one row per order before joining other one-to-many relationships. Joining order lines and payments directly can multiply rows and inflate totals; pre-aggregate each relationship to the required grain first.

Outer joins introduce another common trap. To include customers without paid orders, start from customers and LEFT JOIN orders with o.status = 'paid' inside the ON condition. Putting that condition in WHERE removes NULL-extended rows and defeats the intended inclusion. After joining order_items, COALESCE(SUM(i.quantity * i.unit_price), 0) can display zero revenue. Always decide whether an empty result means zero, unknown, or not applicable before replacing NULL.

Common table expressions make multi-stage logic easier to inspect. For example: WITH order_totals AS (SELECT o.order_id, o.customer_id, SUM(i.quantity * i.unit_price) AS amount FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id WHERE o.status = 'paid' GROUP BY o.order_id, o.customer_id) SELECT customer_id, SUM(amount) AS revenue, AVG(amount) AS average_order_value FROM order_totals GROUP BY customer_id; Ada’s average is 100, not an average of individual line values. Orders without lines are excluded here; validate whether that matches the business definition.

SQL window functions calculate across related rows without collapsing them. Using the same order_totals CTE, replace its final SELECT with: SELECT order_id, customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS customer_revenue, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_id) AS order_rank FROM order_totals; Each order remains visible alongside its customer’s total. The order_id tie-breaker makes row numbering deterministic when amounts match.

For a chronological running total, carry ordered_at into the order-level dataset and use SUM(amount) OVER (PARTITION BY customer_id ORDER BY ordered_at, order_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). An explicit frame states which rows contribute. Window ordering does not guarantee final output ordering, so add an outer ORDER BY when presenting results. These standard SQL techniques transfer broadly, although functions and optimizer behavior differ between database systems.

ACID transactions provide atomicity, consistency, isolation, and durability. Atomicity makes a transaction’s changes commit or roll back together. Consistency means transactions preserve defined invariants when constraints and transaction logic correctly express them. Isolation controls interactions between concurrent transactions. Durability protects committed changes according to the database’s persistence configuration. None of these properties automatically defines business correctness: the database cannot enforce an unstated inventory or accounting rule.

Consider an inventory table with product_id and available columns. A safe conditional decrement is UPDATE inventory SET available = available - 2 WHERE product_id = 42 AND available >= 2 RETURNING available; No returned row means the operation did not obtain sufficient stock, or the product did not exist. Execute this statement and the associated order changes within BEGIN and COMMIT, rolling back if the decrement fails. This avoids the race created by reading availability and later issuing an unconditional update.

PostgreSQL uses multiversion concurrency control. At its default Read Committed isolation level, each statement gets a new snapshot, so two reads inside one transaction can see different committed data. More complex invariants may require row locking or Serializable isolation. Serializable transactions can fail with serialization errors; applications must retry the entire transaction safely. Keep transactions short, acquire locks consistently, and plan for deadlocks. Higher isolation is a correctness tool, not a substitute for transaction design.

Indexing strategies should follow measured query patterns. For frequent customer history queries, CREATE INDEX orders_customer_date_idx ON orders (customer_id, ordered_at DESC); can support filtering by customer and retrieving recent orders. PostgreSQL automatically creates indexes for primary keys and unique constraints, but not for referencing foreign-key columns. Additional indexes consume storage and increase write work, so avoid indexing every column reflexively.

Use EXPLAIN (ANALYZE, BUFFERS) to inspect actual execution, row estimates, loops, and buffer activity. ANALYZE executes the statement, so use caution with writes and expensive production queries. A sequential scan can be the right plan when much of a table is needed. Investigate inaccurate estimates, excessive row multiplication, and unnecessary sorting before forcing an index-based solution. High-throughput querying also depends on efficient connection use, bounded result sizes, representative testing, and maintained planner statistics.

Automated migrations should be versioned, reviewed, and tested against production-like data. Prefer expand-and-contract changes: introduce a compatible structure, deploy code that supports it, backfill carefully, validate, and retire the old structure later. Assess lock duration even for apparently small DDL changes. PostgreSQL CREATE INDEX CONCURRENTLY reduces write blocking but cannot run inside a transaction block and can leave an invalid index after failure. Migration automation must account for these exceptions and include recovery procedures.