Published: May 30, 2022 • 2 min read
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.
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
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
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);
package must have either a factory or store, even better. Change the
constraint to check that the value returned from the parentheses equals one.
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.
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 enforcing business logic at the data layer? Let me know in the comments.
✉️ Get better at programming by learning with me. Subscribe to Jake Worth's Newsletter for bi-weekly ideas, creations, and curated resources from across the world of programming. Join me today!
Blog of Jake Worth, software engineer in Maine.