Postgresql – Multi-Tenant shared Users Tables: Need an advice


Am writing multiple Multi-Tenant applications with separate database per each application (I've decided to go with shared database different schema approach). Each application have Staffs Table for tenants' staffs and Customers for, well customers.

The problem am having is all my applications share the two tables and so there are going to be duplicates if each database is going to be completely independent.

I have thought of having a common database with Staffs and Customers table and have a different app (like Oauth2 server) handling the registration and login. And then have stub table Users in each multi-tenant apps with columns id and role(customer/staff) forming Primary key and gets their data from remote Staffs and Customers respectively.

Am not sure this is a good approach so and if there are better ways to handle that.

If am not clear let me know in comments.

Best Answer

So this is the approach I came up with. Store All user information and details to the Oauth2 Server/Login API and use it to login. Then in each of the service tables just store user ID, role picked up from login into Oauth2 server/Login API and save them into "users" table of your a specific service. That way you can preserve service data integrity while preventing duplicates.

As to Oauth2/Login service, you can use Party-Roles model (also known as Super-Type/Sub-Type). This video well illustrates it but in case you cannot watch it, just make People table and then Customers and Staffs table relating with People table with 1-to-1 relationship with a type.

Thanks to @Sahap Asci for pointing that out in the comment.