Jake Worth

• postgresql

Scenario: you have a record that can belong to one record or another, but not both.

But maybe you don’t like the polymorphism provided by your language or framework.

Maybe something about polymorphism doesn’t sit right with you.

Maybe you want database integrity built into your implementation.

One solution to this problem 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.

Let’s dive in.

The Migration

This is a PostgreSQL solution, but it could be applied to other RDBMS’s.

Consider a table called package. Each package could belong to a factory or a store via an *_id column.

We can enforce this 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 if true, 0 if false), added to store_id’s truthiness coerced to an integer, does not equal two. In short, both columns cannot simultaneously have a not-null value.

Alternately, with a comparison function (thanks to u/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.

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? I’d add a validation to my model that duplicates this logic (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

Databases inevitably become multi-tenant, as multiple frontends start talking to the API, and adding a database constraint up front protects your data from any tenant that might not yet know this business rule.

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.

What are your thoughts on business logic at the data layer? Let me know!