Geek Answers Handbook

Best Database Strategy for a Client Website (Ruby on Rails)

I have created a good website system that meets the needs of a small niche market. I have been selling these sites over the past year, deploying copies of the software using Capistrano on my web server.

It seems to me that the only difference in these sites is a database, a CSS file and a small set of images used for graphic design of individual clients.

Everything else is exactly the same or should be ... Now that I have about 20 of these sites deployed, it is becoming increasingly difficult to maintain them all in the same code. And this problem will only get worse.

I think I need to reorganize this system so that I can use one set of deployed ruby ​​codes, dynamically select the correct database, etc. at the URL of the incoming request.

There seem to be two ways to process the database:

  • using multiple databases, one for each client
  • using one database with client_id field in each table and additional client table

The multiple database method would be the easiest for me at the moment, since I would not have to reorganize every model in my application to add the client_id field to all CRUD operations.

However, it would not be easy to run "rake db: migrate" for tens or hundreds of different databases, every time I want to migrate a database. Obviously this can be done with a script, but it doesn't smell very good.

On the other hand, each customer will have 20K-50K items in the "items" table. I am worried about the speed of full-text search when the element table contains half a million or a million elements. Even with the index in the client_id field, I suspect that the search will be faster if the items are split into different client databases.

If someone has an informed opinion on the best way to solve this problem, I would really like to hear it. Thank you very much in advance...

- John

+5
3

( , ):

  • ( ), .
  • . , , . ( ) . , , .
  • - , , client_id. , , .
  • - , , , .
  • : , , .;)

, , , :

  • .
  • , .
  • , / .
+2

. . , .

, before_filter application_controller, ... - :

before_filter :client_db         # switch to client db

application_controller.rb - :

 def client_db
    @client = Client.find(params[:client_id]) 
    spec = Client.configurations[RAILS_ENV] 
    new_spec = spec.clone 
    new_spec["database"] = @client.database_name
    ActiveRecord::Base.establish_connection(new_spec) 
  end

URL-, example.com?client_id=12345, .

Apache - Mongrel, Apache client_id URL- - . , client_id URL-, . Apache Mongrel. , , .

, , . , -, .

- ?

-

+4

, - , , .

You can use an indexing library like Ferret or something like that to deal with the problem of slow full-text search. This will be a problem anyway when one client database becomes large, so you may need to implement this anyway.

+1
source

More articles:


All Articles