24

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?

  • "multi tenant" implies strong security - implemented somewhere - so that one tenant can't see other's data, can't modify it, can't deny access to it, etc. That security has to be implemented somehow. Options #1 and #2 of the OP both work but the security analysis and the work required to implement security is different. For what it's worth, I worked at a startup that implemented multi-tenancy via option #2 where the tables - with rows belonging to multiple tenants - were hidden (via permissions) from all end users and security was implemented entirely in stored procedures. – davidbak Jan 19 '17 at 22:32
  • 3
    Possible duplicate of Multi-tenancy - single database vs multiple database – JeffO Jan 20 '17 at 11:33
  • 1
    If this ends up being closed as a duplicate, I think we should flag to merge it with the dupe target because both questions have some really good answers. – user22815 Jan 24 '17 at 15:46
29

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.

  • Thanks @Doc Brown (+1) but then, how could you possibly have any database that was not multi-tenant?!? – smeeb Jan 19 '17 at 15:26
  • 4
    @smeeb: multi-tenancy is a property of the application used by different tenants, not of the database "behind" the application. Of course, the db concept needs to support multi tenancy to make this possible. – Doc Brown Jan 19 '17 at 15:30
  • 4
    @smeeb: well suppose you have a User table with a constraint that username is unique, now some employee of a tenant can't use a username anymore just because another someone working at another tenant is already using it. – RemcoGerlich Jan 19 '17 at 15:43
  • 5
    @smeeb: if the only way of serving two tenants is to install and run an application twice, on two different servers, with two separate databases, I think one would not call this multi-tenancy. – Doc Brown Jan 19 '17 at 15:43
  • 1
    I went to a presentation on Azure a while back that was saying MYOB run their cloud solution on Azure, and each tenant gets their own DB (and presumably web servers too); they just have some great automation tools to manage the hundreds of DB instances that must require. On the other hand, the organisation that I currently work for runs hundreds of customers out of a single database, there's just quite a bit of work done in the software to enforce the isolation of customers' data. We also considered a hybrid, where our largest customers would get their own DB, while others shared the original. – David Keaveny Dec 8 '17 at 1:53
36

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.