SJ logo

Single Table Inheritance Association in Relational Database

20.04.20202 Min Read — In 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.

  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

  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.
© 2020 by Silicon Jungles. All rights reserved.
Last build: 15.05.2020