1

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.

Improve this question
8
  • Your approach seems fine. Only one application can write (preferable, only one application can read) to a table / schema. You may also check party - party role pattern.
    – Sahap Asci
    Sep 3, 2017 at 21:15
  • Thanks for comment. Do you have any recommended reading on party-party role pattern? Google comes with a lot of results sometimes and its hard to sift with little time I have.
    – Stefano Mtangoo
    Sep 4, 2017 at 9:20
  • found this very helpful link stackoverflow.com/questions/4969133/…
    – Stefano Mtangoo
    Sep 4, 2017 at 13:58
  • I can't provide a specific link. I am using telco specific documentations and its members only. The Idea is; individual, customer, organization are different relations. An individual accesses to a system via his "individual-identification" (e-mail/ password or a key card) after that can do anything according to the roles. he can be a customer and at the same time a manager of a supplier company.its not a software design patter but a business model pattern. You need to design the code according to the needs.
    – Sahap Asci
    Sep 5, 2017 at 7:39
  • After a lot of searching and sifting I finally found a youtube video that gave me a good foundation and now I unsertand what you mean. If you get time to put your comments together as an answer, I'll be happy to accept it. Thank you for your time
    – Stefano Mtangoo
    Sep 5, 2017 at 10:05

1 Answer 1

Reset to default
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.