Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2011
    Posts
    1

    One big DB or smaller separate DBs?

    I am thinking of creating an online school management software which I would host on my server only, not the school's. The school would then have a subscription.

    The question is, is it best to have one big database for all the schools, or one database for each school. Or have one database and have separate tables for each school, like so:

    schoolA_studentdata
    schoolB_studentdata

    schoolA_gradebook
    schoolB_gradebook

    schoolA_staff
    schoolB_staff

    or just have one big database and one table with all the schools thrown in like so:

    studentdata
    gradebook
    staff

    and have a field called school_ID in each table to keep the school data separate?

    Thank you for your time.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    given that there may be confidentiality and or security issues, and you don't own the data in this case I'd suggest you have separate databases for each customer. it has trhe added advantage is that if the app grows then you can allocate resources across more than one server, it also allows you to have say a premium or standard service... say premium is on a lighter used server and therefore should have a quicker response time

    the only caveat to that would be if there was a need to compare across all customers
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    My concern with that approach is that I have NEVER seen it implemented for any length of time without the designs of the various DBs diverging. He may find himself ultimately supporting dozens of slightly different copies of the database design, which will make upgrades and maintenance a royal pain.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    I agree with the blindman on this one, as he sees it the way i sees it. As far as confidentiality, it can be handled either through row level access or via a view. The view definition would only allow a client to see their specific data, you just have to set the client id when someone logs in.
    Dave

  5. #5
    Join Date
    Feb 2011
    Posts
    7
    Having hosted an application many customers, I would go the multiple database model. It gives you scalability and a reduction in locking conflicts.

    What I did to keep the databases in sync was to write alter scripts. Make them so that they are re-runable. This is a sample of what I do for SQL Server.

    Code:
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    if not exists (select 1 from SYSOBJECTS where NAME = 'DELETED_CUSTOMER_ORDER' and TYPE = 'U')
    begin
        print 'ENH: Adding DELETED_CUSTOMER_ORDER table'
        create table DELETED_CUSTOMER_ORDER
        (
            CUSTOMER_ORDER_ID              int                   not null,
            ORGANISATION_ID                int                   null    ,
            CUSTOMER_ID                    int                   null    ,
            VISION_USER_ID                 int                   null    ,
            ORDER_STATE_CODE               varchar(5)            null    ,
            PAYMENT_METHOD_CODE            varchar(5)            null    ,
            DELIVERY_METHOD_ID             int                   null    
          ...
        }
    end
    If you run this across all of your databases and they will be in sync. You can run it again and it won't cause any damage.

    Chris

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Chris, the problem of diverging schemas is not one that can be solved by scripting out the changes. It is a human issue, and a management issue, where certain persons insist that changes be made for them, or changes be delayed for them, or code is written that is tested and works against one set of data but does not work against another set of data for a different client.
    Scripting out code and objects changes is basic database best practices, but is too simplistic a solution to resolve issues that a largely a problem with project management.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2011
    Posts
    7
    I agree that all of those are issues that you have to cope with when dealing with multiple customers, but I'm not sure that a single database solves them. An application could still fail if it's not tested against all sets of data. Customers will still insist on holding off on changes etc.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    You can have scalability either way. Locking conflicts are also fairly trivial to deal with (unless you're talking threaded client code, but that's a different discussion).

    You are correct in asserting the PEOPLE and MANAGEMENT issues exist for either approach. It doesn't matter what you do, client/expectation management is a constant. What you're talking about is taking the "easy" route to scalability in exchange for maintaining perhaps thousands of diverging branches of code with arbitrary complexity.

    I'm not against multiple databases if the requirements call for it. For example, in a hosted multi-client back office solution with wildly different requirements for integration points and workflow management, it made good sense to maintain separate code lines. The dev and maintenance requirements for dealing with these sweeping architecture disparities between clients within a single solution would have been prohibitively expensive.

    That's the kind of business requirement that I would consider for this architecture decision. The other stuff mentioned so far can be addressed in several ways with minimal additional effort. I'd start with the assumption I'm using one single database. If a good business reason for multiple db's crops up, so be it.
    Last edited by Teddy; 02-21-11 at 18:56.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Feb 2011
    Posts
    7
    Agreed.

    If you have an application that is shared by 1000s of customers you would be better off with a single database. However, if you have a small number of customers with 1000s of users then you might be better off with multiple databases.

    There is no such thing as one size fits all.

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    I require BA's to justify developing and maintaining more than one code base, regardless of number of clients.

    Why would I be better off with multiple databases even if there are only 2 clients? What do they need that's so different that it can't be easily done with one database?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  11. #11
    Join Date
    Feb 2011
    Posts
    7
    I've just never had a problem keeping the code in sync. We have an upgrade script that is automatically applied to each database in the test and production environments.

    I've never really thought about this as an issue before. I can see pros and cons for both depending on the circumstance.

    We've used the multiple databases to our advantage. Our largest customer sites on a data server by themselves and the other share a data server.

    One of our customers have specific and complex pricing rules that were implemented inside a view. It did it's thing very fast as was easy to implement because we could have separate code lines.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    There are pro's and con's for both. I've done both when the scenario called for it. Neither approach is right or wrong...

    I have managed enough projects with several "live" branches to see it as a substantial risk. The risk can be exacerbated by all kinds of factors from new devs that copy/paste between projects, incomplete or out of date change scripts, app dev's, dba's and BA's that can't keep up with all the versions (this one happens a lot) or diverging deployment procedures. It's hard to be an expert on everything.

    What happens when one or two or twelve code lines have done something different to their database that will cause a nuanced issue when you apply the sweeping update that expects consistent platforms? That can get real complicated, real fast. It can be done. There is a big cost involved with QA/promotion to do it right.

    In your example, some of the options I would weigh multiple databases with customized views against would include modifying the current database to support high complex rules or moving the pricing logic to a middle tier/service. This would be a big conversation with BA's about what kind of flexibility is required in the solution. In one scenario, we did come to the agreement that multiple db's were the right way to deliver and maintain the product. It was a pretty special case, but it happens.
    Last edited by Teddy; 02-22-11 at 09:43.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Quote Originally Posted by DisgruntledDog View Post
    One of our customers have specific and complex pricing rules that were implemented inside a view. It did it's thing very fast as was easy to implement because we could have separate code lines.
    Then your databases are no longer in sync, right?
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Feb 2011
    Posts
    7

    ationsb GLd

    Quote Originally Posted by Teddy View Post
    There are pro's and con's for both. I've done both when the scenario called for it. Neither approach is right or wrong...

    I have managed enough projects with several "live" branches to see it as a substantial risk. The risk can be exacerbated by all kinds of factors from new devs that copy/paste between projects, incomplete or out of date change scripts, app dev's, dba's and BA's that can't keep up with all the versions (this one happens a lot) or diverging deployment procedures. It's hard to be an expert on everything.

    What happens when one or two or twelve code lines have done something different to their database that will cause a nuanced issue when you apply the sweeping update that expects consistent platforms? That can get real complicated, real fast. It can be done. There is a big cost involved with QA/promotion to do it right.
    We've been running the application for 10 years with dozens of customers. Mutliple databases just hasn't been an issue. We don't let dev types get their grubby hands on the production or test databases. The updates are automated and are applied to every database. The upgrade process gets tested in the test environment every time.

    It does require discipline but it's not that hard to keep them in sync.



    In your example, some of the options I would weigh multiple databases with customized views against would include modifying the current database to support high complex rules or moving the pricing logic to a middle tier/service. This would be a big conversation with BA's about what kind of flexibility is required in the solution. In one scenario, we did come to the agreement that multiple db's were the right way to deliver and maintain the product. It was a pretty special case, but it happens.
    We had to mimic a customers existing pricing rules. They were very specific to that customer, so modifying the base application made no sense.

    We could have moved the code to the middle tier but the speed of the database doing the work and having native access to all the data made sense to do it there.

    In any case, you have the same problems with having different code in the middle tier as you do having different code in the database. These all need to be managed and controlled to ensure that they work and are tested when change occurs.

    In a perfect world, you wouldn't need to have different code for different customers but the world is far from perfect.

  15. #15
    Join Date
    Feb 2011
    Posts
    7
    Quote Originally Posted by blindman View Post
    Then your databases are no longer in sync, right?
    From the outside world they are identical. The datatypes are the same as are the columns returned.

    Unless you are in a position where one code line will meet the needs of every customer then you need to have the ability to have differing code some place in the application. The database is often the best place to put these differences.

Posting Permissions

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