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

closed as primarily opinion-based by mustaccio, LowlyDBA, McNets, John aka hot2use, Marco Feb 28 at 7:22

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    This all depends on where you ever need to access data from multiple tenants – a_horse_with_no_name Feb 27 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 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 at 14:00