A marketplace is the schema design problem most teams get wrong first, because it looks like two-sided CRUD and is actually a ledger. Buyers and sellers are the easy part. Money movement, order state, and the fact that a listing's price can change after someone has already bought it are where a naive schema starts corrupting data.
What tables does a marketplace schema need?
At minimum: users, seller profiles, listings, orders, order items, and payments. Six tables. Everything else — reviews, messaging, disputes, payouts — hangs off those. The most important decision is that an order must snapshot the price at purchase time rather than referencing the live listing price.
-- Users are one table. A seller is a role, not a separate person.
CREATE TABLE users (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
email citext NOT NULL UNIQUE,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE seller_profiles (
user_id uuid PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
display_name text NOT NULL,
payout_status text NOT NULL DEFAULT 'pending'
CHECK (payout_status IN ('pending','verified','suspended'))
);
CREATE TABLE listings (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
seller_id uuid NOT NULL REFERENCES users(id),
title text NOT NULL,
price_cents integer NOT NULL CHECK (price_cents >= 0), -- minor units, never float
currency char(3) NOT NULL,
status text NOT NULL DEFAULT 'draft'
CHECK (status IN ('draft','active','sold','archived')),
created_at timestamptz NOT NULL DEFAULT now()
);Why must an order snapshot the price?
Because a listing is mutable and an order is a historical fact. If orders join to listings for price, a seller editing their price silently rewrites every past order total, and revenue reporting changes retroactively. Copy the price onto the order row at purchase time.
CREATE TABLE orders (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
buyer_id uuid NOT NULL REFERENCES users(id),
status text NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','paid','shipped','completed','refunded','cancelled')),
total_cents integer NOT NULL CHECK (total_cents >= 0),
currency char(3) NOT NULL,
placed_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
order_id uuid NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
listing_id uuid NOT NULL REFERENCES listings(id),
-- frozen at purchase: what the buyer actually agreed to
unit_price_cents integer NOT NULL CHECK (unit_price_cents >= 0),
quantity integer NOT NULL CHECK (quantity > 0),
title_snapshot text NOT NULL
);How should money be stored?
As integer minor units — cents — never as float or real. Floating point cannot represent 0.10 exactly, so sums drift. Use integer or bigint for the amount and a separate char(3) ISO 4217 currency column. Postgres numeric is also correct but slower; integers are simpler to reason about.
The three constraints most marketplaces omit
A listing cannot be bought by its own seller. A foreign key cannot express this; enforce it with a trigger or at write time.
An order's currency must match every item's currency. Otherwise you sum GBP and USD into one total_cents.
Only active listings can be ordered. Enforce in the database, not in application code alone.
Which indexes does a marketplace actually need?
Index every foreign key you filter or join on, plus the columns behind your two hottest queries: a buyer's order history and a seller's active listings. Postgres does not index foreign keys automatically — only primary keys and unique constraints. This is the most common cause of a marketplace slowing down as it grows.
-- Foreign keys are NOT indexed automatically in Postgres
CREATE INDEX ON order_items (order_id);
CREATE INDEX ON order_items (listing_id);
CREATE INDEX ON orders (buyer_id, placed_at DESC);
-- Partial index: the seller dashboard only ever queries active listings
CREATE INDEX ON listings (seller_id, created_at DESC) WHERE status = 'active';How do you handle deletes without breaking order history?
Never hard-delete a listing that has orders against it. Set status to archived instead. The foreign key from order_items.listing_id exists so an order can still be traced to its origin; deleting the listing would either cascade away order history or fail the constraint. Archiving keeps both the audit trail and referential integrity.
What to check before the first migration ships
Four checks, each mapping to a class of bug that is expensive to fix once real data exists. Run them before the schema reaches production.
- Every foreign key you filter or join on has an index.
- Every money column is an integer of minor units, paired with a currency column.
- Every status column has a CHECK constraint listing its allowed values.
- Every historical record snapshots the values it depends on rather than joining to mutable rows.
Frequently Asked Questions
Should I use UUIDs or bigint IDs for a marketplace?
UUIDs, generated with gen_random_uuid(). They let you create records across services without coordination, and they do not leak volume the way a sequential integer does — a competitor can read your order count off an incrementing ID. The index-size cost is real but small next to that.
Do I need a separate sellers table?
No. A seller is a role a user holds, so keep one users table and attach a seller_profiles row for seller-specific columns. Duplicating identity into a second table creates two sources of truth for email and login.
How do I model multi-currency?
Store the amount as integer minor units plus an ISO 4217 currency code on the same row, and never sum across currencies in SQL. Conversion belongs in the application or a reporting layer with an explicit, dated exchange rate.
When should I denormalize?
After you have a measured slow query, not before. The usual first denormalization in a marketplace is a cached rating average or order count on seller_profiles, refreshed by trigger or a scheduled job.
References
- Data Definition — Constraints — PostgreSQL Documentation
- Indexes — PostgreSQL Documentation
- Data Types — PostgreSQL Documentation
About Jishu Labs
Jishu Labs is a software development company founded in 2016. We build custom software, AI/ML systems, and full-stack web and mobile applications for clients, and we make eight AI tools for software teams.