Enable dynamic updation of learners' course progress
Introduction
This wiki explains current design of course batches, problems with it and proposed changes to the design and implementation to resolve the current issues and able to handle scale.
Background & Problem statement:
In current state changes stored in lms service are not reactive to any changes in the course content. This structure creates the issue when some of the contents changes and Sunbird uses locally stored copy of the content. For example, Once the user has completed the full course, if any unit is added or removed from the course, the changes are not dynamically reflected. There are use cases to enable this.
Key Design Problems:
1. Easy way to maintain course details for a batch.
2. Better handling of course progress computation.
3. Easy to fetch data for given course or batch or user.
Design:
Managing Course Details:
Batches are created for a course. Course details are most of the time constant but, when the creator publishes it the data will be updated. So, copying the course details and saving along with course batch and user_courses is not good.
Option 1: Maintain cache with ttl
Option 2: Always fetch it from source
Solution Approach
A complete solution can be divided into below points
remove the course data stored into db (ex. leafNodeCounts, courseName, tocUrl etc.)
Restructure the DB tables and corresponding ES
migrate the old data according to new structure
Code changes in accordance with the restructured data model
Change the sync process of user progress update to an async process with the help of kafka messaging.
Current DB structure related to course batch progress
course_batch (ES → cbatch)
column | type | description | used | indexed | can be removed |
|---|---|---|---|---|---|
id | text | batchid |
| PK |
|
countdecrementdate | text |
|
|
|
|
countdecrementstatus | boolean |
|
|
|
|
countincrementdate | text |
|
|
|
|
countincrementstatus | boolean |
|
|
|
|
coursecreator | text |
|
| Y |
|
courseid | text |
|
| Y |
|
createdby | text |
|
|
|
|
createddate | text |
|
|
|
|
createdfor | list<text> |
|
|
|
|
description | text |
|
|
|
|
enddate | text |
|
|
|
|
enrollmenttype | text |
|
| Y |
|
hashtagid | text |
|
|
|
|
mentors | list<text> |
|
|
|
|
name | text |
|
|
|
|
participant | map<text, boolean> |
|
|
|
|
startdate | text |
|
|
|
|
status | int |
|
| Y |
|
updateddate | text |
|
|
|
|
content_consumption
column | type | description | used | indexed | can be removed |
|---|---|---|---|---|---|
id | text | hash of userid, batchid, courseid and contentid |
| PK |
|
batchid | text |
|
|
|
|
completedcount | int | how many time the content was completed |
|
|
|
contentid | text |
|
| Y |
|
contentversion | text |
|
|
|
|
courseid | text |
|
| Y |
|
datetime | timestamp |
|
|
|
|
grade | text |
| N |
| Y |
lastaccesstime | text |
|
|
|
|
lastcompletedtime | text |
|
|
|
|
lastupdatedtime | text |
|
|
|
|
progress | int | progress pushed |
|
|
|
result | text |
| N |
| Y |
score | text |
| N |
| Y |
status | int | 1 for progress 2 for completed |
| Y |
|
userid | text |
|
| Y |
|
viewcount | int | how many times user viewed the content |
|
|
|
user_courses (ES → usercourses)
column | type | description | used | indexed | can be removed |
|---|---|---|---|---|---|
id | text | hash of userid, batchid and courseid |
| PK |
|
active | boolean | turns false when user unenroll |
|
|
|
addedby | text |
|
|
|
|
batchid | text |
|
| Y |
|
completedon | timestamp |
|
|
|
|
contentid | text |
|
|
|
|
courseid | text |
|
| Y |
|
courselogourl | text |
|
|
| Y |
coursename | text |
|
| Y | Y |
datetime | timestamp |
|
|
|
|
delta | text |
| N |
| Y |
description | text |
|
|
| Y |
enrolleddate | text |
|
|
|
|
grade | text |
| N |
| Y |
lastreadcontentid | text |
|
|
|
|
lastreadcontentstatus | int |
|
|
|
|
leafnodescount | int |
|
|
| Y |
progress | int | count of content completed |
|
|
|
status | int | 1 for progress 2 for completed |