Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2013
    Posts
    3

    Multi-tenant DB Design, a Salesforce scenario

    I was doing some research into the optimal DB setup for a Salesforce like application (webapp with sensitive data for multiple 10.000's of customers, each customer can have multiple USER accounts).

    My idea is, is that the split database setup (different DB for each customer) is the best since its the only way to make sure data is really isolated in terms of security, and because its not just multi customer, but each customer can have multiple users that login to work on the data. However...is having a DB instance with 10.000 'small' DB's really gonna work?

    I dont really feel much for the shared schema setup since you will end up with 10's of thousands of tables, which doesn't seem very manageable.

    The 1 big DB setup with just using a customerID column per table seems the easiest to implement, but not really secure...and joining tables will be quite a task as I presume.

    What are your thoughts on this scenario? The seperate DB's-scenario seems best to me from a scalability and security perspective...but what do I know

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Separate Databases invariably lead to separate schemas which invariably leads to separate applications which invariably leads to administrative nightmare.

    Invariably.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2013
    Posts
    3
    Quote Originally Posted by blindman View Post
    Separate Databases invariably lead to separate schemas which invariably leads to separate applications which invariably leads to administrative nightmare.

    Invariably.
    Why would it lead to seperate schemas in a SalesForce like application where all customers use the same application(-data)?
    I assume you're a proponent of the 1 big DB setup?

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    SalesForce is a 3rd party solution.
    I'd have thought that they could handle multi-enterprise security, but if not then you would need to follow their best-practices recommendations.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by CD2013 View Post
    end up with 10's of thousands of tables, which doesn't seem very manageable.
    So you think that managing 10000 databases is somehow easier than "managing" 10000 tables? By the way, what is involved in "managing" tables, in your view?

    Quote Originally Posted by CD2013 View Post
    joining tables will be quite a task as I presume.
    Why is that?

    Your approach to security will differ depending on what database platform you choose, so there is no generic answer.

    By the way, the actual Salesforce application does not a separate database for each of their clients, I can tell you that.
    ---
    "It does not work" is not a valid problem statement.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Yeah, I though SalesForce was cloud-based now?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by blindman View Post
    Yeah, I though SalesForce was cloud-based now?
    Don't let the clouds blind your vision -- they still need to store the client data somehow, don't they. All this "cloud" talk makes people think that all those order items and address details just magically appear out of thin air when you need them...
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Aug 2013
    Posts
    3
    Quote Originally Posted by n_i View Post
    So you think that managing 10000 databases is somehow easier than "managing" 10000 tables? By the way, what is involved in "managing" tables, in your view?



    Why is that?

    Your approach to security will differ depending on what database platform you choose, so there is no generic answer.

    By the way, the actual Salesforce application does not a separate database for each of their clients, I can tell you that.

    managing as in db updates like adding/changing columns etc.
    Joining isnt the fastest command, let alone doing it on 2 tables with a million records? if you would have a DB for each customer (or shared schema, having the table size only a 100 records for instance) it would be much faster I'd think.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by n_i View Post
    Don't let the clouds blind your vision -- they still need to store the client data somehow, don't they.
    Uhm....yeah. This I already know. The concern is regarding who has control over the database architecture regarding the original post.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Quote Originally Posted by blindman View Post
    The concern is regarding who has control over the database architecture regarding the original post.
    May be I misunderstood the OP, but I thought that person wanted to be in control of the database architecture when he or she implemented an application similar to Salesforce. So, yes, Salesforce is a "cloud application" and yes, the OP wants to know how its database is actually architected.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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