The use of multi-user rails: what are the pros and cons of various methods?

Question

I originally wrote a Ruby on Rails application for one client. Now I am changing it so that it can be used for different clients. My ultimate goal is that some users (not me) can click a button and create a new project. Then all necessary changes (new schema, new tables, code processing) are generated without the need to edit the database.yml file or add new schema definitions. I am currently using SCOPED access. So I have a project model, and other related models have a project_id column.

I looked at other posts regarding multi-tenant applications in Rails. Many people seem to suggest creating a different scheme for each new client in Postgres. However, for me it is not very useful for the new client to have a different scheme in terms of data model. Each client will have the same tables, rows, columns, etc.

My vision of each client is that in my production database there is a table of different projects / clients. And each of these tables refers to a set of tables that are almost the same for different data. In other words, a table of tables. Or, in other words, the first table will display a different data set for each client that has the same structure.

I explain my vision, similar to how Postgres implements different "schemes"? Does this look like nested tables? Or should Postgres still request all the information in the database? I am not currently using Postgres, but I would like to know if this matches the design. If you know a database software that works with Rails that suits my needs, let me know.

I am currently using scopes to run multiple tenant applications, but it does not feel scalable or clean. However, it is very easy for a non-technical user to create a new project if I give them fill-in information. Do you know if this is possible when defining a Postgres multiple schema so that it works automatically after a user clicks a button? And I would prefer it to be handled by Rails rather than an external script, if possible? (consult anyway)

Most importantly, do you recommend any plugins or should I adopt a different structure for this task? I found that Rails is limited in some cases of abstraction, as mentioned above, and this is the first time that I have encountered the problem of scaling Rails.

Any advice related to multi-tenant applications or my situation is welcome. Any questions for clarification or additional advice are also welcome.

Thanks, --Dave

Answer 1

Remember to use the default scopes by creating named scops the way you work now, it feels like it can be done better. I met this guide from Samuel Kadolf regarding this issue a few months ago, and it looks like this might work well for your situation and have the advantage of keeping your application without any PgSQL features.

Basically, the way he describes application setup involves adding tennant concepts to your application and then using them to capture data during a query using a database.

Answer 2

MSDN has a good idea

At the other end of the spectrum, you store the tenant ID number in each row of each shared table ("shared all"). “All shared” makes disaster recovery difficult — for a single tenant you only need to recover a few rows in each shared table — and it has the lowest degree of isolation. (Poorly formed requests can provide private data.) But it has the lowest cost for each tenant, and it supports the largest number of tenants per server.

My vision for each client is that my production database first has a table of different projects / clients. And each of these tables links to a set of tables that are almost the same for different data. In other words, a table of tables. Or, in other words, the first table will display a different data set for each client that has the same structure.

It looks like you are talking about one scheme for each tenant. Pay particular attention to permissions (SQL and and.)

Answer 3

There are two railscasts on multitenancy that use areas and subdomains , and the other for handling multiple schemes .

There is also a multi-color gem that can help with your areas and a gem of an apartment to handle multiple patterns.

There is also a good presentation on multitenancy-with-rails .