How to create schema for nested structures?

MongoDB Atlas App Services & Realm Atlas GraphQL API
, , , ,
1

I am managing an HR database that consists of two collections: ‘employees’ and ‘attendances.’ To optimize performance, I have structured the ‘attendances’ collection by grouping attendance objects based on the employee _id. The challenge now lies in defining the schema for the following data:

attendances Collection :

[
  {
     _id: ObjectId('65e192de07cd275142fc7ef6')
      {
         "65e190a435710d49fb9adcae":{
            "2024-01-01":{
              "clocks": {
                  "08:30" : {"by": "gate1", "at": "2024-01-01T08:30" }
               },
              "TWH":"08:45"
            }
          },
         "65e190a444710d49fb9adcae":{
            "2024-01-01":{
              "clocks": {
                  "08:35" : {"by": "user", "at": "2024-01-01T12:30" }
               },
              "TWH":"08:15"
            }
        }
     }
  }
]
2

I see 3 things I try to avoid.

Using values as field names

You have object ids, dates and times as field name. Using the attribute pattern is a good way to solve this. Otherwise, you might have to resort to $objectToArray and $arrayToObject.

Storing object ids as string rather than ObjectId

When you store object ids as string you have to $convert to ObjectId whenever you need to $lookup or $match in or from the collection where the original documents reside.

Storing dates as string rather than Date

The field at uses a string to store the value of a Date object. Date values take less space, are faster and provide a richer API than a string representing the same date/time.

The above being stated, I have to admit that sometimes I digress from the 3rd point. In some occasions I prefer to store date as number. For example, string dates like “2024-01-01” will be stored as the number 20240101. That preserves the ordering and the speed and space of a Date but helps in some human direct use-cases. Ditto, for time like “08:30” which will be store as 830. The direct human interaction with the database must be more frequent than the processing of the date/time by code.

Example of a use-case where the human interaction use-cases are more frequent than processing by code:

I store some log into the database, I have no API or application that uses the logs. I perform all the interaction with the stored data directly in compass and mongosh. It is much faster to type find({date:20240101} compared to find({date:"2024-01-01"} or find({date:new Date(2024,01,01)}. And in the case of the string you have to remember that you used - rather than /.

3

@Rajan_Baraiya, I have spent my precious time working on your issue. I think I have provided valuable information. I would appreciate a followup.