Additional database condition in -all- queries?


#1

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


#2

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.


#3

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.


#4

Thanks Justin – I’ll need to think about this a bit more.

Jake

justin wrote:

David Heinemeier H. 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


#5

Jake J. wrote:

somehow add this to -all- queries (DRY, right?). Is this possible?

Jake

David Heinemeier H. 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

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.