Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    Unanswered: Are seperate databases betters?

    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,
    Bill

  2. #2
    Join Date
    Oct 2003
    Posts
    706

    Thumbs up

    If 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.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  3. #3
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  4. #4
    Join Date
    Jul 2002
    Posts
    3
    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, you’d have a thousand databases. Things like Schema updates, index rebuilds, etc. would become a significant activity.

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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.

  6. #6
    Join Date
    Oct 2003
    Posts
    706
    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, you’d 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."
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  7. #7
    Join Date
    Dec 2003
    Posts
    454

    Advantages of multiple databases

    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.

  8. #8
    Join Date
    Jul 2002
    Posts
    3
    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.

  9. #9
    Join Date
    Feb 2002
    Posts
    2,232
    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 ?

  10. #10
    Join Date
    Dec 2003
    Posts
    454

    advantages are much more than disadvantages

    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 customer’s 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •