Marketplace Schema

database-schemas · SQL + ERD viewer
View Resource Back to Lab

Diagram Views

erDiagram users ||--|| seller_profiles : owns seller_profiles ||--o{ listings : publishes users ||--o{ orders : places orders ||--o{ order_items : contains listings ||--o{ order_items : sold_as seller_profiles ||--o{ order_items : fulfills seller_profiles ||--o{ payouts : receives users { bigint id PK text email } seller_profiles { bigint id PK bigint user_id FK text store_name text status } listings { bigint id PK bigint seller_id FK text title int price_cents } orders { bigint id PK bigint buyer_id FK text status int total_cents } order_items { bigint id PK bigint order_id FK bigint listing_id FK bigint seller_id FK int quantity } payouts { bigint id PK bigint seller_id FK int amount_cents text status }

SQL Snippets

schema.sql 
create table users (
  id bigint primary key generated always as identity,
  email text not null unique,
  full_name text not null,
  created_at timestamptz not null default now()
);

create table seller_profiles (
  id bigint primary key generated always as identity,
  user_id bigint not null unique references users(id) on delete cascade,
  store_name text not null,
  status text not null check (status in ('active','suspended','pending')),
  created_at timestamptz not null default now()
);

create table listings (
  id bigint primary key generated always as identity,
  seller_id bigint not null references seller_profiles(id) on delete cascade,
  title text not null,
  price_cents integer not null check (price_cents >= 0),
  quantity_available integer not null default 0 check (quantity_available >= 0),
  status text not null check (status in ('draft','active','archived')),
  created_at timestamptz not null default now()
);

create table orders (
  id bigint primary key generated always as identity,
  buyer_id bigint not null references users(id),
  status text not null check (status in ('pending','paid','fulfilled','cancelled')),
  total_cents integer not null default 0 check (total_cents >= 0),
  created_at timestamptz not null default now()
);

create table order_items (
  id bigint primary key generated always as identity,
  order_id bigint not null references orders(id) on delete cascade,
  listing_id bigint not null references listings(id),
  seller_id bigint not null references seller_profiles(id),
  quantity integer not null check (quantity > 0),
  unit_price_cents integer not null check (unit_price_cents >= 0)
);

create table payouts (
  id bigint primary key generated always as identity,
  seller_id bigint not null references seller_profiles(id),
  amount_cents integer not null check (amount_cents >= 0),
  status text not null check (status in ('pending','paid','failed')),
  period_start date not null,
  period_end date not null,
  created_at timestamptz not null default now()
);

create index idx_listings_seller_status on listings(seller_id, status);
create index idx_orders_buyer_created on orders(buyer_id, created_at desc);
create index idx_order_items_seller on order_items(seller_id);
seed.sql 
insert into users (email, full_name)
values
  ('buyer1@market.dev', 'Buyer One'),
  ('seller1@market.dev', 'Seller One'),
  ('seller2@market.dev', 'Seller Two');

insert into seller_profiles (user_id, store_name, status)
values
  (2, 'One Crafts', 'active'),
  (3, 'Two Studio', 'active');

insert into listings (seller_id, title, price_cents, quantity_available, status)
values
  (1, 'Handmade Desk Lamp', 8900, 12, 'active'),
  (2, 'Ceramic Coffee Mug', 2400, 40, 'active');

insert into orders (buyer_id, status, total_cents)
values
  (1, 'paid', 11300);

insert into order_items (order_id, listing_id, seller_id, quantity, unit_price_cents)
values
  (1, 1, 1, 1, 8900),
  (1, 2, 2, 1, 2400);

insert into payouts (seller_id, amount_cents, status, period_start, period_end)
values
  (1, 7800, 'pending', current_date - 7, current_date),
  (2, 2000, 'pending', current_date - 7, current_date);
queries.sql 
-- 1) Gross merchandise value by seller
select
  sp.store_name,
  sum(oi.quantity * oi.unit_price_cents) / 100.0 as gmv_usd
from order_items oi
join seller_profiles sp on sp.id = oi.seller_id
join orders o on o.id = oi.order_id
where o.status in ('paid', 'fulfilled')
group by sp.id
order by gmv_usd desc;

-- 2) Top listings by units sold
select
  l.id,
  l.title,
  sum(oi.quantity) as units_sold
from order_items oi
join listings l on l.id = oi.listing_id
group by l.id
order by units_sold desc;

-- 3) Pending payouts
select
  sp.store_name,
  p.amount_cents / 100.0 as amount_usd,
  p.period_start,
  p.period_end
from payouts p
join seller_profiles sp on sp.id = p.seller_id
where p.status = 'pending'
order by p.created_at desc;