A SaaS Subscription Data Model

SaaS (Software as a Service) is one of the three main components of Cloud computing. Usually, SaaS applications are web-based and can handle many different users at one time. Subscription-based solutions are very popular SaaS offerings. Some well-known SaaS products include Microsoft Office 365, Amazon Web Services (AWS), Slack, Jira, Stripe, and (of course) Vertabelo! Today we’ll take a look at a data model that would allow us to manage SaaS subscriptions.

The Idea

SaaS products can be very different. Some charge for their services on a regular basis, e.g. monthly or yearly; others charge only for the amount of time or resources used (or a combination of these two). To keep things simple in this article, I’ll focus only on monthly paid subscriptions.

For businesses in the financial sector, custom financial software development can provide tailored solutions that integrate specific subscription models and billing structures, ensuring efficient and accurate management of recurring payments.

Let’s assume that we have a few different SaaS solutions and we need to track all our subscribers in one database. This could be the case when we’re providing database solutions (e.g. Amazon DynamoDB), analytics tools (e.g. Amazon Athena), or robotics applications (e.g. AWS RoboMaker). In fact, if we look at Amazon, we can see there are many different applications available. We would pick only those we really need.

Data Model

The model consists of three subject areas:

• Users & groups
• Software & plans
• Subscriptions, plans & payments.

We’ll describe each of these subject areas in the order they’re listed.

Section 1: Users and Groups

The Users & groups subject area stores information on all the users of our application. We’ll assume that users can be grouped, e.g. when a company wants to buy licenses for several employees. We’ll create a group even when only one user belongs to it. This will give us the flexibility to later add new members to that group.

The most important table here is the user_account table. We’ll use it to store all details related to user accounts. These are:

• first_name & last_name – The first and last name of the user. Please note that each user stored here is a private individual.
• user_name – A username (chosen by the user).
• password – A hash value of the user’s password. (Users set their own passwords.)
• email – The user’s email address, set during the registration process.
• confirmation_code – The code used during the email confirmation process.
• confirmation_time – When the registration/confirmation was completed.
• insert_ts – The timestamp when this record was initially inserted.

Users can create groups; groups have predefined types. A list of all possible group types is stored in the user_group_type table. Each type is UNIQUELY defined by its type_name. We’ll also define the minimum and maximum number of group members allowed for each group type. That range is defined with two values – members_min (the lower boundary) and members_max (the upper boundary).

While creating a new account, users will also select their user group. This will create a new record in the user_group table referencing the selected group type and storing the timestamp (insert_ts) when this record was inserted. The customer_invoice_data attribute is a textual description of what we’ll print on the invoice for that user group.

The last table in this subject area is the in_group table. For each group, we’ll store a list of all its members. Besides references to the user group (user_group_id) and user account (user_account_id), we’ll also store the timestamp when a user was added to the group (time_added) or removed from the group (time_removed, which will only contain a value if the user has been removed). We’ll also have a flag to denote if the user is the group_admin or not. Group admins can update group members and add new members.

Section 2: Software and Plans

Next, we need to define everything we’ll offer to our (potential) customers. We might offer only one type of software, but there is a great possibility we’ll have several different offers. A common example of this case is having a SaaS tool that’s separate from its analytics application, e.g. Stripe and Stripe Sigma. We’ll cover such cases in our data model.

We’ll start with the software table. In this dictionary, we’ll store a list of all our SaaS offerings. For each one, we’ll store:

• software_name – A UNIQUE software name.
• details – All the details describing that software.
• access_link – A location or link where we can access that software.

We should be able to offer our SaaS solutions in one or more different plans. Each plan contains various options. For example, we could have a premium plan that includes all the options we offer and a basic plan that includes only the essentials. We’ll store all distinct plans in the plan table. For each plan, we’ll define:

• plan_name – The name we have selected for this plan. Together with the reference to the software (software_id), this forms the alternate/UNIQUE key of this table.
• user_group_type_id – A reference denoting the type of the group that can use this plan. This could be a single-user group or a standard group. This reference also defines the maximum number of group members for that plan – e.g. if our plan allows five different accounts on one subscription, we should reference the appropriate user_group_type.
• current_price – The current price for this plan.
• insert_ts – The timestamp when this record was inserted.
• active – A flag denoting if this plan is active or not.

We’ve already mentioned that plans for the same software will come with different options. A list of all distinct options is stored in the option dictionary. Each option is UNIQUELY defined by its option_name.

To assign options to different plans, we’ll use the option_included table. It stores references to the related plan (plan_id) and option (option_id). This pair, together with the date_added attribute, forms the UNIQUE key of this table. The date_removed attribute will contain a value only if we decided to remove a certain option from a plan. This could happen when we build a new option to replace the old one or we decide not to have a given option anymore because few people use it.

The last part of this subject area is related to special or promotional offers. In general, such offers give customers more service for less money and last for a certain period of time. They could be aimed at acquiring new customers or selling plan upgrades (or a wider range of services) to existing customers.

All of our promotional offers are stored in the offer table. For each offer, we’ll need to define:

• offer_name – A UNIQUE name we’ve selected for this offer.
• offer_start_date and offer_end_date – The time period during which this offer is available.
• description – A detailed textual description of the offer.
• Discounts: We need the flexibility to have two types of discounts – a fixed amount-based discount (e.g. get $50 off) and a percentage discount (e.g. save 25%). If we offer a fixed discount, we’ll insert that value into the discount_amount attribute; if we offer a percentage discount, we’ll insert that percent into the discount_percentage attribute.
• Duration: We’ll use the same logic here that we’ve used for the discounts. In some cases, offers will last for a defined number of months (e.g. for 24 months after customers sign up); in these cases, we’ll define the duration_months value. Other offers will be valid until a certain fixed date (e.g. until December 31, 2019 ); for these, we’ll define the date and store it in the duration_end_date attribute.

We’ll use the remaining two tables in this subject area to define what each offer contains and what prerequisites it has. For this purpose, we’ll use two tables: include and prerequisite. They share the same structure and contain the same UNIQUE pair of offer_id – plan_id. Some offers may not have any prerequisites, while others may – e.g. if we’re offering a discount for upgrading to a plan with more users or a software subscription for users of some other software.

Offers can be complex, so I’ll provide a few examples.

1. If we currently use Plan A and have an offer to upgrade to Plan B, this is straightforward.
2. If we have two offers, “Plan A upgrades to Plan B” and “Plan B upgrades to Plan C”, we should create one more offer: “Plan A upgrades directly to Plan C”. This allows users to upgrade their plans in one step rather than two. One example of such an upgrade is changing a subscription that currently allows five users per group to one that allows 20 users per group without stopping at an intermediate, ten-users-per-group plan along the way.
3. If a group uses Product A, we could have a special offer to subscribe to Products B and C at a promo price. We could also have two separate offers to subscribe only to Product B and only to Product C.

In general, we should have one offer that will change the current plan to the desired plan without any steps in between and only one offer to subscribe to one or more new products.

Section 3: Subscriptions, Plans, and Payments

The last subject area connects the two previously-mentioned areas and is the true heart of this model.

All subscriptions are stored in the subscription table. We’ll treat each different plan as a separate subscription, even if these subscriptions/plans are the result of the same offer. The reason for this is so we’ll be able to manage subscriptions separately – e.g. cancel them separately if we wanted to. We’ll need to define a number of details here:

• user_group_id – The ID of the group subscribing to this plan. This is important because users won’t be subscribed individually; they are subscribed indirectly, as part of the group.
• trial_period_start_date and trial_period_end_date – The lower and upper boundaries of the trial period (if any) for this subscription.
• subscribe_after_trial – A flag denoting if the subscription will be automatically renewed after the trial period (if any) ends.
• current_plan_id – The current plan for that subscription. If the subscription is no longer active, this attribute will contain the value of the last active plan.
• offer_id – A reference to the offer this subscription is related to. This attribute will contain a value only if this subscription was the result of a certain offer.
• offer_start_date and offer_end_date – The lower and upper boundary of the period during which this offer was active. These attributes will be defined only if this subscription was the result of a certain offer.
• date_subscribed – When this group subscribed to this subscription.
• valid_to – The last date this subscription is valid. In the case of a monthly subscription, we can expect that valid_to will be set to the end of the current month. If a customer unsubscribes at any time during a month, they’ll still be able to use their software until the end of that month.
• date_unsubscribed – The date when that group unsubscribed from this subscription. We can expect that this date will be set manually by the group admin when the group decides not to use the service anymore. However, it could also be set automatically, according to predefined criteria – e.g. automatically unsubscribing a group from their service if there are two or more unpaid invoices.
• insert_ts – The timestamp when this record was inserted.

Subscription plans often change over time. To maintain a complete history of all our plans, we’ll store all plan changes in the plan_history table. For each record here, we’ll need a:

• subscription_id – The ID of the related subscription.
• plan_id – The ID of the related plan.
• date_start and date_end – The time period when this plan was active.
• insert_ts – The timestamp when this record was inserted.

The last table in our model will store our invoices. For each invoice, we’ll keep the following details:

• customer_invoice_data – A description of the customer billed for this invoice. This will be the data from user_group.customer_invoice_data at the moment the invoice was generated.
• subscription_id – The ID of the related subscription.
• plan_history_id – The ID of the plan active during this invoice period.
• invoice_period_start_date and invoice_period_end_date – The time interval (e.g. 1 January 2019 to 31 January 2019) covered by this invoice.
• invoice_description – A short textual description of the invoice.
• invoice_amount – The amount of payment due for this invoice.
• invoice_created_ts – When this invoice was generated and inserted into the table.
• invoice_due_ts – When this invoice is due.
• invoice_paid_ts – The timestamp when this invoice was paid.

Tell Us What You Think About the SaaS Data Model

I guess that most of you have met SaaS, either as a developer or as a user. I’m looking forward to your take on it and on this data model. Feel free to share your experiences and suggestions in the comments below.