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?
Best Answer
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).