0

I recently encountered the following architectural problem at work and I'm unsure about the best course of action here.

We provide a multi-tenant SAAS application for schools with an architecture that works with a central master database, containing configuration and shared data, and a specific database per 'group of schools'. These groups are often geographical in nature, schhools working together and taking a software license for their group.

This has provided problems in the past, because sometimes schools change groups. We have solved this issue by creating a migration tool that extracts the school data from one database and merges it into the other groups database.

Lately we've bumped into the following request: Most schools operate from a single non-profit organisation (which is different from the grouping we use), and now those non-profit organisations are asking to provide them application access to ALL of their schools. For instance, one organisation has over 150 schools, whose data is stored in 25+ different databases.

Average database size is around 6-7GB and we have about 350 of them.

What would our best course of action be, to provide this access? Would it be feasible to merge all databases to one and run from a single database, should we create some kind or synchronisation algorithm to a new database, something else? Our goal is to alter our architecture and infrastructure as required to provide for this new angle of approach.

I can't help but feel that this case somehow has a standard solution, and that there are standard architectures for this, but I just don't know the name. If you make this case abstract, there should be a 'good' solution to it?

Abstract:

Entity can take a software license, group of entities can take a software license, entity can change groups, governing groups can take a license (at a later time) with entities already licensed as single or group license. Data should be accessible on all three levels, with the correct security separation of data per user.

Please provide either an answer to my specific problem, or solutions to the abstract problem.

Bram Vandenbussche
  • 1,401
  • 11
  • 22

1 Answers1

0

One of possible solution to

the abstract problem

is the Cloud, to be more specific - Application model. I see it as your services (used by the large user groups) of the remote servers are networked to allow the centralized data storage. I think that SaaS Multitenant Database architecture is what you are looking for. The answer of this question is very helpful.

In addition, for your case I think that a good option is Shared DB, but Separate schemas. It allows housing of multiple tenants in the same DB. which tenant having its own schema. You can find this and other solutions in this presentation.

Community
  • 1
  • 1
ekostadinov
  • 6,880
  • 3
  • 29
  • 47
  • Does using multiple-schemas within a shared db allow you to query data across schemas (which will happen when a governing group user requests data for all his entities)? – Bram Vandenbussche Oct 08 '14 at 08:52
  • Everything can be configured to fit the best. One such concrete solution is the [Real application clusters](http://www.oracle.com/us/products/database/options/real-application-clusters/overview/index.html) by Oracle. – ekostadinov Oct 08 '14 at 10:15