Search code examples
.netsql-serverarchitecturemicroservicessystem-design

Relating two entities in different databases and communication between two applications

Short story: how do I relate two entities in different databases? In microservices each service has its own database, how are the entities in different databases related to each other in a microservice architecture?

Long story: I am writing two separate but related applications, lets say a management system for used vehicle sellers and an accounts and finance system. I want my applications to work with each other and independently as well.

Now lets suppose a vehicle deal is finalized in the management system then from the finalized deal data I would generate an invoice in the finance system. Similarly if the deal is getting deleted then I would have to delete the corresponding invoice as well but to delete the corresponding invoice I want to somehow relate the deal and the invoice but I cant simply make a relation between the entities because I want to my applications to be completely independent which means different databases as well. Or is there a way to make a relation between two tables in different databases? Is it even a good idea to make relation two tables of different databases?

Furthermore, I want two way communication, for example if a deal is edited then its related invoice gets edited as well and if an invoice is edited then its related deal gets edited as well also just like microservices I want both the systems to be able to run independently and alongside each other as well.

Possible solution: Only solution I can think of right now is to have a single database and two separate application layers but this design is no good itself as I have used this approach before and it has a lot of its own drawbacks.

Technology Stack: My technology stack is .Net and MSSQL

I am looking for answers from people who have designed this kind of a system or have faced similar problems before

Solution

  • Use uuids for identifying your entities and reference those as a simple Strings.

    See How can I generate UUID in C#

    For example: 

    {
  "Name" : "MyDeal",
  "Id" : "6a0e5244-2a85-4944-9be4-04c8cfbf3e45"
}

{
  "Name" : "MyInvoice",
  "Id" : "efed8b94-0065-4004-adcc-9f2d219fb6eb",
  "DealReference" : "6a0e5244-2a85-4944-9be4-04c8cfbf3e45"
}

    Do not use common persistence, such as a common database. See https://stackoverflow.com/a/43618284/7803650.