Here at Highgroove we like a good challenge, and we love it when we can meet a challenge with a simple and elegant solution.

Recently we had a customer with a great problem to have – they had signed up lots of new customers and were experiencing some growing pains.

A typical Ruby on Rails app has one database. This app had one database per customer.

Routine tasks like launching servers, deploying code, or migrating databases were taking much longer than normal because they had to be performed per customer instead of just once.

What this app needed instead was Multitenancy – a fancy word for combining the customers into one database.

To combine safely, we’d need to ensure each customer’s unique keys no longer were in conflict with each other.

Here is an example of unique keys before the merge:

Customer A     Customer B

1

Bob

1

Frank

2

Alice

2

Eleanor

And after the merge:

Customer A & B

1

Bob

1

Frank

2

Alice

2

Eleanor

You can see the unique keys are no longer unique.

How do we fix this? Time to look at some code:

This bit of code will give us the maximum id across all tables in all customer databases.

Once we know the maximum id, we can reassign each customer’s unique keys with enough offset to ensure there are no conflicts:

This will give us the combined table:

Customer A & B

3

Bob

customer_a

4

Alice

customer_a

5

Frank

customer_b

6

Eleanor

customer_b

Now we’re off to the races – with a single database containing records for all of our customers – and no unique key conflicts!

Have you ever had to combine conflicting database records? How did you tackle the problem?