PostgreSQL Polymorphism

May 30, 2022 - 2 minutes read - 406 words

Scenario: you need a database record that can belong to one record or another, but not both. Polymorphism and exclusivity. One approach is to create a polymorphic-style association at the data layer. By doing so, you’ll get data integrity built in, rather than trusting it will be enforced by each tenant at the application layer.

Here’s how I’ve solved this problem in the past.

The Database Migration

This is a PostgreSQL example, but it could be applied to other databases.

Consider a table called packages. Each package could belong to a factory or a store via a factory_id or store_id column. The real situation this describes is that our physical packages (boxes of items we’re shipping to customers or are being returned) can only be in the factory or the store at any given time.

We can enforce this relationship via the following:

alter table packages
  add constraint check_exclusive_ownership
    check (
      (
        (factory_id is not null)::integer +
        (store_id is not null)::integer
      ) <> 2
    )

We’ve added a constraint to the packages table that checks that factory_id’s truthiness, coerced to an integer (1 for true, 0 for false), added to store_id’s truthiness coerced to an integer, does not equal two. In short, both columns cannot have a not-null value.

Alternately, here’s the same idea with a comparison function (thanks to Reddit user truilus):

alter table packages
  add constraint check_exclusive_ownership
  check (num_nonnulls(factory_id, store_id) <> 2);

If a package must have either a factory or store, even better. Change the constraint to check that the value returned from the parentheses equals one.

alter table packages
  add constraint check_exclusive_ownership
  check (num_nonnulls(factory_id, store_id) = 1);

Why Not Use the Application Layer?

Couldn’t we trust our application to handle this logic? Why bother with a database solution?

To that, I’d say: why not both? Enforcing this logic at the application layer lets the application recover itself gracefully, show error message to the users, and more. And it protects the data from any new tenant that doesn’t yet know this particular business rule.

I’d add a validation to my model that duplicates this logic (Ruby on Rails example):

# app/models/package.rb

validate :exclusive_ownership

def exclusive_ownership
  if factory && store
    errors.add(:base, 'Cannot belong to a factory and a store')
  end
end

Conclusion

Thanks to Jack Christensen, who taught me this technique years ago. Jack and I have been teaching database workshops together since 2016 and I continue to be impressed by his SQL solutions.

Postgresql