I am working on a very challenging email marketing database design and i am not able to figure out the best way to build the database, where functionality can be split so it can be used as a microservice.
Our system would have leads and these leads will be added to a campaign. A campaign is a predefined set of instructions that would be applied to every lead. The campaign would have a GUI workflow builder which would actually be built up of 3 instructions i.e email, trigger and delay. Email is a simple email sending instruction, trigger would be if the email is opened or clicked, and delay would be simply to wait for x number of day.
Let me explain with an example. A new lead is added to a campaign with the below workflow.
Explaination: The system would send email A to the user. If the user opens the mail then our system would know that the mail is viewed or not. In both cases the system would wait for 1 day to make a decision and then send another mail. If the mail is opened then email B will be sent else email D. After the email B is sent the system would wait for another 2 days and then send email C.
- The first challenge of this design is to insert the workflow into the database with parent child relationship.
- The next challenge is to isolate a part of the application to work as microservice, specially the email sending part.
What i have come up with: Below is my poor effort. I know this is a very bad design with lots of denormalization but i would like you to suggest something better. Its ok to even reject the whole design.