Hello- Regarding a web application that is provided to several different customers, my original plan was to have separate databases for each customer. The Rails app would then choose the right database determined by the customer''s login. Others suggested rolling everyone''s data into the same database. My question regarding this method is how to segregate the data. I could add a condition like "WHERE customer_id = ?", but it would nice to somehow add this to -all- queries (DRY, right?). Is this possible? Jake -- Posted via http://www.ruby-forum.com/.
Christer Nilsson
2005-Dec-01 20:37 UTC
Re: Additional database condition in -all- queries?
There is no need to do this for all queries. Example: Every customer has some orders. Every order has some orderlines. The orderlines do not need to have the customer_id repated. By following the foreign keys orderline.order_id and order.customer_id, the customer is found. -- Posted via http://www.ruby-forum.com/.
Jake Janovetz wrote:> Hello- > > Regarding a web application that is provided to several different > customers, my original plan was to have separate databases for each > customer. The Rails app would then choose the right database determined > by the customer''s login. > > Others suggested rolling everyone''s data into the same database. My > question regarding this method is how to segregate the data. I could > add a condition like "WHERE customer_id = ?", but it would nice to > somehow add this to -all- queries (DRY, right?). Is this possible? > > Jake >David Heinemeier Hansson described how this is done in applications like Basecamp, in the thread "Basecamp database model". The question being asked was: "It seem like if we are using the framework extracted from the Basecamp app it would be good to know a little about the database model structure. In particular I wonder how multiple projects are handled for one account. I would like to have multiple storefronts for an ecommerce application and think this might be similar to multiple projects in basecamp." DHH''s answer: A few clues: * http://wiki.rubyonrails.com/rails/pages/HowToUseSubdomainsAsAccountKeys * http://dev.rubyonrails.org/svn/rails/plugins/account_location/ This is naturally not verbatim, but for this discussion it''ll serve: class Account < ActiveRecord::Base has_many :projects end class Project < ActiveRecord::Base belongs_to :account has_many :milestones end class Milestone < ActiveRecord::Base belongs_to :project end So you can say Account.find(:first).projects.first.milestones to get all the milestones of the first project that belongs to the first account.
christer wrote:> There is no need to do this for all queries. > > Example: Every customer has some orders. Every order has some > orderlines. The orderlines do not need to have the customer_id repated. > By following the foreign keys orderline.order_id and order.customer_id, > the customer is found.Thanks. While I agree this can be relaxed in some cases, it also needs to be there for security purposes, doesn''t it? In particular, I don''t want people writing their own URLs and gaining access to another customers'' data. This -should- be taken care of by the account system which I have in place, but it''s difficult to consider all possible ways people can infiltrate a system. I''m trying to wrap my head around that with this question. -- Posted via http://www.ruby-forum.com/.
Thanks Justin -- I''ll need to think about this a bit more. Jake justin wrote:> David Heinemeier Hansson described how this is done in applications like > Basecamp, in the thread "Basecamp database model". > > The question being asked was: > "It seem like if we are using the framework extracted from the Basecamp > app it would be good to know a little about the database model > ...-- Posted via http://www.ruby-forum.com/.