Results 1 to 7 of 7
  1. #1
    Join Date
    May 2008
    Posts
    3

    One Huge Database vs. Many Identical Databases

    Hello,

    We are working on a project in which there will be multiple online shops, each "owned" and managed by different clients. The shops will be similar in nature (mostly identical) but each containing its own, unique data. Data would not, in principle, be shared by different clients.

    At this point we are at the database / application design phase. We are debating the following essential question: Should we design one huge comprehensive database to store everyone's data or should we set up the application to create a new instance of the database model for each individual client?

    Some pro's and con's to each...

    One Big Database
    PROS
    • Only one database to backup
    • simplifies application design in that queries to the database are always the same, using "ShopID" to differentiate between clients
    • modifications need only be made once

    CONS
    • Security: we'll try to be secure, of course, but what if we get hacked? ALL data for ALL shops is at risk.
    • Customization: What if a client needs some sort of customization? It could become very complex trying to add features for a specific client.
    • Inital Design will be far more complex and bug-prone


    Lots of distinct, identical databases
    PROS
    • Easy to modify each database to fit specific client needs
    • compartmentalized data for each client
    • Security: if one db gets hacked, it's only ONE, a lot more manageable than the entire system being compromised


    CONS
    • Making a global change to the application / db model would mean updating potentially hundreds or thousands of distinct databases, which sounds horrible in itself, but could also give way to naming or other conflicts arising in some cases
    • Would have to backup and manage hundreds of databases
    • db user management becomes much more complex?
    • Might be heavier server load?


    We have had a fair bit of DB design experience for individual shops but have never had to do something this large. We'd appreciate your comments and discussion. Thanks in advance!

  2. #2
    Join Date
    May 2008
    Posts
    3
    I found this thread which is very interesting, and seems to touch on exactly this topic:

    http://discuss.joelonsoftware.com/de...gn.4.319460.16

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    If your company is going to be responsible for the administration of these databases, then one big one will be vastly easier to manage - I would not consider the alternative of 100s of similar databases. The major DBMSs should have facilities to help with the security side of things; for example, in Oracle there is "Virtual Private Database" (VPD) to ensure that each customer can only access their own data.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I would agree with andrew on this. Each client would probably be given access to named views of the data, and not direct access to the underlying tables.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    One Big Database
    Customization: What if a client needs some sort of customization? It could become very complex trying to add features for a specific client.
    Surely you just add the new feature into the feature set for the whole system. You get the client who wants the feature to pay for the development then once the client has used the feature for a while you release the feature as a optional feature to other clients that they have to pay for. Eventually it becomes a standard feature for all clients. If the idea doesn't suit a global deployment then you just say sorry we can't do it.

    Inital Design will be far more complex and bug-prone
    Why? you're just adding a client_id to the tables or you're using the views. I assume you must believe having having 100's of separate developments going on for different clients is going to be easier?

    Lots of distinct, identical databases
    Easy to modify each database to fit specific client needs
    The idea of having 1000's of nearlly identical databases where each one has something subtly different gives me the shivers. I assume you have 1000's of staff to work on these different databases.

  6. #6
    Join Date
    Sep 2005
    Posts
    9
    Greg

    The following link may be useful for you (it was for our situation)
    http://msdn.microsoft.com/en-us/library/aa479086.aspx

    Eventually it becomes a standard feature for all clients. If the idea doesn't suit a global deployment then you just say sorry we can't do it.
    Mike, sadly this is not always possible. For some industries (e-learning in our case) client requirements can vary greatly. The client still pays for the customisations, but these need to be forever isolated from all other customers.

    Inital Design will be far more complex and bug-prone
    I imagine Greg was considering the client customsations when making this comment.

    1000's of nearlly identical databases where each one has something subtly different gives me the shivers.
    Not all systems require that scale of databases, but we have the same concerns. I imagine this could work with carefully managed schema (for deployments and upgrades) and corresponding application metadata.

    We are currently researching this issue. We have already decided to allocate a separate database for each client. Our current investigation is to determine how extensions will best suit our requirements. At this stage, extending the schema with client specific tables seems to be the approach we will take and our task is determine how best to manage this.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by kevan
    sadly this is not always possible. For some industries (e-learning in our case) client requirements can vary greatly.
    kevan, I have no idea about your requirements but I was posting in reply to Greg
    Quote Originally Posted by gregrobbins
    The shops will be similar in nature (mostly identical) but each containing its own, unique data.

    Quote Originally Posted by kevan
    Not all systems require that scale of databases,
    Again it was more in response to Gregs original request...
    Quote Originally Posted by gregrobbins
    db model would mean updating potentially hundreds or thousands of distinct databases
    Mike

Posting Permissions

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