Practical Schema Design: Normalizing vs. Using JSONB

Real schema design is a series of trade-offs, not a single "correct" answer. PostgreSQL gives you both a traditional relational toolbox (tables, foreign keys, normalization) and flexible document-style tools (JSONB, arrays), and the skill of a good schema designer is knowing when to reach for each.

This topic works through a concrete example — a product catalog — to show how the types and structures covered so far (NUMERIC, TIMESTAMPTZ, JSONB, arrays, foreign keys) come together into one coherent, defensible design.

Why it matters

Real schemas are not purely relational or purely document-shaped. A product catalogue has fields that every product shares and attributes that depend on the category. Knowing how to combine normalised columns, foreign keys and a JSONB column in one table is how PostgreSQL is used in practice.

When to Normalize

Normalize into separate tables with foreign keys whenever data needs independent identity, needs to be queried or joined on its own, must enforce referential integrity, or is shared across many parent rows. A "category" that products belong to, a "customer" that places many "orders," or "order line items" that reference both an order and a product are classic normalization candidates — each deserves its own table with a primary key and foreign keys linking them.

When JSONB (or Arrays) Are Appropriate

Reach for JSONB when the shape of the data genuinely varies per row and is not queried by its internal fields in performance-critical ways — for example, a product's variable "attributes" (a laptop has RAM and screen size; a T-shirt has size and color), or a flexible "metadata" bag for future extensibility. Use a plain array column for short, denormalized lists that always belong entirely to one row, like search tags. The rule of thumb: if you find yourself writing frequent WHERE clauses or JOINs against a specific JSONB key, that key is a strong candidate to become a real, indexed column instead.

Combining Both in One Table

Most production schemas blend both approaches deliberately: core, universally-present, frequently-queried fields become typed columns with constraints (id, name, price, created_at); a small number of foreign keys capture true relationships (category_id referencing categories); and a JSONB column absorbs the genuinely variable, less-frequently-queried extras. This keeps common queries fast and simple while still allowing flexibility where it earns its cost.

Example

CREATE TABLE categories (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE products (
    id          BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    category_id INTEGER NOT NULL REFERENCES categories(id),
    name        TEXT NOT NULL,
    price       NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    tags        TEXT[] DEFAULT '{}',
    attributes  JSONB NOT NULL DEFAULT '{}',
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO categories (name) VALUES ('Laptops');
INSERT INTO products (category_id, name, price, tags, attributes)
VALUES (1, 'UltraBook 14', 999.00, ARRAY['electronics','sale'],
        '{"ram_gb": 16, "screen_inches": 14}');

CREATE TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1

Exercise

This exercise combines foreign keys with JSONB, so it needs a PostgreSQL server. Design a products table for a catalogue: a generated id; a required name; an exact, positive price; a required foreign key to a categories table; and a JSONB column attributes for details that vary by category. Then write the query that lists the name and price of products in category 1 that cost less than 500.

Expected result: the table accepts products with different attributes while still enforcing the price rule and the category reference.

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

-- TODO: create the products table

-- TODO: name and price of products in category 1 costing less than 500

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price > 0),
  category_id INTEGER NOT NULL REFERENCES categories(id),
  attributes JSONB NOT NULL DEFAULT '{}'
);

SELECT name, price
FROM products
WHERE category_id = 1 AND price < 500;

Quick quiz

Interview questions