# Two Wallets > Two user types. Multiple payment methods. One messy billing table. Canonical URL: Domain: Data Modeling · Difficulty: medium · Seniority: L5 ## 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. ## Worked solution and explanation ### Why this problem exists in real interviews Subscription billing is five separate entities wearing a billing costume: users, plans, subscriptions, payment methods, and payments. The trap is denormalizing two of them. Put plan name and price on the subscription row and the first price change forces you to backfill every historical subscription with its frozen original price. Stash the card on the users table and the moment someone saves a second method the model cannot represent it. What separates a hire is reaching third normal form without bolting versioning machinery onto the schema to survive price changes and upgrades. > **Trick to Solving** > > The tell is "users have multiple payment methods". That alone forces `payment_methods` into its own table. Before drawing any tables, a strong candidate asks: can a single payment fund multiple subscriptions, and can a single subscription have multiple payments over time? Both are yes, so `payments` sits between them at its own grain. > > 1. Separate users, plans, subscriptions, methods, and payments as entities > 2. Keep plans in their own table so pricing changes are a plan version, not a user update > 3. Anchor payments to subscriptions so renewal histories are reconstructible > 4. Use status columns for active versus cancelled subscriptions --- ### Break down the requirements #### Step 1: Model plans as a reference table `subscription_plans` is a small dimension of offered plans, carrying tier and price. When a price changes, a new plan row is created and new subscriptions point at it; existing subscriptions keep their original plan FK. #### Step 2: Capture subscription lifecycle `user_subscriptions` stores `started_at`, `ended_at`, and `status`. Upgrades and downgrades end the old row and start a new one, so the history is reconstructible and you can count subscribers as of any month. #### Step 3: Keep payment methods separate Users can store multiple cards and switch defaults. `payment_methods` with an `is_default` flag and a tokenized reference captures this without widening users. #### Step 4: Payments are their own grain `payments` is one row per charge attempt, with its own status and amount. This is what lets failed retries, dunning, and partial refunds live in the same model. #### Step 5: Enforce constraints at the schema level `user_subscriptions.status` constrained to a known set; `payments.amount > 0`; `payment_methods.is_default` constrained so a user has exactly one default at a time. --- ### The schema Below is one conceptually sound third-normal-form design. The split between subscriptions and payments is the anchor; it keeps subscription lifecycle and cash movement as distinct grains, and the `started_at` column on subscriptions is exactly what a growth query reads. A `user_type` column on users carries the student versus instructor split, and the card stays as a tokenized reference plus brand and last_four, never raw card data. > **Why This Design Works** > > Third normal form here costs a few joins at query time and buys clean update semantics for price changes, plan upgrades, payment retries, and refunds. Every state change is a targeted insert or update on exactly one table. The trade-off is that reporting queries need to join three or four tables, which is cheap on any modern engine. > **Interviewers Watch For** > > Strong candidates distinguish plan identity from subscription instance from payment event. They also call out that `payments.subscription_id` is what lets MRR and churn analyses tie cash to the subscription that funded it. Weaker candidates flatten payments onto subscriptions and lose the ability to model retries. > **Common Pitfall** > > Storing `plan_name`, `tier`, and `price` as columns on `user_subscriptions`. This works until a price changes, then every historical subscription needs the frozen original price and the pipeline becomes a maintenance burden. --- ### The growth query the history table unlocks Month-over-month growth is two moves the flattened model cannot make: bucket every subscription by the month it started, then compare each bucket to the one before it. Because `user_subscriptions` keeps `started_at` as a first-class column, both moves are a single pass: aggregate to a monthly count, then read the prior month off the same ordered set to get the delta and the percentage change. **Month-over-month subscription growth** ```sql 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 ``` *bucket new subscriptions by start month, then read the prior month off the ordered set for the delta and percent change* --- ### Trade-offs and alternatives **Third normal form transactional model** Clean update semantics, multi-method support, retries and refunds as first-class rows, and `started_at` makes the monthly buckets trivial. Cost: joins for every reporting query and separate ETL into an analytics star for BI. **Flattened subscription-plus-payment table** Single-table reads, simple for a first version. Cost: price changes require historical backfills, retries duplicate rows, the schema cannot express multiple payment methods, and there is no clean start date to bucket growth by. --- ## Common follow-up questions - A user upgrades mid-cycle with a prorated charge. How does the model represent that? _(Tests whether the candidate ends the current subscription and opens a new one with a linking proration payment.)_ - A card fails and retries happen over three days. Where does that state live? _(Tests whether payments.status carries the retry lifecycle and whether a separate payment_attempts table is warranted.)_ - Finance wants churn defined as subscriptions that ended without a successor within 7 days. How? _(Tests self-join on user_subscriptions and the churn window definition.)_ - A plan price changes from $10 to $12. What rows move and what stays still? _(Tests that new subscriptions point at a new plan row while existing subscriptions keep their original plan FK.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/two_wallets) - [Data Modeling Interview Questions](https://datadriven.io/data-modeling-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.