Table relationships

Introduction

First, let us look at the concept of the three basic relationships in a Relational Database defined based on the fundamental nature of Cardinality links between two tables:

One-to-One Relationship (1-1)

A record in Table A is linked to exactly one record in Table B and vice versa.
Implemented by placing a Foreign Key in one of the two tables and assigning it a UNIQUE constraint.
Examples:
- A User has only one UserProfile.
- A Product has only one ProductDetail.
When to use:
- When splitting an oversized table containing rarely used columns to optimize data read performance.
- When security is required: Separating sensitive information such as credit cards or passwords into a dedicated table with stricter access controls.

One-to-Many Relationship (1-N)

A record in Table A can be linked to multiple records in Table B. Conversely, a record in Table B is linked to only one record in Table A.
Implemented by placing a Foreign Key in the "Many" side table (Table B) pointing to the Primary Key of the "One" side table (Table A).
Examples:
- A Customer can place multiple Orders, but an Order belongs to exactly 1 Customer.
- A Category contains multiple Products, but a Product belongs to only 1 primary Category.
This is the most common relationship in practice, used for modeling data ownership hierarchies or grouping.

Many-to-Many Relationship (N-N)

A record in Table A can be linked to multiple records in Table B and vice versa.
Relational databases cannot directly link two tables in an N-N fashion, so a Junction/Pivot Table must be used to split the N-N relationship into two 1-N relationships. The junction table contains two Foreign Keys pointing to the two main tables.
Examples:
- An Order contains multiple Products and a Product can appear in multiple different Orders, requiring an OrderItem junction table.
- A Student registers for multiple Courses and a Course has multiple Students participating, requiring an Enrollment junction table.
Used when two data entities interact independently and mutually cross-reference each other.

In advanced database design practice, based on these three core relationships, PostgreSQL supports additional specialized relationships according to data characteristics or business requirements.

Self-Referencing / Recursive Relationship

A table contains a Foreign Key pointing back to its own Primary Key. Used to represent hierarchical, tree, or graph structures.
1-N Self-referencing: Tree structure / Parent-child categories (Category Tree, Employee - Manager).
N-N Self-referencing: Network / Graph structure (Social network friends, substitute products).
Postgres heavily supports querying hierarchical structures via WITH RECURSIVE (Common Table Expressions - CTE) or the ltree extension.

Polymorphic Relationship

Occurs when a single table shares a relationship with multiple distinct tables via the same set of FK columns.
Example: A comments table can belong to posts, videos or products.
Implementation involves design patterns like creating junction tables or parent inheritance structures.

Inheritance / Specialization (IS-A Relationship)

An entity is a specialized version of another entity (e.g., Admin IS-A User, Car IS-A Vehicle).
Implementation strategies:
- Class Table Inheritance (Creating 1-1 relationships): The users table holds common attributes, while the admins table holds a 1-1 FK pointing to users containing specialized attributes.
- Postgres Native Inheritance using INHERITS: A unique Postgres feature.
  - Allows child tables to automatically inherit all columns from the parent table.
  - Worked well in older Postgres versions, but due to limitations regarding Foreign Key constraints across tables, it has been superseded from Postgres 10+ by Declarative Partitioning (PARTITION BY).

Temporal / Historical Relationship

A record in Table A links to a record in Table B at a specific timestamp or during a given time range.
Example: An employee belongs to a Department (1-N), but department assignment history changes over time.
Postgres supports Range data types (daterange, tsrange) combined with the following strategies to prevent overlapping time ranges without complex triggers:
- Creating Exclusion Constraints (EXCLUDE USING gist): A generalized solution supported across older versions.
- Using WITHOUT OVERLAPS supported from Postgres version 18, featuring a simpler syntax that enables constraint enforcement on Foreign Keys, which Exclusion Constraints cannot accomplish.

Detail

CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT uuidv7(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    user_type VARCHAR(20) NOT NULL CHECK (user_type IN ('CUSTOMER', 'ADMIN')),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
    user_id UUID PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
    loyalty_points INT DEFAULT 0,
    shipping_address TEXT
);

CREATE TABLE admins (
    user_id UUID PRIMARY KEY REFERENCES users(user_id) ON DELETE CASCADE,
    role_level VARCHAR(50) NOT NULL,
    can_approve_refund BOOLEAN DEFAULT FALSE
);

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    parent_id INT REFERENCES categories(category_id) ON DELETE SET NULL,
    category_name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE products (
    product_id UUID PRIMARY KEY DEFAULT uuidv7(),
    category_id INT REFERENCES categories(category_id),
    name VARCHAR(255) NOT NULL,
    sku VARCHAR(100) UNIQUE NOT NULL,
    base_price NUMERIC(12, 2) NOT NULL CHECK (base_price >= 0),
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE product_price_history (
    price_id SERIAL,
    product_id UUID NOT NULL REFERENCES products(product_id) ON DELETE CASCADE,
    price NUMERIC(12, 2) NOT NULL CHECK (price >= 0),
    valid_range DATERANGE NOT NULL,
    PRIMARY KEY (product_id, valid_range WITHOUT OVERLAPS)
);

CREATE TABLE product_details (
    product_id UUID PRIMARY KEY REFERENCES products(product_id) ON DELETE CASCADE,
    technical_specs JSONB,
    warranty_months INT DEFAULT 12,
    origin_country VARCHAR(100)
);

CREATE TABLE orders (
    order_id UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id UUID NOT NULL REFERENCES customers(user_id),
    total_amount NUMERIC(12, 2) NOT NULL,
    status VARCHAR(50) DEFAULT 'PENDING',
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id UUID REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id UUID REFERENCES products(product_id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(12, 2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE reviews (
    review_id UUID PRIMARY KEY DEFAULT uuidv7(),
    customer_id UUID NOT NULL REFERENCES customers(user_id),
    target_type VARCHAR(20) NOT NULL CHECK (target_type IN ('PRODUCT', 'SELLER')),
    target_id UUID NOT NULL,
    rating INT CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customer_addresses (
    address_id SERIAL PRIMARY KEY,
    customer_id UUID NOT NULL REFERENCES customers(user_id) ON DELETE CASCADE,
    recipient_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NOT NULL,
    full_address TEXT NOT NULL,
    is_default BOOLEAN DEFAULT FALSE
);

One-to-One: products and product_details separate heavy or rarely queried technical data (like technical_specs) from the primary table to optimize read queries.
One-to-Many: customers and customer_addresses, since a customer can own multiple shipping addresses (Home, Office, etc.).
Many-to-Many: orders and products, using order_items as a junction table because orders contain multiple products and products appear in multiple orders.
Self-Referencing: categories, building an infinitely nested multi-level tree structure (a category can contain sub-categories recursively).
Polymorphic: reviews and products / users
- Reviews can belong to a Product OR a User.
- Creating a single unified reviews table avoids redundant table structures like product_reviews and seller_reviews.
Inheritance: users and customers / admins, centralizing authentication information while separating business logic.
- Designed using Class Table Inheritance via 1-1 FK, which is simpler and more effective than relying on the INHERITS clause.
- The parent table (users) stores shared identity attributes, while child tables (customers / admins) store role-specific details.
Historical: product_price_history tracks price fluctuations over time (daterange), serving promotional pricing and revenue reporting.
- Preserves price history over time and prevents overlapping price ranges for the same product.
- The UUID data type (product_id) natively supports B-Tree Index but lacks operator classes for GiST, requiring the btree_gist extension (built into Postgres) to enable standard types (UUID, INT, TEXT...) to interact with GiST index.
- Overlaps can be prevented using two methods:
  - Creating a Constraint EXCLUDE USING GiST: Supported across almost all Postgres versions.
  - Creating a PRIMARY KEY with WITHOUT OVERLAPS: A modern approach supported starting from Postgres 18.

The ERD for the tables is shown below:

Query usage examples:

SELECT 
    u.user_id,
    u.email,
    u.full_name,
    c.loyalty_points,
    json_agg(
        json_build_object(
            'address_id', addr.address_id,
            'recipient', addr.recipient_name,
            'address', addr.full_address,
            'is_default', addr.is_default
        )
    ) AS addresses
FROM users u
JOIN customers c ON u.user_id = c.user_id
LEFT JOIN customer_addresses addr ON c.user_id = addr.customer_id
WHERE u.user_id = 'c1a2b3c4-0000-0000-0000-000000000001'
GROUP BY u.user_id, c.loyalty_points;

WITH RECURSIVE CategoryTree AS (
    SELECT category_id, parent_id, category_name, slug, 1 AS level
    FROM categories
    WHERE category_id = 1 
    
    UNION ALL
    
    SELECT c.category_id, c.parent_id, c.category_name, c.slug, ct.level + 1
    FROM categories c
    JOIN CategoryTree ct ON c.parent_id = ct.category_id 
)
SELECT * FROM CategoryTree ORDER BY level, category_id;

SELECT 
    r.review_id,
    r.rating,
    r.comment,
    u.full_name AS reviewer_name,
    r.created_at
FROM reviews r
JOIN users u ON r.customer_id = u.user_id
WHERE r.target_type = 'PRODUCT'
  AND r.target_id = '590211cd-b85b-498f-8b63-3d5ff8ea602d'
ORDER BY r.created_at DESC;

SELECT 
    p.product_id,
    p.name,
    p.sku,
    ph.price AS current_price,
    pd.warranty_months,
    pd.technical_specs->>'ram' AS ram_spec
FROM products p
JOIN product_details pd ON p.product_id = pd.product_id
JOIN product_price_history ph ON p.product_id = ph.product_id
WHERE p.product_id = '590211cd-b85b-498f-8b63-3d5ff8ea602d'
  AND ph.valid_range @> CURRENT_DATE;

Query 1: Fetches complete Customer information (Inheritance & 1-N) including general attributes, loyalty points and address list.

Query 2: Retrieves the category tree top-down (Self-Referencing) using WITH RECURSIVE to fetch category_id=1 along with all child categories.

Query 3: Looks up Product Reviews (Polymorphic) to get review entries for a specific Product alongside reviewer details.

Query 4: Fetches Product Details along with current price valid today (1-1 & Historical).

Joins the Product table, 1-1 Technical Details table and Historical Price table to obtain active pricing.
Uses the @> operator to verify whether today falls within valid_range.