I was tasked with making a multi-user business application. It has Java / Glassfish BLL using SOAP web services and the PostgreSQL backend. Each tenant has its own database, so (in my case, at least) “multi-tenant” means supporting multiple databases on each application server.
The current peer application server initializes the C3P0 connection pool with the connection string that it receives from the configuration file. I think that now for each client / database serviced by the application server, there should be one connection pool.
Once the user logs in, I can match it to the correct connection pool by viewing its tenant. My main problem is how to achieve this - when the user is first registered, the User backend table is requested, and the corresponding User object is served. It seems I will need to find out which database to use only with a username for work.
My only worthy idea is that there should be a “config” database - a centralized database for managing tenant information such as connection strings. The BLL may query this database for sufficient information to initialize the required connection pools. But since I only have a username to work with, it seems that I also need a centralized search for a username, in other words, a UserName table with a foreign key in the Tenant table.
That's where my design plan starts to smell, giving me doubts. Now I will have user information in two separate databases that need to be maintained synchronously (user add, update and delete). In addition, usernames should now be globally unique, whereas before they should be unique for each tenant.
I strongly suspect that I am inventing the wheel, or perhaps even a more advanced architecture exists. I had never done this before, and there was nobody on my team, hence our ignorance. Unfortunately, the application practically does not use existing technologies (for example, ORM was scanned at home), so our path can be difficult.
I ask the following:
- Criticism of my existing design plan and suggestions for improving or redesigning the architecture.
- Recommendations from existing technologies that provide a solution to this problem. I hope that it can be easily connected at the end of the game, although it may be unrealistic. I read about jspirit but found little information about it - any feedback on this or other frameworks would be helpful.
UPDATE: The solution was successfully implemented and deployed and passed initial testing. Thanks to @mikera for his helpful and encouraging answer!