# 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

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

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
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 🙂