Do multi-tenant DBs have multiple databases or shared tables?

Question

Is a multi-tenant database:

• A DB server that has a different (identical) database/schema for each customer/tenant?; or
• A DB server that has a database/schema where customers/tenants share records inside of the same tables?

For instance, under Option #1 above, I might have a MySQL server at, say, mydb01.example.com, and it might have a customer1 database inside of it. This customer1 database might have, say, 10 tables that power my application for that particular customer (Customer #1). It might also have a customer2 database with the exact same 10 tables in it, but only containing data for Customer #2. It might have a customer3 database, a customer4 database, and so on.

In Option #2 above, there would only be a single database/schema, say, myapp_db, again with 10 tables in it (same ones as above). But here, the data for all the customers exists inside those 10 tables, and they therefore "share" the tables. And at the application layer, logic and security control which customers have access to which records in those 10 tables, and great care is taken to ensure that Customer #1 never logs into the app and sees Customer #3's data, etc.

Which of these paradigms constitutes a traditional "multi-tenant" DB? And if neither, then can someone provide me an example (using the scenarios described above) of what a multi-tenant DB is?

Answer 1

Which of these paradigms constitutes a traditional "multi-tenant" DB

Both concepts are called multi-tenancy, since it is just a logical concept "in which a single instance of software runs on a server and serves multiple tenants" (from Wikipedia). But how you implement this concept "physically" is up to you.

Of course, the application needs a database concept which allows to separate the data of different tenants, and the idea of multi-tenancy is to have some server resources shared (at least the hardware) for better utilization of the resources, and easier administration. So a "multi tenant DB" is one which supports this directly, where parts of the db model or tables are shared.

To be precise, it is possible to build a multi-tenant application with a non-multi tenant DB, providing an individual DB instance per client. However, this precludes to share any DB resources directly between tenants, and the application layer has to make sure to connect the right tenant to the right database.

Answer 2

According to Microsoft, the term has 3 potential meanings (one database for all tenants, or one databaser per tenant).

To use your example, each customer would be it's own tenant.

1. A database per tenant (customer)

   • Each tenant is isolated from the others (no accidental access to other tenants' data)
   • The isolation also makes it easier to manage restoring of data as well as tailoring storage needs for the tenants' needs.

2. A shared database, separate schema.

   • Each tenant has his own schema, and their data is in their own tables.
   • Restoring can be more time consuming, since everyone is in the same database, you can't just restore the database to an earlier backup (it would roll back every tenants' data). An option is to restore to a new database and then merge/copy only the 1 tenants' data.

3. A shared database, shared schema.

   • Every tenants' data is in the same tables. If you track orders for example, every tenants' orders would be located in "dbo.Orders".
   • Tenants' data is separated by a column in each table (could be TenantId), that shows the owner of the row.

There's pros and cons for each, well explained in this article: https://msdn.microsoft.com/en-us/library/aa479086.aspx

Bonus: you can think of it as living quarters (grossly simplified).

1. Every tenant has his own house. They can do whatever they want, and if it burns down, it doesn't really affect anyone else.

2. Every tenant is in the same building, but has own apartment.

3. Everyone live in the same apartment, and all the stuff is marked with a sticky-note to show who owns it.