App Design

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 !

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: http://api.rubyonrails.com/classes/ActiveRecord/Base.html#M000892

Hope that helps !

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.

Bala Paranj 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 Beausoleil
answered my question. I was unaware of the scoped find. Thanks

Ryan

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.

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 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.

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

<shrug/> I guess we'll just have to disagree then :wink:

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 Beausoleil'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.

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