Add DB uniqueness constraint across tables
We saw previously in this post how to add uniqueness database constraint for a single table. But in real-world applications, we have different associations between models, so let’s start with an example:
We have a service platform where a user can buy subscriptions for different services, the requirements are the following:
- A user can have many subscriptions for different services
- A user can have only ONE subscription for a set of services
Class diagram
For the presented diagram we will have those four models:
1. User class:
class User < ApplicationRecord
has_many :subscriptions
end
2. Subscription class with a custom validation:
class Subscription < ApplicationRecord
belongs_to :user
has_many :service_subscription_states
has_many :services, through: :service_subscription_states
validate :uniqueness_user_service_combination
private
def uniqueness_user_service_combination
existing_subscription = Subscription
.where.not(id: id)
.where(user_id: user_id)
.find do |subscription|
subscription.services.sort == services.sort
end
errors.add(:subscription, "already exists") if existing_subscription
end
end
3. Joint class for Service and Subscription:
class ServiceSubscriptionState < ApplicationRecord
belongs_to :service
belongs_to :subscription
end
4. Service class:
class Service < ApplicationRecord
has_many :service_subscription_states
has_many :subscriptions, thourgh: :service_subscription_states
end
Let’s study the current situation, we know that:
- user_id belongs to subscription table
- subscription_id and service_id belong to service_subscription_state table
At first, let’s try to make this problem easier to solve by adding a new column to subscription table that contains a hash generated from user_id and service_id. Also, we should add an index for this column.
The migration:
class AddUniqueTokenToSubscriptions < ActiveRecord::Migration[5.0]
def change
add_column :subscriptions, :unique_token, :text
add_index :conversations, :unique_token, unique: true
end
end
and finally, the method to generate unique_token content:
class Subscription < ApplicationRecord
.....
before_save :generate_unique_token
....
private
...
def generate_unique_token
self.unique_token = Digest::SHA1.hexdigest(
[user_id, service.map(&:id).sort].flatten.join,
)
end
end
In this post, we saw how we solved this challenge by transforming it to a simple problem that we know already how to solve.
That’s it, so keep coding and have fun 🙂
