Schema Explorer

See how real products structure their data. Click a schema to explore its ERD.

💳

Payments

Inspired by Stripe

How a payments platform tracks customers, their payment methods, charges, and recurring subscriptions.

PKPrimary KeyFKForeign Key1:NOne-to-ManyM:NMany-to-Many (via junction table)

Design insights

Amounts are stored in cents (integer) to avoid floating-point rounding — $9.99 becomes 999.

A charge references both the customer AND the specific payment method used, enabling a full audit trail.

Subscriptions track status separately from charges — a subscription can be 'active' even if the latest charge failed (grace period).

Example queries

Monthly revenueConceptual — uses this schema's tables

SELECT DATE_TRUNC('month', created_at) AS month,
  SUM(amount_cents) / 100.0 AS revenue
FROM charges
WHERE status = 'succeeded'
GROUP BY month
ORDER BY month

Active subscribersConceptual — uses this schema's tables

SELECT c.email, s.plan, s.current_period_end
FROM subscriptions s
JOIN customers c ON s.customer_id = c.id
WHERE s.status = 'active'

Relationships

customers.id→payment_methods.customer_id1:N

customers.id→charges.customer_id1:N

payment_methods.id→charges.payment_method_id1:N

customers.id→subscriptions.customer_id1:N