I'm trying to determine the best application and database architecture for a project I am working on.
The idea is that there will be multiple, separate applications that will all share a few tables like a User and an Employee table. Each Application will have a web portion (which may require an API, depending if server-side or client-side rendered) and an iOS portion ( which will require an API.) If I decide to go client-side rendering and use a something like Vue.js, React, or Ember. I may be able to consolidate the web and iOS APIs. (A side note: I am much more comfortable with server-side rendering and would have to learn a JS framework if deciding to go client side but will do so if that is the best option.)
I currently have 3 different approaches on how to implement this kind of system. All of them separate out the common tables inside a separate database that I will call COMMON_DB.
(please excuse option 1 being last, I had to redraw it from the first page.)
--------------------Text Description of Options-------------------
Use an API along with GraphQL that would do all the communication with all the databases in the system. If there were 2 web applications, the API would have 3 databases connected, DB_APP_1, DB_APP_2, and DB_COMMON. The common database would contain tables that are duplicated/shared between the 2 applications. The applications(App1 and App2) would be Client or server side servers and would communicate with the DB API to get the certain information they require.
Each application connects to 2 different databases. App 1 would connect to DB_APP_1 and DB_COMMON, App 2 would connect to DB_APP_2 and DB_COMMON. App1 and App 2 would be server-side ( ether API for client-side or rendering client via server-side rendering)
Each application communicates with a Common API server. The Common API server has 1 database, which is the common tables of each application. Each App has their own database, which contains all the unique table for each application. Each Application would not have direct access to the common database like Option 2. Again, app 1 and app 2 would be server-side, ether APIs for a client-side rendering, or rendering the client server-side.
I am currently battling myself on which of these approaches would be the most beneficial and prevent roadblocks in the future. There will be some learning of new technologies which is to be expected.
Any thoughts and opinions would be greatly appreciated, please let me know if anything is unclear or you have any questions.