Polymorphic relationship vs STI vs Class table inheritance with RoR

Hi I watch your tuts and it's pretty helpful. Always thanks!
I have some question about database design.

There are three types of invoice items with following tables

1) SubscriptionItems,

2) Prorations,

3) UsageItems,

Those have the same attributes below

invoice_id
amount
stripe_invoie_id

However

only SubscriptionItem and Proration

period_start_at
period_end_at

and only Proration and UsageItem has

title

and only UsageItem has

uuid
account_id
description

To achieve this model I've been using polymorphic relation.

class InvoiceItem < ActiveRecord::Base
  belongs_to :invoice
  belongs_to :itemable, polymorphic: true
end

class SubscriptionItem < ActiveRecord::Base
  belongs_to :plan
  has_one :invoice_item, as: :itemable
end

class UsageItem < ActiveRecord::Base
  belongs_to :account
  has_one :invoice_item, as: :itemable
end

class Invoice < ActiveRecord::Base
  belongs_to :account
  has_many :invoice_items
end

class Account < ActiveRecord::Base
  has_many :invoices
  has_many :usage_items
end

For now it works.
However As far as I understand polymorphic should have has_many relation.
So this resides in the middle of Polymorphic and STI.
Because those three types of invoice items are always be subscriptionitem, proration, or usageitem.

It's hard decision that I could keep using this models or use STI instead?
Or Should I use class table inheritance should be fit?

EDIT

I'd love to hear the reason why I could use some design.
Maybe those types pros and cons.

As far as I know,

If I apply STI

That leads many NULLable columns, but RoR supports STI. So it's easy
to use.

If I apply polymorphic with has_one

It stills the rails way but the original polymorphic definition is
different. It should have has_many relationship instead of
has_one. Also it's impossible to add foreign key.

If I apply Class table inheritance,

It's more efficient for relational database, but it's not rails way.

Hey Toshiki, this is a great question!

You're right on the tradeoffs here. Here's some thoughts I have on it:

STI is great when you have very similar models that are unlikley to change or won't have many different columns. For example, you wouldn't want to do this if you had two types of User models but each one had 15 different attributes unique to each other. Then you have 30 columns on one table and you'd only ever use half of them at a time. That means you've got a mess of attributes that you don't use and makes for a confusing time later on.

In your case, the models are pretty similar and you don't have that many additional columns so STI is not a bad fit. Yes, you'll have a couple columns that are null, but that's not a big deal.

As for polymorphic associations, they've absolutely got their downsides too. The primary one being additional complexity and no ability to query efficiently. Since every record has the class name stored inside the column's value, you can't add foreign keys which is very unfortunate. It's great for things like comments where you often will be querying for Movies, Actors, or other models and their associated comments and never the reverse of looking at comments and trying to find their "commentable" records. You can index the commentable_type and commentable_id columns together to make finding a Movie and all the associated comments a fast query, but there's no way to do uniqueness or anything like that on a database level.

Another downside with the polymorphic associations are that you now have a whole bunch more tables and you can already see it feels overkill in this situation. As any project grows, more tables means it's harder and harder to wrap your head around how things work as time goes on.

So what I would say is this:

• If you imagine your models aren't going to change hardly at all in the future (it seems like they won't), then STI doesn't seem like too bad of a solution here. You've got very similar models, and there's only 3, and you only add a couple columns unique to each.
• If there's a possiblity you might be adding more columns or several new Invoice Item models...then you might be better off going with polymorphic associations instead so you don't have one bloated table with 60 columns and most of them being null values.

Then the real conclusion here I would probably give you is: start simple and just pick one that seems most intuitive to you right now.

You honestly can't go too wrong with either approach here. The one thing that you're trying to optimize for is the future. You can have a best guess as to what will change in the future, so use the path that seems most in line with that. If something changes and it turns out the solution you chose wasn't ideal, then you can always write migrations to move data into another structure and change your models along with it.

You will always have that option to reorganize your data so even if you pick the wrong solution, you can always fix it and refactor.

@Chris

Thank you so much for the detailed answer. I don't have anyone to ask in our team. (Those who have really good understanding of OR mapping.)

Yes, it's true that STI is not only RoR thing and it's introduced in the book by Bill Karwin.

• SQL antipatterns
• Practical Object-Oriented Models in SQL

Also it's introduced PofEAA too!
So it's absolutely decent pattern IMO.

In here the main question is that possible to change to polymorphic from STI in the future?

In an example,

You can see three types of entities.
-subscription item

• proration
• usage item

So for implementation,
subscription item and proration are similar, but usage item is a little different from the others.

(So you seem to ues Stripe API as GoRails billing processor. What I does is developing billing system in our application with Stripe.)

We have a mapping layer that sits between the Stripe API and your own internal invoice system to

• generate a completely custom receipt
• store data for an invoice (who get charged for what items)

subscription item and proration
in the invoice.payment.succeedd webhook, we can just handle and stroe the data. (like which inoivce has which items.)

usage item is basically,

• for extra optional items on top of the subscription (subscription + something)
• Stripe ususally has the cicle that after they send invoice.created webhook, we could add items to the invoice for 1 hour before invoce.payment_succeed/faild trigers (except the very first invoice)
• In invoice.created webhook we can fetch the usage_items and add the invoice, so that's why it's belongs to an account entity.

I wish I could articulate the situation, and you could understand.

However usage_item is not used for now. Maybe in the future.

I totally agree with what you said.

start simple and just pick one that seems most intuitive to you right now.

For now I could delete unused stuff (usage item)

So the question here is possible to change to polymorphic from STI in the future?

you can always write migrations to move data into another structure and change your models along with it

It's always scarly to change the db shceme especially table once the application runs in production.
I'd love to hear how I could achieve it efficiently. (Maybe little off the topic, and I'm very new to RoR)
It's okay to restructure the database schema, and what about the data in db, and also downtime to deal with that.

Thanks!

Also In the first example, I simplifid too much usage item entity.
It has more than 4 ~ 6 different column from other types of invocie items. (subscription item, and proration)
So it's not really like 20 ~ 30 columns.
I think I can still use STI but what do you think?