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:

2- Subscription class with a custom validation:

3- Joint class for Service and Subscription:

4- Service class:

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:

and finally, the method to generate unique_token content:

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 🙂

Tagged , , ,