We have a monolithic application built with a Flask/Pony framework. It works great right now.
However, in the near future we are going to start shifting to a micro-service architecture. In this process, we are going to most likely have a separate Db for each service. For a number of reasons that I wont go into, this is beneficial. But i get hung up, and I know many others do too, when thinking about the foreign key relationships developed between tables at the moment.
People have solved this by denormalizing their db's ending up with duplicated tables in another service's DB.
Im curious what the users of pony think of this solution and if its feasible.
And what have others using PonyORM, or any other ORM for that matter, come up with when using a micro service architecture?
Edit:
Our database right now is simplistically:
Main DB
User
|-id
|-name
|-role = Required("Role")
Role
|-id
|-name
|-set("User")
Now this is all in one Db but the logic is within two different services. User service and role service. The role service controls authorizations and views allowed that the user is allowed to access.
Ideally, I would like to split these into separate Db's, but still be able to keep the relationship between the two. I don't think that it can be done through a natural sql query/index (or can it?).
First of all I should notice, that splitting an application into microservices is not always a good idea, as it can add more complexity. Here is a couple of links to interesting articles and its discussions on HN: The End of Microservices, Enough with the microservices, Modules vs Microservices.
But you've mentioned that there are reasons for this move. In this case, you cannot avoid duplication of some data in both databases. One of the possible ways to split the original database is the following:
The first database will have the User and the Role entities, but the Role entity will only have a minimal number of attributes - id and name.
The second database will have no User entity, only the Role entity with full information about the rights, that this role provides. The id and the name attributes of the Role objects in the second database should have the same value as the ones in the first database.
This way the relationship between the User and the Role entities will not be broken, as they belong to the same database. This will help keeping data integrity. In the same time, there will be some operations that require synchronous change in both databases - roles creating, renaming and deleting. For this purpose you can make the changes in the Role microservice first and then send the corresponding request to the User microservice. But you need to implement the logic that will make sure that both changes were made without errors, retry or rollback if the second change wasn't successful.