What schema changes are needed to add a Pro subscription tier?
This tests normalization of billing data versus hardcoding tiers. Add a plans table with integer cents pricing, link subscriptions via plan_id, and leave users untouched. Red flag: adding a tier string column to users or storing prices in subscriptions.
What's really being asked
Whether you normalize subscription metadata into a dedicated plans table or hardcode tier logic into the users and subscriptions tables. The core concern is relational design for SaaS billing, data integrity through foreign keys, and safe migration patterns for production data.
The full answer
First, state that the users table should not change because subscription state belongs to a billing entity, not the identity entity. Second, describe creating or extending a plans table with columns like id, name, price_monthly_cents, price_yearly_cents, trial_days, features as JSON, and is_active. Third, explain that the subscriptions table needs a plan_id foreign key referencing plans, plus status enums such as trialing, active, past_due, canceled, and paused, along with billing period timestamps. Fourth, mention a backfill migration that maps existing subscriptions to the correct plan rows without downtime, and emphasize storing all monetary values as integer cents to eliminate floating-point precision bugs.
The mistakes people make
Adding a tier string or enum column directly to the users table, which couples identity to billing and breaks when a user has multiple subscriptions. Duplicating price columns on the subscriptions table, which creates update anomalies when pricing changes. Proposing to drop and recreate tables, or omitting a migration strategy for existing rows. Storing prices as floats or decimals without explaining the integer-cents pattern. Hardcoding Pro features in application code rather than modeling them in the schema.
What usually comes next
How would you handle a user downgrading from Pro to Free mid-cycle? How would you support per-seat pricing or usage-based metering on top of tiered plans? What indexes would you add to keep the billing dashboard fast at high volume? How would you ensure tenant isolation if this is a multi-tenant SaaS?
A concrete example
Imagine you currently have a subscriptions table with columns id, user_id, status, and price. You would create a plans table with id, name, price_monthly_cents, and is_active. Then add plan_id to subscriptions as a nullable foreign key, backfill it by joining on the old price values to map each subscription to its plan, mark the column not null, and finally drop the old price column from subscriptions. The new Pro tier is simply a new row in plans with name Pro and price_monthly_cents set to 2999, requiring zero application deploys to recognize the tier.
Interview question
Which approach safely adds a Pro tier to an existing SaaS billing schema while keeping user identity separate from subscription data?
- a.Create a plans table with integer cents pricing, add a plan_id to subscriptions, and migrate existing data without changing users.Correct
- b.Drop the subscriptions table, recreate it with a tier string column, and repopulate it from application code.
- c.Add a tier enum to the users table and store Pro pricing as a float in the subscriptions table.
- d.Add price columns for each tier directly to the subscriptions table so every row tracks its own cost.
Why? this is the answer
Normalizing tier metadata into a plans table with integer cents and linking via plan_id prevents update anomalies and keeps billing separate from user identity. Option D seems intuitive because subscriptions relate to prices, but duplicating plan data on each subscription row forces schema changes whenever pricing changes and violates normalization.
Just read this? Test yourself on what you have been reading.
Read the original → erflow.io
- #database design
- #subscription billing
- #schema migrations
- #normalization
- #growth
You just looked this up. Could you explain it out loud?
That is the part interviews actually test. Tezvyn takes questions like this one and gives you what the interviewer is really checking, the answer that lands, and the mistake that ends the conversation, in the four minutes before your next meeting.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles