0

So. I'm building a multi-tenant Laravel SaaS web-app, and am a little stuck when it comes to the database design. I have been searching around trying to find a solution, but i really can't decide on which one to go with. I really hope some of you with more experience and knowledge than me can come up with some advice. Sorry about the long post, but i hope you’ll hang in.

Problem:

In the app my users will be importing data from an external database of their own (with a know schema).

E.g.: I will be importing products with realtions to categories. The easiest way would just be to import the external product_id to the new primary key of the product. BUT as the users product_id’s will probably conflict, i will have to assign each product with a new primary key, while still keeping the external product_id for reference when syncing back to the external db.

E.g.: external product_id will be ext_product_id and i will assign a new product_id as a primary key.

As of now i can think of 3 ways to do this:

Solution 1 - Single database with new primary keys:

So if i import a list of products and categories i will have to save each external product_id as ext_product_id and assign a new primary key to the product. I will then have to query the categories ext_category_id = the products ext_category_id and then create a new relation with the new primary key product_id and primary key category_id. These looping queries takes forever when importing several thousands of rows, and some tables has 4 different relations which mean a lot of “ext_” columns to keep track of and sync.

Solution 2 - composite primary key:

As each user will have no reference to an external database i could create composite keys consisting of the tenant_id and e.g. the external product_id. This would allow my to just batch insert the external data with a key prefix consisting of the tenant. This way the relations should be working "out of the box". But Laravel doesn't support the feature as far as i understand? Any ideas?

Solution 3 - multiple databases:

To create a separate database for each tenant would probably be the best solution performance and sanity wise (to begin with), as i would just be able to copy/batch insert the external database, and the relations would be working right away. But i'm really worried about the scalability of this design: How many databases would i realistically be able to manage? Say i have 1000 or even 10000 customers? What if i want to add a column in an update - would i be able to perform some kind of loop-migration to all databases?

I really hope that some of you can help me move on with this as i am stuck and have no experience with solution 2 and 3.

Thanks in advance!

user3250613
  • 11
  • 2

3 Answers3

0

I would personally go for Solution 2 as that is probably the safest.

Solution 1 should be ruled out since you don't want to confuse the users of your application by modifying their data.

Solution 3 would probably be a pain to maintain and is more likely to fail (back-end of the application) + you will lose all track of whose database it is.

As for solution 2 that seems to me like the ideal one: I don't know what you are using (PHPMyAdmin or another type) but basically what you want to do is have 2 columns:

table
    id(PK, AI) original_id(PK)

and then just the rest of your table. Like this you will have your own Auto Increment (AI) key and you won't get any conflicts from your users since the combination of your auto_increment and that of the user is going to ALWAYS be unique.

for example:

user1:
    id = 1 | original_id = 1
user2:
    id = 2 | original_id = 1

This still works because the combination is unique. Another pro of using this composite UID is that you can still use your own id to perform queries or actions on the desired rows etc...

Hope this helps

Florian Humblot
  • 1,121
  • 11
  • 29
0

There are many things to consider when choosing an architecture, but from what you've described, I suggest you use Solution 3 because:

  • as you've very well pointed out, it's the best solution performance wise (especially if you end up with a lot of customers) and you won't need to handle the overhead of having large amounts of entries for all customers in one table
  • you have a clear database structure where only the necessary relations are present, no extra fuss to track different customers

As far as maintaining and updating database structure, you can can create Laravel Commands to automate running migrations for multiple databases. You can have a look at this answer to get an idea of how you could do that (although that situation is a little different from what you'll be needing, it offers some insight). Also anything else that needs to be handled in batch can be automated via Laravel commands or other scripts, so the amount of databases should not hinder maintenance.

Community
  • 1
  • 1
Bogdan
  • 43,166
  • 12
  • 128
  • 129
0

A more modern way of doing this is to use UUID as primary keys. If you also, when you import data have a source_uuid, import_time etc, in the table you can bookkeep all import (and export).

It might be hard to convince all parties to use UUID - but that is the best way go.

/gh

gorhas
  • 9
  • 1