Skip to content

03 Data Model

Jump to bottom
Huzefaaa2 edited this page Jan 29, 2026 · 1 revision

Data Model & Database Schema

Entity Relationship Diagram

erDiagram
    MB_USERS ||--o{ LEARNING_MODULES : "has"
    MB_USERS ||--o{ CAREER_SURVEYS : "completes"
    MB_USERS ||--o{ YOUTH_FEEDBACK_SURVEYS : "submits"
    MB_USERS ||--o{ EMPLOYER_FEEDBACK_SURVEYS : "receives"
    MB_USERS ||--o{ SURVEY_DISTRIBUTION_LOGS : "targeted"
    SURVEY_TEMPLATES ||--o{ SURVEY_DISTRIBUTION_LOGS : "uses"
    LEARNING_MODULES ||--o{ SURVEY_TEMPLATES : "relates"
    
    MB_USERS {
        int user_id PK "Primary Key"
        string login_id UK "Unique: Email"
        string password "Hashed"
        string student_id "Format: MB-APAC-2026-XXXXX"
        string role "student, admin, instructor"
        string email UK
        string full_name
        string phone
        date dob
        string institution
        string education_level
        string skills "CSV or JSON"
        timestamp created_at
    }
    
    LEARNING_MODULES {
        int module_assignment_id PK
        int user_id FK
        string module_id "Unique module identifier"
        string title
        string description
        int duration "Hours"
        string skills "Comma-separated"
        string prerequisites
        string difficulty_level "Beginner, Intermediate, Advanced"
        string status "active, inactive, completed"
        int progress "0-100"
        date started_date
        date completed_date
        date assigned_date
    }
    
    CAREER_SURVEYS {
        int survey_id PK
        int user_id FK
        string student_id
        json survey_data "JSON responses"
        timestamp completed_at
    }
    
    YOUTH_FEEDBACK_SURVEYS {
        int survey_id PK
        int user_id FK
        string student_id FK
        string placement_company
        string job_title
        date survey_date
        date sent_date
        date completed_date
        string completion_status "pending, completed"
        int overall_performance "1-5"
        int technical_skills "1-5"
        int communication_skills "1-5"
        int teamwork "1-5"
        int work_ethic "1-5"
        int punctuality "1-5"
        int reliability "1-5"
        int problem_solving "1-5"
        string strengths "Text"
        string areas_for_improvement "Text"
        boolean would_rehire
        string feedback_comments
        int recommendation_score "1-10"
        timestamp created_at
    }
    
    EMPLOYER_FEEDBACK_SURVEYS {
        int survey_id PK
        string student_id FK
        string employer_name
        string employer_email
        string job_title
        date survey_date
        date sent_date
        date completed_date
        string completion_status
        int overall_performance "1-5"
        int technical_skills "1-5"
        int communication_skills "1-5"
        int teamwork "1-5"
        int work_ethic "1-5"
        int punctuality "1-5"
        int reliability "1-5"
        int problem_solving "1-5"
        string strengths
        string areas_for_improvement
        boolean would_rehire
        string feedback_comments
        int recommendation_score "1-10"
        timestamp created_at
    }
    
    SURVEY_TEMPLATES {
        int template_id PK
        string template_type "youth_feedback, employer_feedback, career"
        string template_name
        json questions_json "Array of questions"
        int version "Versioning"
        boolean is_active
        date created_date
    }
    
    SURVEY_DISTRIBUTION_LOGS {
        int log_id PK
        string survey_type "youth_feedback, employer_feedback"
        string recipient_email
        string recipient_type "youth, employer"
        int survey_id FK
        string student_id FK
        date sent_date
        boolean opened "Click tracking"
        date opened_date
        boolean completed
        date completion_date
        string survey_link "Unique token"
    }
Loading

Feature Tables

1. student_daily_features (50 rows)

Column Type Example Purpose
user_id INT 1 Link to student
student_id STRING MB-APAC-2026-ABC Student identifier
email STRING student@magicbus Contact
registration_date DATETIME 2026-01-29 Onboarding date
modules_assigned INT 2 Total modules
modules_completed INT 1 Finished count
avg_completion_pct INT 50 Average progress
modules_started INT 2 Started count
days_since_registration INT 15 Account age
feature_timestamp DATETIME NOW() Computation time

2. student_dropout_risk (50 rows)

Column Type Example Purpose
user_id INT 1 Student ID
student_id STRING MB-APAC-2026-ABC Identifier
email STRING student@magicbus Contact
modules_assigned INT 2 Context
modules_completed INT 1 Context
modules_started INT 1 Context
avg_completion_pct INT 50 Context
days_since_registration INT 15 Context
dropout_risk_level ENUM HIGH/MEDIUM/LOW Risk Level
risk_score INT 1-9 Risk Score
risk_reason STRING "No modules started" Reason
risk_computed_at DATETIME NOW() Computation time

Risk Scoring Logic:

  • HIGH: risk_score ≥ 7 (No activity, low completion)
  • MEDIUM: risk_score 4-6 (Some activity, moderate risk)
  • LOW: risk_score ≤ 3 (Active, good progress)

3. student_sector_fit (50 rows)

Column Type Example Purpose
user_id INT 1 Student ID
student_id STRING MB-APAC-2026-ABC Identifier
sector_interests STRING "Design & UI/UX" Interest
interest_confidence INT 1-100 Confidence
skill_readiness_score INT 1-100 Skill Level
sector_fit_score INT 0-100 Overall Fit
readiness_status ENUM Green/Amber/Red Status
computed_at DATETIME NOW() Computation time

Status Mapping:

  • Green: fit_score ≥ 70 (Well-aligned)
  • Amber: fit_score 50-69 (Moderate alignment)
  • Red: fit_score < 50 (Poor alignment)

4. module_effectiveness (16 rows)

Column Type Example Purpose
module_id INT 1 Module identifier
module_name STRING "Python Basics" Display name
learners INT 25 Total students
completed_count INT 15 Completed count
completion_rate INT 60 Percentage
avg_completion_pct INT 65 Average progress
avg_points_earned INT 450 Points/learner
effectiveness_level ENUM High/Medium/Low Ranking
computed_at DATETIME NOW() Computation time

5. gamification_impact (2 rows)

Column Type Example Purpose
group_type STRING "Badge Earners" Comparison Group
user_count INT 20 Group size
avg_engagement_pct INT 80 Engagement %
completion_rate INT 85 Completion %

Groups:

  • Row 1: Badge & Points Earners
  • Row 2: Non-Gamification Participants

6. mobilisation_funnel (4 rows)

Column Type Example Purpose
funnel_stage STRING "Registered" Stage name
count INT 50 Count at stage
pct_of_registered INT 100 Percentage

Funnel Stages:

  1. Registered: 50 (100%)
  2. Started Learning: 40 (80%)
  3. Quiz Participation: 30 (60%)
  4. Achievement: 25 (50%)

Database Schema SQL

-- Users Table
CREATE TABLE mb_users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    login_id TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    student_id TEXT UNIQUE,
    role TEXT DEFAULT 'student',
    email TEXT UNIQUE NOT NULL,
    full_name TEXT,
    phone TEXT,
    dob DATE,
    institution TEXT,
    education_level TEXT,
    skills TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Learning Modules
CREATE TABLE learning_modules (
    module_assignment_id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    module_id TEXT,
    title TEXT NOT NULL,
    description TEXT,
    duration INTEGER,
    skills TEXT,
    prerequisites TEXT,
    difficulty_level TEXT,
    status TEXT DEFAULT 'active',
    progress INTEGER DEFAULT 0,
    started_date DATE,
    completed_date DATE,
    assigned_date DATE,
    FOREIGN KEY (user_id) REFERENCES mb_users(user_id)
);

-- Feature Tables (For Analytics)
CREATE TABLE student_daily_features (
    user_id INTEGER NOT NULL,
    student_id TEXT,
    email TEXT,
    registration_date DATETIME,
    modules_assigned INTEGER,
    modules_completed INTEGER,
    avg_completion_pct INTEGER,
    modules_started INTEGER,
    days_since_registration INTEGER,
    feature_timestamp DATETIME,
    FOREIGN KEY (user_id) REFERENCES mb_users(user_id)
);

CREATE TABLE student_dropout_risk (
    user_id INTEGER NOT NULL,
    student_id TEXT,
    email TEXT,
    modules_assigned INTEGER,
    modules_completed INTEGER,
    modules_started INTEGER,
    avg_completion_pct INTEGER,
    days_since_registration INTEGER,
    dropout_risk_level TEXT,
    risk_score INTEGER,
    risk_reason TEXT,
    risk_computed_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES mb_users(user_id)
);

CREATE TABLE student_sector_fit (
    user_id INTEGER NOT NULL,
    student_id TEXT,
    sector_interests TEXT,
    interest_confidence INTEGER,
    skill_readiness_score INTEGER,
    sector_fit_score INTEGER,
    readiness_status TEXT,
    computed_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES mb_users(user_id)
);

-- Create indexes for performance
CREATE INDEX idx_users_email ON mb_users(email);
CREATE INDEX idx_users_role ON mb_users(role);
CREATE INDEX idx_modules_user ON learning_modules(user_id);
CREATE INDEX idx_features_user ON student_daily_features(user_id);
CREATE INDEX idx_dropout_user ON student_dropout_risk(user_id);
CREATE INDEX idx_sector_user ON student_sector_fit(user_id);

Data Dictionary

Enumeration Values

Role:

  • student - Youth user (learner)
  • admin - Administrator (system management)
  • instructor - Content creator

Learning Status:

  • active - Module currently available
  • inactive - Module archived
  • completed - Module finished by student

Difficulty Level:

  • Beginner
  • Intermediate
  • Advanced

Survey Completion Status:

  • pending - Sent but not completed
  • completed - Response received

Readiness Status:

  • Green - High readiness
  • Amber - Moderate readiness
  • Red - Low readiness

Dropout Risk Level:

  • HIGH - Risk score 7-9
  • MEDIUM - Risk score 4-6
  • LOW - Risk score 1-3

Effectiveness Level:

  • High Impact - Completion ≥ 80%
  • Medium Impact - Completion 60-79%
  • Needs Improvement - Completion < 60%

Referential Integrity

mb_users (Primary)
├── learning_modules (Foreign Key: user_id)
├── career_surveys (Foreign Key: user_id)
├── youth_feedback_surveys (Foreign Key: user_id)
├── employer_feedback_surveys (Foreign Key: user_id)
└── survey_distribution_logs (Foreign Key: user_id)

Performance Indexes

-- Query Performance Optimization
CREATE INDEX idx_users_email ON mb_users(email);
CREATE INDEX idx_users_role ON mb_users(role);
CREATE INDEX idx_modules_user ON learning_modules(user_id);
CREATE INDEX idx_modules_status ON learning_modules(status);
CREATE INDEX idx_features_user ON student_daily_features(user_id);
CREATE INDEX idx_dropout_user ON student_dropout_risk(user_id);
CREATE INDEX idx_sector_user ON student_sector_fit(user_id);
CREATE INDEX idx_surveys_user ON youth_feedback_surveys(user_id);
CREATE INDEX idx_dist_email ON survey_distribution_logs(recipient_email);

Last Updated: January 29, 2026

Clone this wiki locally