Designing Subscription Models: SaaS Relationship Mapping

Previously in this course, we covered Mapping SaaS Users and Accounts: Entity Design for Scalability, where we established the foundation for user authentication and multi-user access. In this lesson, we add the "revenue engine" of our SaaS project: the subscription model.

Designing a subscription model is about more than just storing a status string. You need to model the relationship between your customer's account and the specific service tier they pay for, ensuring your database can handle upgrades, downgrades, and lifecycle tracking.

Defining Subscription Entities

To move beyond identifying core entities, we must define the specific components of a subscription. A subscription model generally requires three distinct tables to maintain data integrity and flexibility:

1. plans: The static definitions of your offerings (e.g., "Basic", "Pro", "Enterprise").
2. subscriptions: The active link between a specific account and a plan.
3. accounts: The entity we previously modeled, which now acts as the parent for the subscription.

By separating the "Plan" (the product definition) from the "Subscription" (the instance of a customer buying that product), you ensure that if you change the price of your "Pro" plan tomorrow, you don't break the history of every customer currently subscribed to it.

Establishing One-to-Many Relationships

In a standard SaaS relationship mapping, a single account typically holds one active subscription at a time, but a subscription plan can have many accounts subscribed to it. This is a classic one-to-many (1:M) relationship.

Using Crow's Foot notation, the plans table is the "one" side, and the subscriptions table is the "many" side.

The Data Model Structure

Here is how we visualize the link between these entities:

Entity relationship diagram: ACCOUNTS | → SUBSCRIPTIONS : "owns"; int id PK; string name; decimal price; int id PK; int account_id FK; int plan_id FK; date start_date; string status; int id PK; string name

Worked Example: Defining the Schema

In practice, your SQL implementation of this model will look like this. We are extending our project to ensure that every subscription explicitly points to a plan and an account.

SQL
-- The definition of what you sell
CREATE TABLE plans (
    plan_id INT PRIMARY KEY,
    plan_name VARCHAR(50),
    monthly_price DECIMAL(10, 2)
);

-- The record of a customer's specific subscription
CREATE TABLE subscriptions (
    subscription_id INT PRIMARY KEY,
    account_id INT, -- Links to the Account
    plan_id INT,    -- Links to the Plan
    status VARCHAR(20), -- e.g., 'active', 'canceled', 'past_due'
    FOREIGN KEY (account_id) REFERENCES accounts(account_id),
    FOREIGN KEY (plan_id) REFERENCES plans(plan_id)
);

Hands-on Exercise

Take the current ERD you are developing for your project. Add a subscriptions table and a plans table. Define the account_id and plan_id columns in the subscriptions table as foreign keys. Sketch out how you would represent a "Lifetime" plan versus a "Monthly" plan within the plans table.

Common Pitfalls

• Storing "Plan" data in the "Account" table: Avoid adding current_plan as a text column in your accounts table. This violates normalization principles and makes it impossible to track price history or handle plan upgrades gracefully.
• Assuming one-to-one: Even if your current business model only allows one plan per account, always model this as a 1:M relationship. It allows for future flexibility (e.g., "Add-ons" or "Multi-seat" configurations).
• Missing Status: Beginners often forget a status column. A subscription is rarely just "on"; it can be "trialing", "canceled", or "past_due". Without this, your application logic will be forced to guess based on dates.

FAQ

Q: Should the subscription table include the price at the time of purchase? A: Yes. Because prices change, it is best practice to store the price in the subscriptions table (or an invoice table) to reflect what the customer is actually paying, rather than just referencing the plans table.

Q: How do I handle upgrades/downgrades? A: You generally "expire" the old subscription record (change status to 'canceled') and create a new record in the subscriptions table with the new plan_id.

Recap

We’ve successfully evolved our project by isolating subscription logic into its own entities. By separating plans from subscriptions, we’ve created a flexible architecture that supports business growth, tracking, and complex billing states.

Up next: Refining the Conceptual Model, where we will validate our full ERD against our initial business requirements.