Hi. I have been talking with some developers who have built a hosted application supporting multiple customers. Their database approach is to create a new, dedicated database (same schema each time) for every customer that signs-up.
This approach is contrary to typical hosted DB designs that I have delt with -- that is, a single database holding multiple customer information rather than a unique database for each customer.
Does the improved security of a dedicated database out-weigh the additional maintenance requirements?
If anyone has some objective thoughts on this topic, I'd love to hear them.
Thanks,
BillIf this is a "hosted" application for, say, a cadre of retail stores each one of which has its own block of customers, products, sales, etc to deal with, then the stores are the hosting-company's "customers."
Each store expects to see and to deal with only its own set of information, and considers that information to be highly proprietary.
The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers. I think that having separate databases, with separate login-IDs etc, is really the only scenario that would work.|||It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?|||Thanks for the replies.
[The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers...]
Even with a single database, reasonable precautions are taken. All customers have separate logins and distinct logical segregation of data. The approach is roughly analogous to a bank safe with safe boxes holding the possessions of multiple parties.
[It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?]
I think if you were to ask any given customer, their knee-jerk reaction would be to say that they want their own database. But other than the fear of co-mingling of data, there are no specific scale or performance reasons that would demand it.
My concern with separate databases is the administrative overhead if you have a thousand customers, youd have a thousand databases. Things like Schema updates, index rebuilds, etc. would become a significant activity.|||My biggest worry would be if someone from company A deletes half their data. After the restore, company B asks "Where is my data from today". Just my .02 USD.|||Originally posted by billdavidheiser
[The hosting-company will have, ex minimis, a warehouseman's fiduciary duty with respect to the information, extending not only to the stores but indirectly to their customers...]
Even with a single database, reasonable precautions are taken. All customers have separate logins and distinct logical segregation of data. The approach is roughly analogous to a bank safe with safe boxes holding the possessions of multiple parties.
[It really depends on the requirements - Do the customers need to have additional isolation(security/fault tolerance/performance) ? How much data will be stored for each customer and what data will be maintained ?]
I think if you were to ask any given customer, their knee-jerk reaction would be to say that they want their own database. But other than the fear of co-mingling of data, there are no specific scale or performance reasons that would demand it.
My concern with separate databases is the administrative overhead if you have a thousand customers, youd have a thousand databases. Things like Schema updates, index rebuilds, etc. would become a significant activity.
As usual, what it really comes down to is: "it depends." Multiple databases make it less likely that the wrong customers' data will be visible but have their own negative implications as well. I think that the best advice, borne out by this little discussion, is that the designer must be keenly aware of the tradeoffs that will inevitably be made with any decision. Every decision is a compromise. No matter how forcibly a particular position may be argued on a forum ;) it is neither "right" nor "wrong."|||Multiple databases have the following advantages:
If one or some customers ask for his or their data backup you can easily do it.
If one or some customers ask for some changes which may be related to the table structures, you can easily handle it.
If one or some customers leave, you can easily backup and delete (drop) his or their databases without cleaning his or their data in one database.|||Originally posted by sundialsvcs
As usual, what it really comes down to is: "it depends." Multiple databases make it less likely that the wrong customers' data will be visible but have their own negative implications as well. I think that the best advice, borne out by this little discussion, is that the designer must be keenly aware of the tradeoffs that will inevitably be made with any decision. Every decision is a compromise. No matter how forcibly a particular position may be argued on a forum ;) it is neither "right" nor "wrong."
["it depends."]
Ah, very helpful. Thank you. Are you an Economics professor by any chance? :)
Seriously though; I understand what you are saying and agree. I was hoping to hear if anyone out there has a real-world experiance with trying to maintain a boat-load of individual customer databases.|||MCrowley brings up a good point which ties into fault tolerance. What happens if the database is suspect or part of it is corrupt - all of your users would be hosed if using 1 database.
What about the amount of data being stored per customer and the frequency of updates/deletes/inserts per customer ? Is the amount of data fixed per customer and do they infrequently change data ?|||The company that I'm working for has individual customer database for each customer. All databases have the same structure except some extra fields for some databases based on the customers requirements. We had some problems which happened to some of databases and the websites related to those databases were affected. In this case, individual customer database shows an advantage.
For the maintenance of the databases, since they have almost same structure you can use a script to do a job for all databases. I think that the advantages are much more than disadvantages.
