DP World | SDE3 | Hyderabad | 14 June 2022 | [Result (Reject)]

I was asked to design Attendance Management System in DP World.

The interviewer joined exactly 5 min late and the last 5 min were kept for DP World tech discussion = 10 min
We had a brief discussion on the Intro of what he does and what I do around 10-12 min = ~10 min

In these remaining 40 mins-
our discussion went ahead with the capacity estimation, eventual consistency of service and low-level design of the service.
After our LLD discussion, we had only 2 min left for the high-level (design) discussion.

Any thoughts how should I done better in terms of solution and time management.

Below is my solution for the same problem.

Attendance Management System:

5-6 Org + 5
10k Employee

Basic Functionalities of our Service:

1. To on-board organisations
2. Organisations can add different departments
3. Organisations can add employees to different departments
4. Employees can add/edit/delete their details
5. Employees should be able to fill attendance
6. Punching machine - push data
7. Manual entries
8. An employee can add the attendance of past also (apart from today)
9. Approval is not needed for attendance
10. Reminder for attendance

Non Functional requirement of our Service:

1. Highly Available Module (99.999%)
2. Eventual Consistency
3. Low latency
4. Durability/Persistance (Attendance shouldn’t be lost)

Scale Estimation:
No of Current Organizations: 5
Future organisations: 5
Employees in One Organization: ~100k

Current Daily Active Employees: 100k

100 per cent attendance captured by punching machine
10:00 office hour
90 percent traffic : [9:30 to 10:30]

READ:WRITE == 1:1
READ/WRITE
PEAK: No of requests on service: 90k/hour = 90000/60*60 = 900/36 = ~=25QPS

Storage Estimation:

Basic Flow:

Employees - Punching Machine —----Interaction with —----- Employee Attendance module

Punching Machine:
Input: Thumb/Card of employee
Processing: Checking Authenticity
Output: Date, Time, Employee_Details

Input: Date, Time, Employee_Details
Loadbalances - Employee Attendance Service - Save to – Database

Eventual Consistency:
2 places

1. Third party attence software - calling backend service - save to one of our database machines and asynchronous save to other replication
2. Third party attence software - save to disk storage and update like below period
   1. At the end of the day at 10 PM - transferring data
   2. At the end of the week at 10 PM - transferring data
   3. At the end of month at 10PM - transferring data

Queue:
Load on database
Multi-master
Queue - Message Queue- Amazon SQS - consume strictly at once
[.............]: Database

RDBMS: Schema

Organisation Table:

Employee Table: employee_id (primary key), first name, last name, gender, contact_details, emp_email, age

Departments Table: department_id(primary key), department_name

Attendance Table: attendance_id, employee_id, date, entry_time, end_time, is_present,