2

I am not asking for an opinion here. I need an answer supported by arguments.

We have an web-application that will be installed multiple times on one server (one instance per customer). How should I organize the database for that case? Should I go with database per instance or schema per instance?

Option 1:

  • DB: appname_customername1, SCHEMA: appname
  • DB: appname_customername2, SCHEMA: appname
  • DB: appname_customername3, SCHEMA: appname

Option 2:

  • DB: appname, SCHEMA: appname_customername1
  • DB: appname, SCHEMA: appname_customername2
  • DB: appname, SCHEMA: appname_customername3

How the two approaches compare in sense of performance and memory usage? Is there a difference in administrative tasks/costs?

NOTES:

  • NO data is shared between tenants and never will be
  • There is no shared master data and it will never exist
  • 1
    This all depends on where you ever need to access data from multiple tenants – a_horse_with_no_name Feb 27 '19 at 12:19
  • 1
    one advantage of schemas over databases is ability to join shared "master" data with per-tenant tables, without any need of cross-database synchronization. – filiprem Feb 27 '19 at 12:31
  • From what you've written, it appears that you should go for the separate schema approach since you have no shared data between the clients - a major reason to go for multi-tenancy. See here, here and here and links within those threads for discusstion. – Vérace Feb 27 '19 at 14:00