Best practice for holding data for multiple users/customers

[Sam Martin]

Hi all,

I posted a question a while back on SO but didn't get a huge response and I was wondering what the general opinion would be.

I want to use a database to server data for multiple customers but am considering a number of different ways to segregate the data.

Here's the original question...

My question is what is the most effective way to model a database (or database) for multiple clients.

Given how easily new databases can be created and used I was thinking that it may be good to use separate databases per client to isolate the data.

Then I read several questions like this asked already and from what I can see the general consensus is that there will be physical limits on the OS/File handles - so the preferred approach would be to use a single database with multiple collections.

Would it therefore be best to create a collection of each type for each customer, e.g. db.mycollection_cust1234 or to hold a reference to a customer doc in each collection and create a compound index on ClientId and the _id?

Any advice / experience welcome.

EDIT: in response to dubs' comment, would it therefore best to have to have:

// Equipment collection with CustomerId
{ EquipDesc = 'Hammer', CustomerId = ObjectId(xyz) }

// or Equipment_xyz (and one for each customer)
db.Equipment_xyz.insert( { EquipDesc : 'Hammer' } );
Thanks in advance,

Sam

Hopefully it's clear from the original question what I'm trying to do.

Thanks in advance

Sam

[Joel Lucsy]

I think this all depends on what volume of "customers" you intend to have. Since it doesn't sound like you're considering sharding, I suspect low numbers are in order. Of which, then, pick which scenario best fits your programming and/or data.

Sam

--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongod...@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user...@googlegroups.com
See also the IRC channel -- freenode.net#mongodb

[Spencer T Brody]

There are plusses and minuses to the multi-database approach.  If you use one database per client, then for every database you'll have ~200mb of data use at minimum due to data file preallocation.  However, in the upcoming 2.2 release there will be database level locking, so you'll get more concurrency by using multiple databases.  Also, user authentication and authorization is done on a per-database level, so if you want to have different access control for different clients it may make sense to use a separate database per client.

As for whether you should use a single collection or multiple collections for a single customer, it depends on how you query the data.  If your usual queries are all for the same single customerId, then splitting things into multiple collections could work.  But if you ever want to do a query across all customers (such as, find me all the hammers used by any customer), then that will be difficult to do with the multiple collection model, it'd be much easier if they were all in the same collection.