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.

Campaign Builder

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.


  1. The first challenge of this design is to insert the workflow into the database with parent child relationship.
  2. 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. enter image description here

Your Answer

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

Browse other questions tagged or ask your own question.