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 *_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.
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 business logic at the data layer? Let me know!
Get better at programming by learning with me! Join my 100+ subscribers receiving weekly ideas, creations, and curated resources from across the world of programming.