Software as a service - Database

views:

109

answers:

+1 Q:

Software as a service - Database

If I am building a CRM web application to sell as a membership service, what is the best method to design and deploy the database?

Do I have 1 database that houses 100s of records per table or deploy multiple databases for different clients?

Is it really an issue to use a single database since I believe sites like Flickr use them?

+6 A:

Multiple clients is called "multi-tenant". See for example this article "Multi-Tenant Data Architecture" from Microsoft.

ChrisW 2009-09-18 20:27:52

@Theo: why dont you design your system to work in both setups: 1) different databases and 2) same database, but different schemas. Basically your client app gets a db connection for a client in some generic way and just works with the DB. In this case you can configure your deployment and also change from one to the other if needed.

van 2009-09-18 20:49:43

@van: I just read the article about Multi-Tenant Data Architecture, quite informative. Your comment makes sense, it's just that the different schema method is apparently quite a bit of work to restore backups per schema. Though the major benefit with the schema option is the ability for MS SQL to handle more deployments.TOUGH choices to make here

Theo 2009-09-18 21:20:39

@chrisW: After reading that article, it all really comes down to the scenario in which the SaaS application is built and the security implications. For my current situation though, the intensive isolated option seems the more logical choice allowing for independent scalability per client without the gray area that the schema option brings about.

Theo 2009-09-18 21:24:58

Long term it is easiest to maintain one database with multiple clients' data in it. Think about deployment, backup, etc. However, this doesn't keep you from having several instances of this database, each containing a subset of the full client dataset. I'd recommend to grow the number of databases after you have established the usefulness/desirability of your product. Having complex infrastructure is not necessary if you have no traffic....

So, I'd just put a client id in the relevant tables and smile when client 4 comes in and the extent of your new deployment is one insert statement.

joeslice 2009-09-18 20:28:08

+3 A:

In a situation like a CRM system, you will probably need to have separate instances of your database for each customer.

I say this because if you'd like larger clients, most companies have security policies in place regarding customer data. If you store their customer data in the same database as another customer, you're running the risk of exposing one companies confidential data to another company (a competitor, etc.).

Sites like Flickr don't have to worry about this as much since the majority of us out on the Interwebs don't have such strict policies regarding our personal data.

Justin Niessner 2009-09-18 20:29:03

+1 for security concerns. in CRM world one used to think that hosted solutions were not even possible because of security concerns. salesforce.com proved it wrong though, but they try their best to prove their customers their data IS secure. you do have to have more automation for multiple db backups etc. But it is also easier to migrate/expand customers data and update to new database structures one-by-one.

van 2009-09-18 20:39:30

Yes indeed this is true. We even have clients who won't let us store data on the same server in a different database and one who even made the server be in a differnt building. We also had a very embarassing incident about three years ago when a developer forgot to put the client id in a query and emailed proprietary data to sales reps from several other companies who were in the same database. Believe me all our large clients are on separate systems now.

HLGEM 2009-09-18 20:42:55

... on the other hand, with many small clients that do use your system a lot simultaneously, you might have issues with connection pooling.

van 2009-09-18 20:46:48

ansaurus

tags:

views:

answers:

Software as a service - Database

related questions