We would like to use a shared database for a number of separate applications. These apps are completely separate (separate repo, deploys, etc…). Many of these apps have interdependencies between their data. For example, an application for creating and sharing project estimates would need access to product data in a catalog app that furnishes an API. Also, all of these apps would need authentication, which would be handled by an identity app that keeps user data.
There are several approaches we can take:
- Integrate the apps over APIs. I don’t like this idea because it might mean recreating tables and schemas in two separate databases and then worrying about keeping data in sync. For example, we might need to create a user and product tables and their schemas in both apps. Although, an API serves as a contract and could make the integrations more robust.
- Keep all apps in an umbrella or monolith. I don’t like this option because these apps deal with different business concerns, and while they might share underlying data, they implement this data in completely different ways.
- Separate apps, shared database. I don’t like this because, unless you are mad, you would need a dedicated app to manage your repo and migrations.
- Separate apps, shared database, separate database schemas. This is my preferred option.
I prefer option four because it’s easier to work on and maintain separate applications. Separate schemas ensure that each app can manage its own database. But a shared database makes it easier reuse data, setup joins, and database constraints or maintain referential integrity. It’s as easy as joining a different prefix.
For example, an
estimates.cart (schema.table) could have an
owner_id foreign key that points to
estimates.line_items can have a
product_id column that could point to
Does this sound like a good idea? Has anyone done this type of architecture? What disadvantages (other than some schema data being shared between migrations) do you see?
Would you go down this route, or would you recreate data where needed? For example, multiple user schemas and so on…