Friends, I am building a service application and would like to see a gross suggestion to achieve.
The core of the application is to manage research projects, hence it will have a group of users, who belong to an organization, who login and manage their own projects. Many organizations carry out such projects, which are identical in nature. The project management has identical database tables and schema (across institutions). I have designed a plan as follows:
Database-1: A common database users table (all institutions together) get authenticated by querying this table. This table has a institutional code corresponding to each user as a column.
Database-2 (institutional code as its name): Based on the institutional code, all the project management is done by connecting to this database. Within this database tables will be present.
....
All databases will have identical schema and identical user interface. Institute wise management is easy this way.
Now, using Laravel, i know i can connect to multiple databases. I have done this in codeigniter 3 but trying to migrate to Laravel 5.3 as models architecture is different between Codeigniter 3 and Laravel 5.3.
Any better suggestions. I know my query is not a pure question but this question is about implementation of a many to many relation.
Since all institutions will have same modules and same functionalities why are you creating multiple databases? I have successfully done a similar project as you, a Centralised CMS which integrate all church under one diocese in CodeIgniter.
Just add an institution id in all tables and query your table operation with institution id every time. Adding multiple databases will definitely decrease your project performance and increase complexity. An institution table will track all your institutions.
If you need more details about database structure and implementation please comment below.
I have successfully worked on mutliple database and mutltiple domain application. In that I have created one table "branches" which stored the multiple branches details like its database name, domain name.
I have set the record as domain= "branch1", database="db_branch1" in table "branches"
Also set the middleware which check the valid domain name and allow to associated routes for domain "branch1".
Once the main common database authenticate the user creditials it connect to branch database in main connection.
It can not explain in very short method, here I just explain the work flow. If you need more details I will explain it.