Implementing Subscription Tables: SQL Constraints for SaaS

Previously in this course, we explored the Logical vs Physical Schema: Designing Databases for Implementation to prepare our blueprints for code. Now that we have a solid design, it's time to build the engine that powers your revenue: the subscription tables.

In a SaaS application, the subscription logic is arguably the most sensitive part of the database. If your schema allows an account to have a "premium" status without an active billing record, you lose revenue. If a user can be on a plan that doesn't exist, your application crashes. We will move from Designing Subscription Models: SaaS Relationship Mapping to concrete SQL implementation.

Defining the Plan Catalog

Before tracking who is subscribed to what, we must define the "what." A plans table acts as our catalog. It defines the tiers available in your application (e.g., Free, Pro, Enterprise).

When implementing this, don't just store the name; store the constraints. Using a CHECK constraint ensures that we only ever allow valid billing intervals and currency types.

SQL
CREATE TABLE plans (
    plan_id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price_cents INTEGER NOT NULL,
    currency CHAR(3) DEFAULT 'USD',
    interval VARCHAR(20) NOT NULL,
    -- Ensure price is always positive
    CONSTRAINT chk_price_positive CHECK (price_cents > 0),
    -- Limit intervals to standard SaaS cycles
    CONSTRAINT chk_interval CHECK (interval IN ('monthly', 'yearly'))
);

By placing these constraints at the database level, you protect your data from bad input originating from any source—whether it’s your API, a manual admin update, or a background job.

Implementing the Subscriptions Table

The subscriptions table links your accounts to the plans table. To maintain a clean state, this table requires strict foreign key relationships and status tracking.

In production, you should never allow a subscription to exist without an associated account. We enforce this with NOT NULL constraints and ON DELETE logic.

SQL
CREATE TABLE subscriptions (
    subscription_id SERIAL PRIMARY KEY,
    account_id INTEGER NOT NULL REFERENCES accounts(account_id) ON DELETE CASCADE,
    plan_id INTEGER NOT NULL REFERENCES plans(plan_id),
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    current_period_end TIMESTAMP WITH TIME ZONE NOT NULL,
    
    -- Constraint to ensure we only track valid subscription states
    CONSTRAINT chk_status CHECK (status IN ('active', 'canceled', 'past_due', 'trialing'))
);

Why Use CHECK Constraints?

You might be tempted to handle validation purely in your application code (e.g., in Python or JavaScript). However, the database is your final line of defense. If a future developer writes a direct SQL update that bypasses your application logic, the CHECK constraint will reject the invalid status, preventing data corruption.

Hands-on Exercise

To solidify these concepts, perform the following steps in your local SQL environment:

1. Create the Plans Table: Execute the CREATE TABLE plans block provided above.
2. Add a Violation Test: Try to insert a row into plans with a price_cents of -500. Your SQL engine should return a constraint violation error.
3. Verify Constraints: Attempt to insert a plan with an interval of 'daily'. Ensure your chk_interval constraint blocks this.
4. Create the Subscriptions Table: Create the table structure as shown, ensuring the foreign keys link correctly to your existing accounts table.

Common Pitfalls

• Floating Point for Money: Never use FLOAT or REAL for prices. As shown in our example, use INTEGER (storing cents) or DECIMAL. Floating-point math leads to rounding errors that eventually drain revenue.
• Hard-coding logic: Avoid putting "plan logic" (like feature access levels) directly into the subscriptions table. Features belong in a separate table, which we will address later in the course.
• Ignoring Time Zones: Always use TIMESTAMP WITH TIME ZONE for subscription end dates. SaaS products are global; storing dates without time zone awareness will cause billing cycles to drift for your international customers.

FAQ

Q: Should I use ENUM types instead of VARCHAR with CHECK constraints? A: While ENUM types are available in databases like PostgreSQL, they can be difficult to alter later (e.g., adding a new status). Using VARCHAR with a CHECK constraint provides the same data integrity but is significantly easier to migrate as your business grows.

Q: Why use ON DELETE CASCADE on the account_id? A: In most SaaS models, if an account is deleted, the subscription records are meaningless "orphans." Cascading deletes keep your database clean by removing those records automatically.

Recap

We have successfully moved from theory to implementation. By defining a plans table with price and interval constraints and a subscriptions table that links to your users, you've established the foundation for a robust billing system. You’ve learned that the database schema itself acts as a guardrail for your business rules.

Up next: We will begin our journey into data organization by exploring Introduction to Normalization, where we'll learn how to structure data to eliminate redundancy.