4

I'm about to finish building a simple subscription based support ticket Web app. I'm setting up authorization. But since this it's going to be my very own Web app that I'm going to deploy I'm wondering about this.

Do you create a separate database per account opened?

Let's say you have this support ticket Web app. You have ONE and ONLY ONE account owner. Account owner can setup agents that can respond to support tickets. Also, there are customer roles that open support tickets.

So as you can see the database will contain users, support tickets and more.

What is the best way to go?

1) Create one database for the whole application? That way every time somebody signs up, everything is added to the same database with the other tickets and users data and everything else or...

2) Everytime someone signs up, create a separate database per account subscription.

I'm thinking that maybe option number 2 would be a best choice for security and data integrity purposes. If so, how have you gone about tackling this issue?

leonel
  • 10,106
  • 21
  • 85
  • 129

2 Answers2

6

It sounds like what you want is Multitenancy:

Multitenancy refers to a principle in software architecture where a single instance of the software runs on a server, serving multiple client organizations (tenants). Multitenancy is contrasted with a multi-instance architecture where separate software instances (or hardware systems) are set up for different client organizations. With a multitenant architecture, a software application is designed to virtually partition its data and configuration, and each client organization works with a customized virtual application instance. - Wikipedia article on Multitenancy

This article while a little dated is the general idea of how I would go about doing it. Simple Rails Multi-Tenancy. It's clean and efficient and saves you from writing code that you don't need to.

Devin M
  • 9,636
  • 2
  • 33
  • 46
4

You should go for option #1. Number 2 is (almost (there are probably cases where it is good, but I can't find one at the moment)) never an option.

You are right in security purposes (well, in a sense), but it also creates a lot of other problems that you will have to think about.

  1. Having a different database for each user means that for each request (remember, HTTP is state-less) you will have to open up a new connection to the database, do whatever needs to be done and then close the connection again, instead of using the connection pooling that is in Rails. This affects the performance a great deal.

  2. Administration will be a hassle the more databases you have. Also, having multiple databases on a server do require more resources than just a bigger database.

  3. You would have to circumvent the entire connection handling in Rails since there it is usually one database per application. It is easy to change the database for specific models, but it adds additional places where things can go wrong.

  4. Rails do have good functionality for scoping and handling of separating data within the same database, just for that kind of use-case that you are mentioning.

Jimmy Stenke
  • 11,140
  • 2
  • 25
  • 20