Single Table Inheritance Association in Relational Database

Single Table Inheritance (STI)

Many sub-classes inherit from one superclass with all the data in the same table in the database. The superclass has a type column to determine which subclass an object belongs to.

Imagine that we are creating an application that supporting contactless payment. There are two kind of users in the application, shopper and merchant. A shopper will able to scan the QR code to pay for the merchant. This case is a perfect candidate for STI.

From the superclass User model with the attributes for first_name, last_name, email, we have two subclasses can inherit from User model and can get all those same attributes. To tell the framework that we are using STI and want all the data for User and its subclasses to be in the same table in the database, we create the type column for the superclass.

1
2
3
4
5
6
7
8
class User < ApplicationRecord
end

class Shopper < User
end

class Merchant < User
end

Any methods or validations in the User class are shared with each of its subclasses. And we also can add unique methods to any of the subclasses as needed

1
2
3
4
5
6
7
8
9
10
11
class User < ApplicationRecord
  validates :email, uniqueness: true, presence: true
end

class Merchant < User
  has_one :qr_code
end

class Shopper < User
  has_many :payment_methods, dependent: :delete_all
end

Now, we want to expand the functionality of the application. We want to support Shopper from different payment gateways (Stripe, BrainTree, Paypal, etc), with each gateway integration we will need to add at least one column to Shopper (aka User table). Now, this is where things can get sticky, our model don’t perfectly share data fields any more. Few issues can arise in this situation:

  • Our table will have a lot of null values since objects will have fields that don’t apply to them.
  • As the table grows, we can run into performance costs when querying if we don’t add filters. A search of a certain of Shopper using BrainTree will look at every item in the User table - so not only Shopper, but Merchant also.
  • There is nothing stopping a user from adding more inappropriate data to the model.

CONCLUSION

So, let’s recap about Single Table Inheritance technique:

PROS

  • Simple to implement.
  • Follow DRY (Don’t Repeat Yourself) principle - saves replicated code by using inheritance and shared attributes.
  • Subclasses can have own behavior as necessary.

CONS

  • Doesn’t scale well: as data grows, table can become large and possibly difficult to maintain/query.
  • Allow creation of invalid objects if validations are not in place.
  • Difficult to validate and query if many null values exists in table.