SaaS Database Development - Multiple Databases? Crack?

Question

I have seen SaaS applications hosted in many different ways. Is it a good idea to split functions and modules into multiple databases? For example, putting things like the “User” table in one database and special / special tables on another database, and possibly other common shared tables in another database?

Answer 1

Start with one database. Share data / functionality when the project requires it.

Here's what we can learn from LinkedIn:

• Single database not working
• Referential Integrity Will Not Be Possible
• Any data loss is a problem.
• Caching is good, even if it is moderately effective
• The growth path should not be underestimated.

Source:

LinkedIn Architecture

LinkedIn Communication Architecture

Answer 2

High scalability is a good blog for scaling SaaS applications. As mentioned, splitting tables between databases, as you suggested, are usually bad ideas. But a similar concept is a shape where you keep the same (or similar) scheme, but share data on multiple servers. For example, users 1-5000 are located on server1, and users 5000-10000 on server2. Depending on the queries your application uses, this may be an effective way to scale.

Answer 3

For SaaS applications, you use multiple databases for multiple tenants, but usually do not break them down by module.

This is the most common model I've seen in SaaS application development. Your base schema is replicated for each tenant you add to your application.

Answer 4

Having a single database is best for data integrity, because then you can use foreign keys. You cannot use this built-in data integrity if you split the data into several databases. This is not a problem if your data is not related to each other, but if it is connected, it is possible that your one database will contain data that is incompatible with another database. In this case, you will need to write code that will regularly check your databases for inconsistent data so that you can process it accordingly.

However, to ensure the scalability of your site / application, several databases (for example, an Internet scale) may be required. For example, you can host each database on a different physical server.

Answer 5

Dividing a database into functions may not be a good idea if you do not see strong evidence pointing to the need. Often, you may need to upgrade two databases as part of a single transaction, and distributed transactions are much more difficult to work with. In addition, if the database needs to be partitioned, you can use shards.

Answer 6

There are many ways to achieve it, but the challenges of multi-user work go deeper than just a data model. I don’t like connecting to the product, but check out SaaSGrid with my company, which I work on, Apprenda . It is a cloud-based operating system that allows you to write single-user SOA applications (feel free to use NHibernate to access data) that automatically inject multi-user access into your application. When you publish your application, you can do things such as choosing a data model (an isolated database or sharing), and SaaSGrid will be deployed accordingly, and your application will work without any code changes - just write the code, as if it would be for one tenant!

Answer 7

Why use a database at all?

I consider it a good idea to use distributed storage systems such as Hadoop, Voldemort (project-voldemort.com, developed and used by LinkedIn).

I think db is good for sensitive data such as cash transactions, but you can use distributed storage for the rest.

Answer 8

Ask yourself: what do you get by moving everything to separate databases?

A lot of management pain would be my guess. I would be more interested personally to have everything in one database, and if you run into problems that cannot be solved with one database, then move the data to several databases.

Answer 9

Maintain a natural design (denormalize as much as necessary, normalize as little as possible). Divide the database model into your modules and consider service-oriented principles by processing the data with the service (which owns the data).