Joins, Relationships, Constraints, Normalization, and Practical Data Modeling

Relational Thinking

The true power of a relational database appears when related tables can be joined meaningfully. Customers place orders. Orders contain items. Students enroll in courses. Employees belong to departments. Instead of repeating the same text in many rows, PostgreSQL lets you model relationships cleanly and query them later.

Primary Keys and Foreign Keys

CREATE TABLE order_items (
    order_item_id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(order_id),
    product_id BIGINT NOT NULL REFERENCES products(product_id),
    quantity INTEGER NOT NULL,
    unit_price NUMERIC(10,2) NOT NULL
);

Foreign keys protect referential integrity. They prevent invalid references such as an order item pointing to a missing order or product.

Joining Tables for Useful Outputs

SELECT
    o.order_id,
    c.full_name,
    o.order_status,
    o.order_date
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_status = 'PENDING';

Joins are essential for dashboards, APIs, reports, and operational back-office queries. Understanding them deeply is a major milestone in SQL learning.

Normalization and Design Tradeoffs

Normalization helps reduce unnecessary duplication. Customer details belong in a customer table, not copied into every order. At the same time, some duplication is intentional, such as storing unit price in an order item for historical correctness. Advanced design means understanding when normalization helps and when carefully chosen denormalization serves performance or audit goals.