CODEWITHSUNDEEP
mysqlnode.jsmongodbdatabase-designdatabase
Omar Kanawati
Omar Kanawati

Reputation: 59

Attendance System MongoDB design

I need to design and implement a database for employee's attendance system. The db need not to be a non-relational, I can go with whatever suits best with the requirements. The requirements are simple, I need to store employees information along with their clock in and out times.

Data requirements are as follows:

  1. Number of employees will not be big (20-50).
  2. Ability to retrieve all attendance times for all employees for a specific day or range of days (for a month for example).
  3. Ability to add/modify/remove attendance times for specific employees.
  4. Ability to retrieve calculated late attendance for each employee. (Employee is considered late according to some business rules related to attendance times and employee's information).

-Is using MongoDB better that using relational SQL (like mySQL)?

-What's the suggested high level design of the DB that will best simplifies DB implementation, data access, and application development?

Upvotes: 0

Views: 4844

Answers (2)

Azwok
Azwok

Reputation: 203

This design can be achieved by both MongoDB or a relational database, with strengths and weaknesses of each. The schema design by user641887 is a perfectly valid approach with MongoDB, although I wouldn't use "date" as the "_id" in attendence, as two employees on the same day will have the same "_id" which is invalid, I would leave the "_id" of attendee of an Object_id. However just be aware about the limitations in mongo with collection joins, as you will need to look into the '$lookup' function (https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/), which was only added in Mongo-3.2. The advanatage of a mongo design is that it allows each document in the attendance table that user641887 proposed by be dynamic, and should this database grow very large, it shouldn't be too hard to scale the database. But I doubt that will be a concern if there are only 50 staff with 1 entry per day (50*365 = 18250 per year), even 10 years of data is a very small amount.

The above requirements can also be achieved using a relational structure, where you would again have 2 tables as described by user641887. Depending how many additional pieces of information you want to store in the "other attribute/parameters" you have a couple of options. If there are only a few known possible other attributes, then you can add a few nullable fields to each table. But if there are many fields which could exist or you don't know what to expect before you add them, then you can have two additional tables associated with employee:

employee_attributes:

  • employee _id : the _id code that matches the employee _id in the employee table
  • attribute_code : an integer code that links to the code_description table (below)
  • attribute_value: the value of the attribute

NOTE: This approach with a single attribute table is limited to the attribute_value only having one data type (most likely string), but if you need to have multiple data types you can resolve that by having multiple employee attribute tables for each data type, e.g. employee_attribute_i (for ints), employee_attribute_s (for strings), employee_attribute_b (for booleans).

attribute_code_description:

  • attribute_code : the int code of this attribute
  • attribute_meaning: a string description of what this attribute is for (e.g. "allergies", "probation", "start_time", ...)

This same approach can be used for the "other attendance parameters".

With regards to "calculated late attendance for each employee", then you can set up triggers/rules to automatically fire that can add to a counter for each employee to monitor if they are late. This will work by firing a trigger upon insert into the the attendee table where the in_time field that can then by compared with the employees "start_time", if it is greater that that, +1 to a counter that logs how often they are late. I know that can be done in several relational databases (postgres/ingres certainly, and I'm sure many others). I don't know if that can be done on a mongo server.

Upvotes: 3

user641887
user641887

Reputation: 1576

you could have 2 collections one for the employee and one for the attendance.

employee collection can have attributes related to the employee

  • _id : Object_id
  • name : string
  • email : string
  • ... other employee attributes

and attendance collection can have attributes related to attendance.

  • _id : date (you can store date as string or any other format to make it unique per day
  • in_time : date
  • out_time : date
  • other attendance parameters....
  • employee_id : (_id for employee)

HTH.

Upvotes: 3

Related Questions