A monolithic database design for a single application is far more flexible than most people realize, often bottlenecked by team habits rather than inherent limitations.
Let’s see what this looks like in practice. Imagine a simple e-commerce application, and we’re designing the tables for products and orders.
-- Products Table
CREATE TABLE products (
product_id UUID PRIMARY KEY,
name VARCHAR(255) NOT NULL,
description TEXT,
price DECIMAL(10, 2) NOT NULL,
stock_quantity INT NOT NULL DEFAULT 0,
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Orders Table
CREATE TABLE orders (
order_id UUID PRIMARY KEY,
user_id UUID NOT NULL, -- Assuming a 'users' table exists elsewhere
order_date TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(50) NOT NULL DEFAULT 'pending', -- e.g., pending, processing, shipped, delivered, cancelled
total_amount DECIMAL(10, 2) NOT NULL,
shipping_address JSONB, -- Storing structured address data
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);
-- Order Items Table (linking orders and products)
CREATE TABLE order_items (
order_item_id UUID PRIMARY KEY,
order_id UUID NOT NULL REFERENCES orders(order_id),
product_id UUID NOT NULL REFERENCES products(product_id),
quantity INT NOT NULL CHECK (quantity > 0),
price_at_purchase DECIMAL(10, 2) NOT NULL, -- Price of the product at the time of order
UNIQUE (order_id, product_id) -- An order can have a product only once, adjusted by quantity
);
This structure is straightforward. products holds our catalog. orders tracks customer purchases. order_items is the crucial junction table, detailing which products were in which order, and importantly, capturing the price_at_purchase to handle historical pricing.
The problem this solves is providing a single, consistent source of truth for application data. All core entities reside in one place, simplifying queries and transactional integrity. Imagine needing to fulfill an order: you grab the order details, check order_items for the products, then products for current stock (though the price_at_purchase is immutable). All within the same database.
Internally, the database engine handles the heavy lifting. When you query SELECT * FROM orders WHERE user_id = '...', the database uses indexes (like a B-tree on user_id if we added one) to quickly locate relevant rows. For order_items, foreign key constraints ensure that an order_id or product_id must exist in its respective table, preventing orphaned records. JSONB for shipping_address allows for flexible, yet queryable, structured data within a single column, leveraging GIN indexes for efficient searching if needed.
The core levers you control are indexing, data types, and constraints. Proper indexing (e.g., CREATE INDEX idx_orders_user_id ON orders(user_id);) dramatically speeds up lookups. Choosing appropriate data types (e.g., DECIMAL for currency, UUID for unique identifiers) prevents precision loss and ensures data integrity. Constraints (NOT NULL, CHECK, UNIQUE, FOREIGN KEY) enforce business rules at the database level, making the application more robust.
Many developers shy away from JSONB columns thinking they are a black box or that they lose the performance benefits of relational columns. However, PostgreSQL’s JSONB is binary, compact, and supports indexing (like GIN indexes) that can make querying nested JSON data as fast as querying traditional relational columns, offering a powerful hybrid approach within a single table.
The next logical step is to consider how to handle more complex relationships, like product variants or user-defined configurations, within this monolithic structure.