Advanced Subscription Modeling: Features and Junction Tables

Previously in this course, we covered the basics of designing subscription models and implementing subscription tables to track account plans. While those lessons established the core billing relationship, they treated "plans" as static entities. Today, we move beyond simple tiers by modeling granular feature access.

Why Hard-Coded Plans Fail

In early-stage SaaS, it’s tempting to hard-code features—perhaps adding a is_analytics_enabled boolean column to your plans table. But what happens when you introduce a "Pro Plus" tier that includes custom reporting, or when you decide to offer a "Team Add-on" for API access? Adding columns to a table every time your product team dreams up a new feature is a maintenance nightmare.

To design a scalable system, we must decouple the concept of a feature from the instance of a subscription plan. We achieve this using a many-to-many relationship managed by a junction table.

Modeling Features and Subscriptions

To build this, we need three distinct entities:

1. Plans: The tier definition (e.g., Basic, Pro).
2. Features: A catalog of every available capability (e.g., "API Access", "Custom Exports").
3. Plan_Features: The junction table that defines which plans include which features, potentially with usage limits.

The Junction Table Pattern

A junction table doesn't just link two entities; it acts as an "association entity." By placing our data here, we can define attributes specific to the relationship, such as a limit (e.g., "100 API calls per month").

SQL
-- 1. Define the catalog of available features
CREATE TABLE features (
    feature_id INT PRIMARY KEY,
    feature_name VARCHAR(50) UNIQUE NOT NULL,
    feature_code VARCHAR(20) UNIQUE NOT NULL
);

-- 2. Define the junction table linking plans to features
CREATE TABLE plan_features (
    plan_id INT,
    feature_id INT,
    limit_value INT DEFAULT NULL, -- NULL implies "unlimited" or "enabled"
    PRIMARY KEY (plan_id, feature_id),
    FOREIGN KEY (plan_id) REFERENCES plans(plan_id),
    FOREIGN KEY (feature_id) REFERENCES features(feature_id)
);

This structure is a classic example of handling many-to-many relationships, allowing you to add a new feature to your database and immediately map it to existing plans without modifying your schema.

Worked Example: Mapping "Pro" Access

Imagine our "Pro" plan includes "Custom Reporting" with a limit of 5 reports per month.

SQL
-- Insert a feature
INSERT INTO features (feature_name, feature_code) 
VALUES ('Custom Reporting', 'rpt_custom');

-- Map to the Pro plan (assuming plan_id = 2)
INSERT INTO plan_features (plan_id, feature_id, limit_value)
VALUES (2, 1, 5);

When querying a user's permissions, you can now join these tables:

SQL
SELECT f.feature_name, pf.limit_value
FROM plan_features pf
JOIN features f ON pf.feature_id = f.feature_id
WHERE pf.plan_id = 2;

Practice Exercise

Take your current project schema. Create a features table and a plan_features junction table. Define three features (e.g., "Dark Mode", "Data Export", "Priority Support") and assign them to your existing "Basic" and "Pro" plans using the junction table. How would you handle a feature that is "enabled" but has no numerical limit? (Hint: The limit_value can be NULL).

Common Pitfalls

• Over-normalization: Don't create a plan_features table if your plans are truly static and will never change. Use this pattern only when you expect the feature set to grow or fluctuate.
• Performance at Scale: As you add more features, JOIN queries can become expensive. Ensure you have indexes on your foreign keys (the plan_id and feature_id columns in the junction table) to keep lookups fast.
• The "All-or-Nothing" Trap: Forgetting to handle the "unlimited" case. Always design your application logic to treat a NULL limit as "unlimited" rather than "zero."

FAQ

Q: Should I put feature flags on the users table instead? A: Generally, no. Managing features at the plan level is much cleaner. If you need user-specific overrides, that’s a separate concern better handled by an RBAC system.

Q: Can I store the feature status (enabled/disabled) here? A: Yes. If a record exists in plan_features, the feature is enabled. If it doesn't, it's disabled. You don't need an is_enabled boolean column.

Recap

We've moved from static tiers to a flexible, feature-driven model. By using a junction table to connect plans and features, you've gained the ability to define limits and toggle access without needing to run ALTER TABLE commands every time your product evolves. This keeps your schema clean and compliant with normalization principles.

Up next: We will explore how to design for scalability, ensuring our data types and structures can handle the growth of our SaaS platform.