4

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.

Different ways to implement the current system I am thinking of

(please excuse option 1 being last, I had to redraw it from the first page.)

--------------------Text Description of Options-------------------

Option 1:

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.

Option 2:

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)

Option 3:

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.

Thank you

  • 1
    Yet another option would be to take the eventual consistency route and store the common data in both db1 and db2, and sync them as required, doing away with the common db altogether. This has some big impacts though. OTOH, you could stuff both App1 and App2's info in the same db... they're part of the same system? Not all data in a database has to relate to all other data in a database... – jleach Apr 20 '18 at 17:41
  • I think I would prefer to say away from having the common data in both dbs, as that would require additional work to keep them both up to date. They could all stay in 1 DB, but the idea is to have 5+ applications ( small but still requiring 10-15+ tables.) so the idea of splitting up the information is appealing. Im now considering if I should use multiple Schemas in 1 DB or multiple Databases that each having 1 schema. ( this also depends on if we decided to keep using MySql or use Postgres) – SegFaultDev Apr 23 '18 at 15:23
  • There are some informaiton missings : does your apps need to update things in the common API ? Specifically in the same transaction than updating their datas ? Is there the need to perform join between apps & common datas ? – Walfrat Apr 24 '18 at 7:52
  • Please let me know if you need more information. I believe there would be two ways to update/change the data in the common DB/Schema. Either there is a separate application that deals with managing the data and all the other applications cannot make changes to common db entities. Not sure if that is the best approach. Initially I was thinking that each application could make changes to the entities in the common db. Each application would call an update endpoint in the common api which would make the appropriate changes. Entities in the common table would be like, Users, Employees, etc. – SegFaultDev Apr 24 '18 at 16:40
  • there will be pivot/relationship tables inside each app that will reference entities in the common tables. As for joins, there may be a case for that? not one that I can think of right now. Would the need for a join change implementation? – SegFaultDev Apr 24 '18 at 16:41
2

I like option 1. Your apps are then ignorant of their underlying persistence schema and the persistence management is done by the API (or layers below/within).

A common route would be your #1 option, with a repository pattern that has an interface like "get this", "get that", and the repository implementation deals with pulling the info from (and saving the info to) the correct database.

Alternatively, a separate API for each app, where each API has a repository somewhere below that does the same thing.

I would avoid the apps "talking to" the database directly as indicated in options 2 and 3.

Remember that (generally) each component should not generally be concerned with details of the services below it.

Generally speaking, the more segregated your services/layers are, the easier they tend to be to maintain over the long term, at the cost of a more complex initial setup. On the other hand, starting with a very simple architecture can be extremely difficult to deal with later on, so if there's any sort of expected longevity in the project, I'm much more apt to go with the architecture investment.

There's many variations on it (and if you're doing something like microservices you're in a much different realm), but here's a quick visual that gets the basic point across:

enter image description here

2

I'm seeing a need for common tables, which then has exploded into multiple databases. This would concern me.

Separating the application tier into multiple applications can be a good place to start, but separating databases is definitely not the first step.

Create 1 database. The "common" tables would go in a "common" schema. Each application would get its own schema and can hold foreign key references to tables in the common schema.

From there you are free to split the application tier into as many apps and APIs as you deem fit.

If database performance is a concern, start out with a server architecture that allows for database sharding. This can help spread the load, and since multiple servers are being used you have the ability to upgrade hardware without a coordinated ecosystem wide "re-pointing" of applications to a new, upgraded database.

Start out with 1 database. Separate schemas. Split your application tier into as many layers, APIs and micro services as you deem fit. It's much easier to refactor code than restructure data - especially data in separate database servers.

When pulling reports becomes too slow, then it's time to add a "reporting database." After all, quarterly reports don't need up-to-the-second data. Eventual consistency is fine for that.

So basically options 1-3 look fine, except the database part. Have them all point to the same DB. Then choose any option you like.

Updates in response to comments:


SegFaultDev commented:

With mySQL, every schema is actually a Database, as they are synonymous ( at least that is my understanding of it).

I think the main point is to allow for referential integrity between databases/schemas/collections of tables at first. Only break them into completely separate systems if you absolutely have to. MySQL shouldn't have an issue with this.

It's completely fine to break the application tier into multiple systems (web applications, web APIs, micro-services), but you will still have an easier time with the overall architecture if your data storage mechanism is one cohesive unit, be it "schemas" in Oracle or "databases" in MySQL.

  • Are there any Cons/negatives to using Schemas vs Databases? With mySQL, every schema is actually a Database, as they are synonymous ( at least that is my understanding of it). I am leaning towards using Postgres which does support schemas within a Database. Our current application ( which requires from major TLC / potentially a rewrite) currently uses MySQL. – SegFaultDev Apr 23 '18 at 15:20
  • 1
    @SegFaultDev: I updated my answer to clarify things based on your comment. – Greg Burghardt Apr 23 '18 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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