Are you single, a schema, or in the mix?

Are you single


As CTO’s and technology heads, when we are thinking of designing a new system in a SaaS environment there are a number of factors to consider but let’s think big. The decisions taken now will be in place for many years to come and are exceptionally difficult to unwind. There is no need to redesign or refactor in the future if you have the key considerations in place from the start. If you have read my post on user interface design “Ditch the aging UI, not the aging app”, this is the other side of the coin, we are not going to be in a position to redesign without extensive costs.

As for the overall design, we are going to assume that you will need some web/application server and a database server in its simplest form but you need to know it will be powerful enough now, and in the future.


Within this section, we discuss the architecture design of the system. For the purpose of the discussion, we can consider either a company undergoing a SaaS transformation from a single tenant generally on-premise enterprise solution to a SaaS offering or an initiative to build a product from scratch. All facets remain the same, just the approach is slightly different in the addition of tenant provisions in the databases. This however is beyond the scope of this post but tenants are shown here throughout this section.

Remember that separation of user information, data security, data security compliance and other compliance items will be discussed in future posts and are critical to be incorporated but for now let’s keep in the realm of architecture.

A tenant/client are used interchangeably in this post but refer simply to a logical collection of users which need to be completely private and protected from other users.

Scaling Up – Unlimited Resources?

Cloud providers have what seems an unlimited supply of hardware and configurations but in reality it is a collection of multi-processing nodes with high speed storage arrays on the back providing the storage space. The great alure of the cloud and cloud providers is that you can start small to keep costs low and keep scaling up the processing and storage requirements as the business grows. Scaling up is the mechanism of doing this and is typically no more complicated that allocating ram, virtual CPU’s and storage space via a console on the cloud provider. It is worthwhile noting that at the time of writing, Amazon Web Services can offer a system with 448 Virtual CPU’s, 12TB Ram and 16TB storage space for 730,000 USD/year which is exceptionally cost effective and would far exceed the requirements of most providers.

Scaling Out – Scaling all resource aspects:

Since Cloud Providers have again a virtually unlimited supply of hardware and configurations they can provision many small units to provide many processing servers. This is the nature of scaling out. Each server has its own bandwidth, connectivity, storage and memory, distributing the need to a power house of processing capabilities. As your service grows, simply bring more servers and processing power online. Cloud Service providers have mechanisms to scale out website and web servers but a solid scale out strategy requires correct technologies, design and implementation of systems with correct development of software. It is not as simple as going to a console and changing the number of CPU’s but the benefits are many given the system is virtually unlimited in terms of storage and processing power.

It’s Question time:

So scale up, scale out? If it is difficult to know your requirements how do you take a call on this. Sure, if you know the extent of the system then you could possibly say that scaling up is all I need to keep it simple. But what if you don’t know just how astronomical your growth will be.

The Easy Bit – Application Servers

Application or web servers via network load balancers can address the load from the browser on the client machine or the application running on the desktop as they handle atomic units within the request. Using modern technologies and development tools, this is almost boilerplate coding. Application servers therefore scale up and out without any great problem but they all need to connect to the database. So below we look at the database configuration and architecture.

Database – Three Plus One Designs

There are 3+1 different mechanisms for the design of a multi-tenant SaaS database system. Each has its pros and cons and requires careful consideration on the type of SaaS customer using the SaaS offering.

Option 1 – Separate database per tenant/client:

In this approach, each client has its own database. Hence they have their own set of data which will remain isolated from other data which other clients own, that is, logically the database will be isolated.

Pros: High security and can be customized according to the tenant’s needs. Tenant leakage cannot occur because there is only 1 tenant on the server.

Cons: High maintenance and hardware costs and only really works if you have a large user base under each tenant that merits a database server for each tenant.

Option 2 – Shared database with a different table grouped by schema per tenant/client:

Here, all the tenants will be configured under the same database but they will be having their tables grouped by a schema until some threshold is met, then move to the next server. Isolation will be at the table level.

Pros: Lower in cost and addresses security concerns to an extent. Tenant leakage again is only in the schema and provided this is set up correctly, for example a different SQL server login for each tenant, then the database by design protects itself.

Cons: Data restoration of a single-tenant is difficult. Again suffers from the problem of overkill for small user bases. Consider there is effectively a complete database for a tenant taking up requisite disk space accordingly. If that tenant has 5 users, it is a large overkill.

Option 3 – All are shared – database, table, and schema:

In this case, the database and the tables are shared. So here, the tables are the same and will be storing multiple tenant’s data in any order. They are identified by a column that indicates the source i.e. tenant information. This would be validated when any tenant places a call request. Here, the isolation will be at a row level.

Pros: Lowest cost. This handles tenants from a single user to a large client easily. Certainly the most cost effective solution and will allow movement of information accordingly to different servers by a simple movement query as each row in the database will have a tenant id column.

Cons: Data security needs to be addressed appropriately. There is a real risk of data leakage with this model as the is no database isolation.

Cons: Development costs and testing costs are potentially higher than other options as the data leakage is a real reputational risk so needs solid testing and development.

Option 4 – My preferred approach – Option 3 + Option 1

Option 4 adopts the model of Option 3 where we cater for small tenants as well as large and in the case of large (tier 1 account) or at the SaaS Customer request (for additional fees) a single tenant can be setup accordingly (Option 1). However, to give additional flexibility, the initial tenant maybe in a shared server environment with a move later to a single database server.

Connection Flows

The web/application servers need to connect to the correct database server (unless you are planning to have 1 monolithic server or a database cluster). We employ a simple identifier server lookup in our token whenever we make calls from the client browser or application. The token is created when the user gets a successful logon. A simple flow chart shows the approach.

The Cherry On The Cake

By adopting this approach we can cater to the very small, the very large, the companies that start small and ramp up, we limit any potential problems with security and limit potential database corruption because of the distributed nature of the system. In addition, let’s say you want to bring a new database server into the environment, simply introduce the information to the authentication server (in the above example) and if the application server handling the request does not know the server mentioned, it simply performs step 1 and 2 to auto refresh the list. New database servers on the fly.


Remember, you have to pick your approach and stick with it. I hope this has been helpful in removing the opacity behind SaaS database architectural design. With Option 4 you get all the benefits and can handle the real-world use cases of people trialing with say 5 people and then setting up a 1,000 users. Your SaaS architecture you decide today needs to keep you going for years to come.

Colin Stone

Colin Stone

Colin Stone brings more than 25 years of experience in the field of Information Technology and Investment Banking. Driving all aspects of technology operations, including product design, IT systems, networking and security.
Colin Stone

Colin Stone

Colin Stone brings more than 25 years of experience in the field of Information Technology and Investment Banking. Driving all aspects of technology operations, including product design, IT systems, networking and security.