1

Well, I know that it sound a bit weird but it's exactly what I want to do.

I got a rails application in which has 8 tables. One of them, a device created table, named: "partners".

I store each users in that table. But other than that, the other tables content should be unique for each partner. I was thinking to use some method after the authentication and set the database as db_"#{parentName}", but I don't know how to proceed with it. How can I create and use these different databases with the same structures?

Improve this question
3
  • 2
    It look like you want to do some kind of multi-tenancy. I guess you want each user to have his own version of the app/site. With postgresql you can use schemas, it is not possible in Mysql. Another common practice is to have a tenant_id in all tables to differentiate data ownership. Anyway, do a search about multitenancy, it is what you want to do. Commented Aug 22, 2014 at 9:15
  • I will have a high write rate and thats why i don't think postgresql was the right call to go, i m looking at multi tenancy right now.Thanks Commented Aug 22, 2014 at 9:28
  • Writing answer for you Commented Aug 22, 2014 at 9:28

2 Answers 2

Reset to default
2

SAAS

What you're looking at is multi tenancy:

enter image description here

This basically means you have a set of different databases (not just datatables) for a multitude of users. You'll generally have a central set of "public" data (such as users & options), and then a series of "tenant" databases which will house all the tables you need

Rails multi-tenancy has only really been achieved with PostgreSQL schemas before:

enter image description here

However, there is hope for MYSQL :)

MYSQL Multi Tenancy

The problem with MYSQL (true) multi tenancy is several-fold:

  1. Creating DataBASEes programatically is only possible with the "right" hosts
  2. Rails can only support one schema - meaning if you want "Tenant" DB's, you need to create functionality for multiple schemas
  3. You need a way to bind the creation of your databases with the creation of Accounts etc (probably with Resque)

We've actually worked through the first issue right now - our host RackSpace actually provides an API for its MYSQL database instances. After working with one of their incredible developers (Evan Light),we managed to get it a system in place whereby we can create MYSQL databases on the fly. Totally legit & programmatic :)

The next issue is something we're still working on - multiple schemas. This is somewhat trickier, as Rails seems to rely on creating the schema at db/schema.rb every time you perform a migration. For true multi-tenancy, you need to be able to handle schemas for your tenant db's, as well as your "main" db's. I don't have any resources for you on this right now unfortunately

Finally, you need to be able to link it all together. It will be highly inefficient if you tried to create a database "synchronously" - meaning that you handle it in the same flow as your application. You need to handle it "asynchronously" (in the background), freeing up your application's processes. This will best be done with a queuing mechanism such as Resque

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Rich, You are one of that tiny amount (permanently tending to 0) of authentic stackoverflow users, how put explanation as absolute priority, instead of simply pursuing accept check mark by posting answer. Infinite respect.
Haha thanks Andrey! Don't get me wrong, I like the reputation - but what's far more intriguing is the ability to polish my skills by working out some pretty in-depth issues (MYSQL multi tenancy being one on my hit list right now) -- thanks for taking the time to comment!
0

Just have one database, and make the user id part of the primary key in each table.

Improve this answer

4 Comments

it will be too populated since for one partner i m expecting around 80 million row for a month
But surely having a separate database for each user will require even more storage space.
i can manage the space but the query time in those tables must be fast
Providing you design your queries properly, there should not be any significant impact on execution times. The indexes will do all the heavy lifting for you. You may want to partition the tables though, in case you have a lot of full table scans.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.