Hi, I''m currently working on a web based app, something like a CRM tool. The idea is that a user can have its own database, separated from other users'' databases. There''s no point that user A should have access to data of user B. Is this (one database per customer) the right way to go? Can I manage this easily with RoR? How do I tell AR which DB to use? Would it be resource hungry? (100 users in parallel means 100 db connections...) Or should I go for 1 database? Thanks in advance for your opinion. Raph
Hi, I''ve previously created a web-based CRM (not in Rails though), and went for the single database approach. I found it quite easy to keep track of who owns what data, and were I to do it again I''d stick with that approach. If you use multiple databases then whenever you want to make a change to the schema you need to change it in all databases. It would make upgrades to the server quite a bit harder to do. It''s also a lot simpler using one database, since Rails seems to be suited to that, and using the associations you can place in the models it''s a piece of cake to access specific users'' data. Hope that helped a bit. Ben On Apr 9, 2005 8:28 PM, Raphael Bauduin <rblists-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> Hi, > > I''m currently working on a web based app, something like a CRM tool. > The idea is that a user can have its own database, separated from > other users'' databases. There''s no point that user A should have > access to data of user B. > > Is this (one database per customer) the right way to go? Can I manage > this easily with RoR? How do I tell AR which DB to use? Would it be > resource hungry? (100 users in parallel means 100 db connections...) > > Or should I go for 1 database? > > Thanks in advance for your opinion. > > Raph > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >
On Saturday, April 9, 2005, 8:28:06 PM, Raphael wrote:> Hi,> I''m currently working on a web based app, something like a CRM tool. > The idea is that a user can have its own database, separated from > other users'' databases. There''s no point that user A should have > access to data of user B.> Is this (one database per customer) the right way to go? Can I manage > this easily with RoR? How do I tell AR which DB to use? Would it be > resource hungry? (100 users in parallel means 100 db connections...)> Or should I go for 1 database?_Definitely_ one database. Organisations with datasets much much much much much larger than yours use a single database to keep all their related data. If your users are each going to have their own database _structure_, that''s a different story. But I doubt that''s what you have in mind. It''s easy to separate different users'' data within the one database. Say a user has many widgets, and each widget has many quarks. create table users ( id int auto_increment, login, password, etc. ) create table widgets ( id int auto_increment, user_id int, # Each widget belongs to a user ... ) create table quarks ( id int auto_increment, widget_id int, # Each quark belongs to a widget, ... # and therefore (indirectly) to a user ) I''m sure you get the idea. ActiveRecord helps you like this: class User < AR::Base has_many :widgets end class Widget < AR::Base belongs_to :user has_many :quarks end class Quark < AR::Base belongs_to :widget end Now, given a ''user'' object: user.widgets # All their widgets user.widgets.first # Their ''first'' widget user.widgets.first.quarks # All the quarks belonging to that widget Given a quark object: quark.widget.user # The user (indirectly) belonging to that quark Each user''s data is separate from other users'' data. Cheers, Gavin
> I''ve previously created a web-based CRM (not in Rails though), and > went for the single database approach. I found it quite easy to keep > track of who owns what data, and were I to do it again I''d stick with > that approach. > > If you use multiple databases then whenever you want to make a change > to the schema you need to change it in all databases. It would make > upgrades to the server quite a bit harder to do. It''s also a lot > simpler using one database, since Rails seems to be suited to that, > and using the associations you can place in the models it''s a piece of > cake to access specific users'' data.I would second the single database approach. This will also save your DB a lot of stress. On most RDBMSs each database on the server has to be allocated it''s own set of resources for caches and such. If there will be requirements specific to each user of your system, I would suggest accessing all data through views from the start. That way if a customization comes along, you can work off of a view specific to the user in question, and presents the necessary information. Steve
On Apr 9, 2005 12:43 PM, Gavin Sinclair <gsinclair-81uBx+iSpXA0n/F98K4Iww@public.gmane.org> wrote:> On Saturday, April 9, 2005, 8:28:06 PM, Raphael wrote: > > > Hi, > > > I''m currently working on a web based app, something like a CRM tool. > > The idea is that a user can have its own database, separated from > > other users'' databases. There''s no point that user A should have > > access to data of user B. > > > Is this (one database per customer) the right way to go? Can I manage > > this easily with RoR? How do I tell AR which DB to use? Would it be > > resource hungry? (100 users in parallel means 100 db connections...) > > > Or should I go for 1 database? > > _Definitely_ one database. Organisations with datasets much much much > much much larger than yours use a single database to keep all their > related data. > > If your users are each going to have their own database _structure_, > that''s a different story. But I doubt that''s what you have in mind. >Database structure will be the same for all users indeed. I''ll use the ideas described in this document http://techdocs.postgresql.org/oscon2004/joe.conway/pres_oscon_2004-r1.pdf to let users have customisability in the list of attributes attached to a table. And that led me to wonder if I should create one database for each user (no need to track which user added which attribute, and the table holding values could become _huge_ in no time). I''m using postgresql, and I also wondered if shouldn''t use namespaces rather than separate databases. In the end, one database is certainly the way to go. Thanks for all the enlightening answers. Raph> It''s easy to separate different users'' data within the one database. > Say a user has many widgets, and each widget has many quarks. > > create table users ( > id int auto_increment, > login, password, etc. > ) > > create table widgets ( > id int auto_increment, > user_id int, # Each widget belongs to a user > ... > ) > > create table quarks ( > id int auto_increment, > widget_id int, # Each quark belongs to a widget, > ... # and therefore (indirectly) to a user > ) > > I''m sure you get the idea. ActiveRecord helps you like this: > > class User < AR::Base > has_many :widgets > end > > class Widget < AR::Base > belongs_to :user > has_many :quarks > end > > class Quark < AR::Base > belongs_to :widget > end > > Now, given a ''user'' object: > > user.widgets # All their widgets > user.widgets.first # Their ''first'' widget > user.widgets.first.quarks # All the quarks belonging to that widget > > Given a quark object: > > quark.widget.user # The user (indirectly) belonging to that quark > > Each user''s data is separate from other users'' data. > > Cheers, > Gavin > >