Cohort Management

Problem Statement

The system requires a mechanism to manage cohorts, which are groups of users with different contexts, and track the members within each cohort. Additionally, it needs to maintain a record of the roles assigned to each user within a cohort.

Approach

We will design and implement a database schema to store information about cohorts and their members. This schema will include tables for cohorts and cohort members. The database design will be optimized for efficient querying and management of cohort-related data.

Solution

• Cohort Table: This table will store information about cohorts, including their unique identifiers, names, types, creation timestamps, and other relevant metadata.
• Cohort Members Table: This table will maintain records of cohort memberships, specifying the association between users and cohorts, along with timestamps for creation and updates.

DB Design

Cohort Table

Column Name	Data Type	Description
cohortId	UUID	Primary key
name	VARCHAR	Name of the cohort
type	VARCHAR	Type of the cohort
parentId	UUID	ID of the parent cohort (if any)
image	VARCHAR	Path to an image associated with the cohort
tenantId	UUID	Foreign key referencing the Tenants table
createdBy	UUID	ID of the user who created the cohort
updatedBy	UUID	ID of the user who last updated the cohort
createdAt	TIMESTAMP	Timestamp indicating when the cohort was created
updatedAt	TIMESTAMP	Timestamp indicating when the cohort was last updated
params	JSONB	The parameters, such as attendanceCaptureEnabled, will be stored in JSON format under 'params'.
status	BOOLEAN	Indicates whether the cohort is active or not

Cohort Members Table

Column Name	Data Type	Description
cohortMembershipId	UUID	Primary key
cohortId	UUID	Foreign key referencing the Cohort table
userId	UUID	Foreign key referencing the Users table
createdBy	UUID	ID of the user who created the membership
updatedBy	UUID	ID of the user who last updated the membership
createdAt	DATE	Date when the membership was created
updatedAt	DATE	Date when the membership was last updated

This design will allow for efficient management of cohorts and their members, providing a robust solution for organizing and tracking users within different contexts.

Cohort Management

Generic Field Entity with FieldValues

Tenants