Hello,
I was looking for suggestions on RoR app design. My app is going to be
accessed by multiple people belonging to multiple companies. Each
person will only be able to working with their company’s data. Also
each person will have a role and permissions within the application.
The role based auth. I have. My question is how to make sure that each
company can only work with their company’s data. Should each company
have their own instance of the application (by this I mean a site
specific to them). This seems like it would make the database easier to
work with but deployment a pain, or should all the companies data
reside in the same db. If this is the case, how do I filter all
queries through ActiveRecord to only choose from their company’s
data? I would hate to have to put a condition into every find() call. I
am new to rails so I may be asking a ridiculously stupid question but I
have searched around a lot and have not found any info on this other
that don’t use multiple db’s with rails. From this I assume from
within the same app, not instance.
Thanks
Ryan
Hi !
2006/10/4, [email protected] [email protected]:
queries through ActiveRecord to only choose from their company’s
data? I would hate to have to put a condition into every find() call. I
am new to rails so I may be asking a ridiculously stupid question but I
have searched around a lot and have not found any info on this other
that don’t use multiple db’s with rails. From this I assume from
within the same app, not instance.
Given:
class User < AR::Base
has_many :contacts, :order => ‘name’
end
class Contact < AR::Base
belongs_to :user
end
It is possible to do this:
current_user.contacts.find(:all, :conditions => [‘name LIKE ?’, ‘a%’])
ActiveRecord “knows” to generate the following SQL:
SELECT * FROM contacts WHERE user_id = 39 AND (name LIKE ‘a%’) ORDER BY
name
This is called a “scoped find”. You may also want to take a look at
this: Peak Obsession
Hope that helps !
François Beausoleil
http://blog.teksol.info/
http://piston.rubyforge.org/
Bala P. wrote:
Short answer is : You will have each company id in some table pointing to several records in
another table. When a user logins in you will know what company the user belongs to, so you can
save the company id in the session for the user. When you access the database you have to use the
company id to retrieve the records corresponding to that company.
I understand this.
My question was if I use this method do I have to set a :condition in
each X.find() using ActiveRecord. I think that Francois B.
answered my question. I was unaware of the scoped find. Thanks
Ryan
I would suggest that you pick up a very basic book on database design.
The problem that you have
explained is frequently encountered in the real world database design.
Short answer is : You will have each company id in some table pointing
to several records in
another table. When a user logins in you will know what company the user
belongs to, so you can
save the company id in the session for the user. When you access the
database you have to use the
company id to retrieve the records corresponding to that company.
I just looked back at my original post, and I was not very clear at all
in what I was asking for. Sorry about that.
Richard C. wrote:
If that is not the case (it would be rare), then everything gets tagged
with
a company id.
I guess the question becomes: Is there a way to have AR automatically
use the company_id as a has_many condition? As far as I know, you can
only put static conditions in an association.
-matthew
I am sorry that I wasn’t clear. The questions I have are not really
about
DB Design. This is more about the rails framework itself.
I was just wondering what was the best way to deploy/design the
application. Ok so say that I would like to keep all of the companies
data
in different DBs. Now how do I dynamically deploy or configure the
application to connect to the correct db? Again this is not about
database
design issues.
Ryan H. wrote:
in different DBs. Now how do I dynamically deploy or configure the
application to connect to the correct db? Again this is not about
database
design issues.
The easiest thign to do would be to keep the source in svn and create 3
different working copies, each referencing a different DB. This way you
could make company specific brandings by changing CSS and
images and such. Look into Capistrano for deployment automation.
-matthew
thats easier? Why not have a separate db for each user? or each
payment plan? or each role?
Splitting it up into separate db’s won’t scale. And splitting the data
into separate db’s won’t make it ‘more secure’.
The idea of grouping data together and putting access rights on those
groups is not uncommon in a db-based application. This is just the
next logical step after creating the initial user authentication.
Adam
On 10/4/06, [email protected] [email protected] wrote:
work with but deployment a pain, or should all the companies data
reside in the same db.
There are other issues to consider here too. Perhaps the companies
themselves
may have objection to their data being merged in with other companies.
If that is not the case (it would be rare), then everything gets tagged
with
a company id.
Thats the basics, but you will need to do some solid research on DB
design.
I guess we’ll just have to disagree then 
My experience comes from the institutional financial industry,
specifically prime brokerage, where we have complex relationships with
a few hundred hedge funds. Talking about demanding clients! We keep
everything in one place as it makes it easier to maintain reference
data, fix issues, etc. In fact back in the 80’s, supposedly every
hedge fund’s data was kept separately, but maintaining that as we grew
became very expensive. Plus that flexibility of allowing different
upgrade paths for each company became a nightmare to maintain…
Everything was merged starting in the late 90’s.
Simplicity is in the eye of the beholder I guess!
Adam
piggybacking on Francois B.'s suggestion to use a scoped find,
per recipie number 28 put your with_scope() call inside a
before_filter() call, which DRYs up your code and helps you not forget
to use the with_scope() statement. (just don’t forget that you are now
always using it!)
this of course applies only if you are using one DB which i strongly
recommend unless there is a compelling reason not to.
Adam G. wrote:
thats easier? Why not have a separate db for each user? or each
payment plan? or each role?
Because then you couldn’t have meaningful relationships between your
users, thats why. Having seprate database instances (and even code
bases) for diffnent companies is reasonable because you don’t need to
cross reference between companies. They are independent data sets,
hence, different databases.
One reason to put different companies on different database instsance:
What if you came out with a new release of your applicaiton but didn’t
want to upgrade all the companies at once? Maybe one of them wants to be
a beta tester for new features. There are plenty of good reasons to keep
indepenedent data sets separate… the least of which is security.
Splitting it up into separate db’s won’t scale. And splitting the data
into separate db’s won’t make it ‘more secure’.
It’ll “scale” as well as any other setup.
The idea of grouping data together and putting access rights on those
groups is not uncommon in a db-based application. This is just the
next logical step after creating the initial user authentication.
Ryan said to assume he was going to put each company in their own DB. He
wanted to know how to deploy that. And I was responding to that.
-matthew
On 10/7/06, Adam G. [email protected] wrote:
upgrade paths for each company became a nightmare to maintain…
Everything was merged starting in the late 90’s.
We used to keep data separated also for financial data, but are moving
away from it for the same reasons. sql functions and things like
rules and views are great tools for making sure data doesn’t get
mixed, while still having a sane schema.
Chris
Thank you all for the advise.
Ryan