How to handle recurring orders schema in mongodb

I am building an app which serves daily essentials like milk to customer. The milk subscription can be of 2 main types -

  1. Regular Interval (say every 2 day)
  2. Custom Interval (say every Tuesday and Thursday)

The customer should be able to edit their regular subscription for a period of time (say as usual I want 1 ltr milk but for 15th Feb to 30th Feb I want 2 ltrs)

User can also say pause my subscription for next 10 days as I am on vacation

Any suggestions/help with schema design will be helpful

Hi @Abhibhaw_Asthana ,

It sounds that main collection should be a user subscription for a product or a group of products. It should have the details for a subscription:

_id : ... , 
productName : "milk",
deliveryAddress : ... ,
startingDate : ... ,
status : "active"
deliveryDefaultDetails : {
  Interval: { 
     type: "custom",
     value : ["monday" , "Thursday" ... ]
    // -or-
    type: "every",
    value : "2 days"
productDetails : { size : "1litr" } ...

As you can see each product has its default delivery details. It can be a dynamic object or array with specific fields per requirement. Those are the defaults if no exceptions are submitted for specific dates.

Now for the exceptions I recommend to have an exception collection with specific exceptions for specific dates. The idea is that for each delivery day the application will check if the defaults are not supposed to be overwritten by another value for that day from an exception. For example the exceptions collection will be :

 _id : ... , 
SubscriptionId : [From subscription collection],
exceptionstartDate : ... ,
exceptionendDate : ...
exceptionDetails :
[ { 
    deliveryDetails : { 
                  productDetails : "2 litr",
     // -or-
    status : "paused"

Now if you index subscirptionId : 1, exceptionstartDate : 1, exceptionendDate : 1
Together , you can search what defaults needs to be overridden for each delivery day.

If there are multiple exceptions for a single day have the application merge and decide what is the end override …

Let me know if that makes sense.


1 Like

Thanks a lot it seems like an appropriate way :raised_hands::raised_hands: A little doubt, how to define an “or” in schema I am using mongoose package.

Hi @Abhibhaw_Asthana ,

To be honest mongoose is a third party framework that been adopted for schema enforcement as its main advantage. Now that MongoDB has built in easy to use schema validation we do not see benefits in using mongoose and using Node JS driver with the objects as is would be a prefared way.

Now the “or” is a conceptional idea, one object can have field x and the other field y and the application can read the object, look at the keys and define the specific patterns or workflows. Another option is to use generic names for the fields like :key and value.

So one document can have :

Interval: { 
 key : "custom",
"value": [ { "day" : "Monday" }, {"day" : "Tueday"}  ]

While another document can have :

Interval: { 
 key : "periodic",
"value":  { "granularity" : "day" ,  quantity : 2}  

This is the advantage of a flexible schema and a document store.


1 Like

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.