0

I'm working on an application which is being used in multi-tenant mode, and have created a MySQL database instance, where each tenant has its own schema. The problem I'm facing is, because the application is constantly changing, due to addition of features, etc., the database tables are also changing very often. This means, I have to keep changing the table structures in all schemas for every single change.

Is there any database management system (DBMS) where table structures are defined once, and each instance only has the table data?

MDCCL
  • 8,520
  • 3
  • 30
  • 61
ArM
  • 101
  • 1
    Take a look at the threads and my answers here. It seems to me that you should try and consolidate your tables - have one client table with a client id field and work with that. Changing DDL on an active application is not a great idea for performance, not to mention testing issues - compounded with multiple tables! – Vérace May 21 '18 at 11:22
  • 1
    Good that you're addressing this now -- it doesn't get easier once you're in production. – Colin 't Hart May 21 '18 at 12:38

1 Answers1

1

If you have set up separate logins (passwords) for every tenant maybe you could consider using one "main" schema and building a database view(s) for each table filtered by condition "where login = current_user()" ("login" column must be properly handled in your inserts). Then you can grant access to users only to views.

This way you could filter data by logged user and also you minimize your work dedicated to keeping database schema/structures up-to-date - you have to update only 2 structures: database tables and database views.

But honestly I would rather advise you to automate process of deploying changes to databases - store every change to database schema in script files and then automate executing scripts on databases (some shell/bash scripts where parameters are database names to handle).

bigfoot
  • 116
  • 3