我应该使用多个数据库吗? [英] Should I use multiple databases?

问题描述

我将要使用Ruby on Rails创建一个应用程序，并且我想使用多个数据库，基本上是一个会计应用程序，每个用户都有多个公司.我想为每个公司创建一个数据库

I am about to create an application with Ruby on Rails and I would like to use multiple databases, basically is an accounting app that will have multiple companies for each user. I would like to create a database for each company

我发现了此帖子 http://programmerassist.com/article/302 但我想阅读更多有关此问题的想法. 我必须在MySQL和PosgreSQL之间做出选择，哪种数据库可能更适合我的问题.

I found this post http://programmerassist.com/article/302 But I would like to read more thoughts about this issue. I have to decide between MySQL and PosgreSQL, which database might fit better my problem.

推荐答案

有多个处理多租户应用程序的选项.

There are several options for handling a multi-tenant app.

首先，您可以向表中添加范围(如Chad Birch所建议-使用company_id).对于大多数用例来说，这很好.如果您要处理安全/私有的数据(例如会计信息)，则需要非常小心地进行测试，以确保数据保持私有.

Firstly, you can add a scope to your tables (as suggested by Chad Birch - using a company_id). For most use-cases this is fine. If you are handling data that is secure/private (such as accounting information) you need to be very careful about your testing to ensure data remains private.

您可以使用多个数据库运行系统.您可以有一个为每个客户端使用数据库的应用程序，或者实际上可以为每个客户端使用单独的应用程序.为每个客户运行一个数据库可以减少一些麻烦，但这是可行的.根据您拥有的客户端数量和预期的负载，我实际上建议您看看运行单个应用程序.通过一些部署设置工作(capistrano，chef，puppet等)，您可以使此过程非常简化.每个客户端都在一个完全独特的环境中运行，并且如果特定的客户端具有很高的负载，则可以将它们分解到自己的服务器上.

You can run your system using multiple databases. You can have a single app that uses a database for each client, or you can have actually have a seperate app for each client. Running a database for each client cuts a little against the grain in rails, but it is doable. Depending on the number of clients you have, and the load expectations, I would actually suggest having a look at running individual apps. With some work on your deployment setup (capistrano, chef, puppet, etc) you can make this a very streamlined process. Each client runs in a completely unique environment, and if a particular client has high loads you can spin them out to their own server.

如果使用PostgreSQL，则可以使用模式执行类似的操作. PostgresQL模式提供了一种非常方便的方式来隔离来自不同数据的数据客户.数据库包含一个或多个命名模式，而这些命名模式又包含表.您需要在迁移和部署中添加一些技巧，但是效果确实很好.

If using PostgreSQL, you can do something similar using schemas. PostgresQL schemas provide a very handy way of islolating your data from different clients. A database contains one or more named schemas, which in turn contain tables. You need to add some smarts to your migrations and deployments, but it works really well.

在Rails应用程序中，将过滤器附加到打开或关闭当前用户的模式的请求.

Inside your Rails application, you attach filters to the request that switch the current user's schema on or off.

类似的东西:

before_filter :set_app

def set_app
  current_app = App.find_by_subdomain(...)
  schema = current_app.schema

  set_schema_path(schema)
end 


def set_schema_path(schema)
  connection = ActiveRecord::Base.connection
  connection.execute("SET search_path TO #{schema}, #{connection.schema_search_path}")
end

def  reset_schema_path
  connection = ActiveRecord::Base.connection
  connection.execute("SET search_path TO #{connection.schema_search_path}")
end

这篇关于我应该使用多个数据库吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！