Two Wallets

A medium Data Modeling interview practice problem on DataDriven. Design the schema on the canvas: tables, keys and relationships, then get feedback on it.

Domain
Data Modeling
Difficulty
medium
Seniority
L5
Asked in
Amazon

Problem

We run an online education marketplace with two user types: students paying for access, and instructors paying for listing and promotion features. Both subscribe to different plan tiers and keep several payment methods on file, each stored as an opaque gateway token plus the card brand and last four digits for display, never full card numbers. Finance also needs to reconstruct how many subscribers we had in any past month, so design the data model behind billing.

Two user types. Multiple payment methods. One messy billing table.

Worked solution and explanation

What this really is

This is a temporal history problem dressed up as a billing page. The live product only ever shows today's plan and today's card, so the design that passes every screen is one users row with current_plan, gateway_token, card_brand and card_last4. That is the trap. Every upgrade overwrites the old plan, so the started_at Finance needs to count March's subscribers never exists. A second saved card either fails or clobbers the first. The real skill is to close rows instead of overwriting them: subscriptions become dated periods, and cards become a one-to-many child of the user.

Building it

Step 1: Keep one users table with a user_type discriminator

Students and instructors share identity, email and login. Only their plans differ. A user_type of 'student' or 'instructor' carries the split. Two forked tables would double every join for no gain.

Step 2: Put tier and price on subscription_plans

Plans are a small reference table. A price change inserts a new plan row. Existing subscriptions keep their plan_id, so what someone paid in March stays true.

Step 3: Make user_subscriptions a history, not a slot

Store one row per subscription period, with started_at, ended_at and status. An upgrade sets ended_at on the old row and inserts a new one. A month's subscribers are the rows open during that month: started_at falls before the month ends, and ended_at is NULL or falls after the month starts.

Step 4: Give cards and charges their own grains

payment_methods holds one row per saved card, keyed to user_id, with token, brand and last_four, never the card number. payments holds one row per charge and points at the subscription_id it funded and the payment_method_id it hit. That makes retries and refunds new rows, not edits.

The reference design

Five grains: `user_subscriptions` holds dated periods, `payment_methods` hangs off `users`, and each `payments` row names both the subscription it funded and the card it charged.
  • users: user_id BIGINT PK, user_type TEXT, email TEXT, signup_at TIMESTAMP, country TEXT
  • subscription_plans: plan_id BIGINT PK, plan_name TEXT, tier TEXT, billing_interval TEXT, price DECIMAL, features TEXT
  • user_subscriptions: subscription_id BIGINT PK, user_id BIGINT FK, plan_id BIGINT FK, started_at TIMESTAMP, ended_at TIMESTAMP, status TEXT
  • payment_methods: payment_method_id BIGINT PK, user_id BIGINT FK, method_type TEXT, brand TEXT, last_four TEXT, token TEXT, is_default BOOLEAN
  • payments: payment_id BIGINT PK, subscription_id BIGINT FK, payment_method_id BIGINT FK, amount DECIMAL, paid_at TIMESTAMP, status TEXT
  • user_subscriptions.user_id references users.user_id
  • user_subscriptions.plan_id references subscription_plans.plan_id
  • payment_methods.user_id references users.user_id
  • payments.subscription_id references user_subscriptions.subscription_id
  • payments.payment_method_id references payment_methods.payment_method_id

Proving it with Finance's question

The query is the proof, not the deliverable. Each period carries its own started_at, so bucketing by month and reading the prior month with LAG() takes one pass over user_subscriptions. On the flat design there is nothing to bucket.

Month-over-month new subscriptions
WITH monthly_new AS (
    SELECT
        strftime('%Y-%m', started_at) AS month,
        COUNT(*) AS new_subscriptions
    FROM user_subscriptions
    GROUP BY strftime('%Y-%m', started_at)
)
SELECT
    month,
    new_subscriptions,
    new_subscriptions - LAG(new_subscriptions) OVER (ORDER BY month) AS mom_change,
    ROUND(
        100.0 * (new_subscriptions - LAG(new_subscriptions) OVER (ORDER BY month))
        / LAG(new_subscriptions) OVER (ORDER BY month),
        1
    ) AS mom_growth_pct
FROM monthly_new
ORDER BY month

Only possible because started_at survives every plan change

Dated subscription rows
An upgrade closes one row and opens another, and started_at and ended_at answer any past month. Cards live in payment_methods, as many as the user saves. The cost is a join or two per report.
Current-state columns on users
One current_plan field, overwritten on every change. Last March is gone the moment someone upgrades. One gateway_token per user, so a second card clobbers the first. It reads fast and reports nothing.

Common follow-up questions