Data Model for SaaS Application

I’m looking at my data model for my SaaS app and feel it is sub-optimal.

The basic structure isn’t too surprising. Here’s the gist of it.

class User
  has_one :company
  has_many :subscriptions #only one of them will be active at a time
  has_many :invoices
end

class Subscription
  belongs_to :user
end

class Company
  belongs_to :user
end

class Invoice
  belongs_to :user
end

A note on the subscriptions table: Only one will be active. The rest are there for historical purposes, so we can see all the times someone changed their account to a different plan.

Here are the things that bug me about the data model.

  1. The Company model does most of the heavy-lifting in the app. To tell
    how it should behave, we need to reference the subscription object (for the plan) and the user (for their state - active or inactve).
    It’s probably not a huge performance issue in production, but
    requires a fairly large object graph to be created during testing,
    which causes things to drag.

  2. There’s no linkage between a subscription and an invoice, which makes reporting more challenging then I’d like.

  3. Tracking failed billing attempts is a pain.

  4. There’s some ambiguity about which entity’s state should be modified in the case of a suspension (e.g., due to non-payment).

  5. And a raft of other little issues.

I realize that this is inherently a complex scenario, so I’d expect the software to be complex as well. However, to rectify the issues I mentioned above, the solution points to adding more objects and doing some denormalization. Both of these things give me pause and make me wonder if there isn’t a different way to represent this information that would be simpler and more flexible.

Does anyone here have any experience creating SaaS/subscription data models? If so, how would you go about this?

Regards,
Eric

It’s probably not a huge performance issue in production, but requires a fairly large object graph to be created during testing, which causes things to drag.

Yeah, that happens. I haven’t found a great way around this. If you’re using factory_girl, you can use build_stubbed which will avoid creating these dependencies when you don’t need them.

There’s no linkage between a subscription and an invoice, which makes reporting more challenging then I’d like.

It sounds like maybe Subscriptions should have_one Invoice.

Tracking failed billing attempts is a pain.

Why? Maybe Subscriptions actually need to have_many Invoices?

There’s some ambiguity about which entity’s state should be modified in the case of a suspension (e.g., due to non-payment).

I’d assume that the Subscription would have its state modified, but that that might affect some query method on User like has_active_subscription?.

Does anyone here have any experience creating SaaS/subscription data models? If so, how would you go about this?

We’ve created a couple. I think you’re heading in pretty much the right direction. Nothing particularly bad jumps out at me, so I’d just press forward and deal with the most-painful issues as you come to them.

There is this open source subscription app, developed in rails.

I haven’t used it. But maybe is a good idea to read the code.

1 Like

Thanks! I meant to find that and link to that. Appreciate you doing so.

I’ve seen the Stripe example. Unfortunately, it’s light on the data modeling. It assumes that you are using Stripe to contain your subscription records, which we are not.

It feels like with the myriad SaaS applications out there that there would be more literature on their structure and that how to optimally model (realizing that optimal is circumstantial) their internals would be a solved problem.