Sample Data Structure for MGSLG Analytics Prototype

Sample Data Structure for MGSLG Analytics Prototype

Overview

This document provides sample data structures and example records to guide prototype development and demonstrate the expected data relationships and analytics capabilities.

Sample Data Schema

1. Participants Table

CREATE TABLE participants (
    participant_id VARCHAR(20) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    date_of_birth DATE,
    gender ENUM('Male', 'Female', 'Other', 'Prefer not to say'),
    street_address VARCHAR(200),
    city VARCHAR(50),
    province VARCHAR(30),
    postal_code VARCHAR(10),
    current_position VARCHAR(100),
    institution_id VARCHAR(20),
    years_experience INTEGER,
    sector ENUM('Public', 'Private', 'NGO', 'Other'),
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Sample Participant Records

[
  {
    "participant_id": "MGS2023001",
    "first_name": "Nomsa",
    "last_name": "Mthembu",
    "email": "nomsa.mthembu@education.gov.za",
    "phone": "+27823456789",
    "date_of_birth": "1985-03-15",
    "gender": "Female",
    "city": "Cape Town",
    "province": "Western Cape",
    "current_position": "Deputy Principal",
    "institution_id": "SCH001",
    "years_experience": 12,
    "sector": "Public"
  },
  {
    "participant_id": "MGS2023002",
    "first_name": "Thabo",
    "last_name": "Maleka",
    "email": "t.maleka@school.co.za",
    "phone": "+27845678901",
    "date_of_birth": "1978-08-22",
    "gender": "Male",
    "city": "Johannesburg",
    "province": "Gauteng",
    "current_position": "School Principal",
    "institution_id": "SCH002",
    "years_experience": 18,
    "sector": "Public"
  },
  {
    "participant_id": "MGS2023003",
    "first_name": "Sarah",
    "last_name": "Williams",
    "email": "sarah.williams@privatesch.co.za",
    "phone": "+27876543210",
    "date_of_birth": "1990-12-05",
    "gender": "Female",
    "city": "Durban",
    "province": "KwaZulu-Natal",
    "current_position": "Head of Department",
    "institution_id": "SCH003",
    "years_experience": 8,
    "sector": "Private"
  }
]

2. Programs Table

CREATE TABLE programs (
    program_id VARCHAR(20) PRIMARY KEY,
    program_name VARCHAR(200) NOT NULL,
    category ENUM('Leadership', 'Governance', 'Skills Development', 'Other'),
    target_audience ENUM('Educators', 'School Leaders', 'Governing Bodies', 'Mixed'),
    duration_days INTEGER,
    delivery_mode ENUM('In-Person', 'Online', 'Hybrid'),
    description TEXT,
    learning_outcomes TEXT,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Sample Program Records

[
  {
    "program_id": "PROG001",
    "program_name": "Advanced School Leadership Certificate",
    "category": "Leadership",
    "target_audience": "School Leaders",
    "duration_days": 5,
    "delivery_mode": "In-Person",
    "description": "Comprehensive leadership development program for school principals and deputy principals focusing on strategic planning, change management, and educational leadership.",
    "learning_outcomes": "Strategic thinking, Change management, Team leadership, Educational policy understanding"
  },
  {
    "program_id": "PROG002",
    "program_name": "School Governance Fundamentals",
    "category": "Governance",
    "target_audience": "Governing Bodies",
    "duration_days": 3,
    "delivery_mode": "Hybrid",
    "description": "Essential training for school governing body members covering legal responsibilities, financial oversight, and educational governance.",
    "learning_outcomes": "Legal compliance, Financial oversight, Policy development, Stakeholder engagement"
  },
  {
    "program_id": "PROG003",
    "program_name": "Digital Classroom Integration",
    "category": "Skills Development",
    "target_audience": "Educators",
    "duration_days": 2,
    "delivery_mode": "Online",
    "description": "Practical training on integrating digital tools and technologies into classroom instruction.",
    "learning_outcomes": "Digital literacy, Technology integration, Online teaching methods, Student engagement strategies"
  }
]

3. Program Instances Table

CREATE TABLE program_instances (
    instance_id VARCHAR(20) PRIMARY KEY,
    program_id VARCHAR(20),
    start_date DATE,
    end_date DATE,
    location VARCHAR(100),
    trainer_name VARCHAR(100),
    capacity INTEGER,
    actual_attendance INTEGER,
    FOREIGN KEY (program_id) REFERENCES programs(program_id)
);

Sample Program Instance Records

[
  {
    "instance_id": "INST2024001",
    "program_id": "PROG001",
    "start_date": "2024-03-15",
    "end_date": "2024-03-19",
    "location": "Cape Town Training Center",
    "trainer_name": "Dr. Patricia Ndlovu",
    "capacity": 25,
    "actual_attendance": 22
  },
  {
    "instance_id": "INST2024002",
    "program_id": "PROG002",
    "start_date": "2024-04-08",
    "end_date": "2024-04-10",
    "location": "Johannesburg Conference Center",
    "trainer_name": "Prof. Michael Seboko",
    "capacity": 30,
    "actual_attendance": 28
  },
  {
    "instance_id": "INST2024003",
    "program_id": "PROG003",
    "start_date": "2024-05-20",
    "end_date": "2024-05-21",
    "location": "Online Platform",
    "trainer_name": "Ms. Jennifer Adams",
    "capacity": 50,
    "actual_attendance": 45
  }
]

4. Enrollments Table

CREATE TABLE enrollments (
    enrollment_id VARCHAR(20) PRIMARY KEY,
    participant_id VARCHAR(20),
    program_instance_id VARCHAR(20),
    enrollment_date DATE,
    completion_status ENUM('Enrolled', 'Completed', 'Withdrawn', 'No-Show'),
    completion_date DATE,
    certificate_issued BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id),
    FOREIGN KEY (program_instance_id) REFERENCES program_instances(instance_id)
);

Sample Enrollment Records

[
  {
    "enrollment_id": "ENR2024001",
    "participant_id": "MGS2023001",
    "program_instance_id": "INST2024001",
    "enrollment_date": "2024-02-15",
    "completion_status": "Completed",
    "completion_date": "2024-03-19",
    "certificate_issued": true
  },
  {
    "enrollment_id": "ENR2024002",
    "participant_id": "MGS2023002",
    "program_instance_id": "INST2024002",
    "enrollment_date": "2024-03-20",
    "completion_status": "Completed",
    "completion_date": "2024-04-10",
    "certificate_issued": true
  },
  {
    "enrollment_id": "ENR2024003",
    "participant_id": "MGS2023003",
    "program_instance_id": "INST2024003",
    "enrollment_date": "2024-05-01",
    "completion_status": "Completed",
    "completion_date": "2024-05-21",
    "certificate_issued": true
  }
]

5. Feedback Table

CREATE TABLE feedback (
    feedback_id VARCHAR(20) PRIMARY KEY,
    participant_id VARCHAR(20),
    program_instance_id VARCHAR(20),
    overall_satisfaction INTEGER CHECK (overall_satisfaction BETWEEN 1 AND 5),
    content_quality INTEGER CHECK (content_quality BETWEEN 1 AND 5),
    trainer_effectiveness INTEGER CHECK (trainer_effectiveness BETWEEN 1 AND 5),
    venue_logistics INTEGER CHECK (venue_logistics BETWEEN 1 AND 5),
    likelihood_recommend INTEGER CHECK (likelihood_recommend BETWEEN 1 AND 5),
    comments TEXT,
    feedback_date DATE,
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id),
    FOREIGN KEY (program_instance_id) REFERENCES program_instances(instance_id)
);

Sample Feedback Records

[
  {
    "feedback_id": "FB2024001",
    "participant_id": "MGS2023001",
    "program_instance_id": "INST2024001",
    "overall_satisfaction": 5,
    "content_quality": 5,
    "trainer_effectiveness": 4,
    "venue_logistics": 4,
    "likelihood_recommend": 5,
    "comments": "Excellent program that provided practical leadership tools I can immediately apply in my school. Dr. Ndlovu's expertise was evident throughout.",
    "feedback_date": "2024-03-20"
  },
  {
    "feedback_id": "FB2024002",
    "participant_id": "MGS2023002",
    "program_instance_id": "INST2024002",
    "overall_satisfaction": 4,
    "content_quality": 4,
    "trainer_effectiveness": 5,
    "venue_logistics": 3,
    "likelihood_recommend": 4,
    "comments": "Very informative content on governance responsibilities. Prof. Seboko explained complex legal requirements clearly. Venue could be improved.",
    "feedback_date": "2024-04-11"
  },
  {
    "feedback_id": "FB2024003",
    "participant_id": "MGS2023003",
    "program_instance_id": "INST2024003",
    "overall_satisfaction": 4,
    "content_quality": 4,
    "trainer_effectiveness": 4,
    "venue_logistics": 5,
    "likelihood_recommend": 4,
    "comments": "Great introduction to digital tools. Online format worked well. Would appreciate more hands-on practice time.",
    "feedback_date": "2024-05-22"
  }
]

6. Career Updates Table

CREATE TABLE career_updates (
    update_id VARCHAR(20) PRIMARY KEY,
    participant_id VARCHAR(20),
    position_title VARCHAR(100),
    institution_name VARCHAR(100),
    change_type ENUM('Promotion', 'Job Change', 'Additional Responsibility', 'Salary Increase'),
    change_date DATE,
    update_source ENUM('Self-Reported', 'LinkedIn', 'Institution Feedback', 'Survey Response'),
    verified BOOLEAN DEFAULT FALSE,
    notes TEXT,
    FOREIGN KEY (participant_id) REFERENCES participants(participant_id)
);

Sample Career Update Records

[
  {
    "update_id": "CU2024001",
    "participant_id": "MGS2023001",
    "position_title": "Principal",
    "institution_name": "Green Valley Primary School",
    "change_type": "Promotion",
    "change_date": "2024-06-01",
    "update_source": "Self-Reported",
    "verified": true,
    "notes": "Promoted to Principal position 3 months after completing leadership program. Credits program with providing confidence and tools needed for promotion."
  },
  {
    "update_id": "CU2024002",
    "participant_id": "MGS2023002",
    "position_title": "District Education Specialist",
    "institution_name": "Gauteng Department of Education",
    "change_type": "Job Change",
    "change_date": "2024-07-15",
    "update_source": "LinkedIn",
    "verified": false,
    "notes": "Career progression from school principal to district level position in provincial education department."
  }
]

Sample Analytics Queries

1. Program Completion Rates

SELECT
    p.program_name,
    COUNT(e.enrollment_id) as total_enrollments,
    SUM(CASE WHEN e.completion_status = 'Completed' THEN 1 ELSE 0 END) as completions,
    ROUND(SUM(CASE WHEN e.completion_status = 'Completed' THEN 1 ELSE 0 END) * 100.0 / COUNT(e.enrollment_id), 2) as completion_rate
FROM programs p
JOIN program_instances pi ON p.program_id = pi.program_id
JOIN enrollments e ON pi.instance_id = e.program_instance_id
GROUP BY p.program_id, p.program_name
ORDER BY completion_rate DESC;

2. Average Satisfaction by Program

SELECT
    p.program_name,
    COUNT(f.feedback_id) as feedback_count,
    ROUND(AVG(f.overall_satisfaction), 2) as avg_satisfaction,
    ROUND(AVG(f.content_quality), 2) as avg_content_quality,
    ROUND(AVG(f.trainer_effectiveness), 2) as avg_trainer_effectiveness
FROM programs p
JOIN program_instances pi ON p.program_id = pi.program_id
JOIN feedback f ON pi.instance_id = f.program_instance_id
GROUP BY p.program_id, p.program_name
ORDER BY avg_satisfaction DESC;

3. Career Progression Analysis

SELECT
    part.province,
    COUNT(DISTINCT part.participant_id) as total_participants,
    COUNT(cu.update_id) as career_progressions,
    ROUND(COUNT(cu.update_id) * 100.0 / COUNT(DISTINCT part.participant_id), 2) as progression_rate
FROM participants part
LEFT JOIN career_updates cu ON part.participant_id = cu.participant_id
GROUP BY part.province
ORDER BY progression_rate DESC;

4. Geographic Participation Distribution

SELECT
    part.province,
    COUNT(DISTINCT part.participant_id) as unique_participants,
    COUNT(e.enrollment_id) as total_enrollments,
    ROUND(AVG(f.overall_satisfaction), 2) as avg_satisfaction
FROM participants part
JOIN enrollments e ON part.participant_id = e.participant_id
LEFT JOIN feedback f ON part.participant_id = f.participant_id
GROUP BY part.province
ORDER BY total_enrollments DESC;

Data Visualization Examples

Executive Dashboard Metrics

• Total Participants: 1,247 (↑12% from last year)
• Programs Delivered: 47 (↑8% from last year)
• Average Satisfaction: 4.3/5.0 (↑0.2 from last year)
• Career Progression Rate: 34% (within 12 months)

Regional Distribution

• Western Cape: 32% of participants
• Gauteng: 28% of participants
• KwaZulu-Natal: 18% of participants
• Eastern Cape: 12% of participants
• Other provinces: 10% of participants

Program Performance Rankings

1. Advanced School Leadership Certificate: 96% completion, 4.6/5.0 satisfaction
2. School Governance Fundamentals: 93% completion, 4.4/5.0 satisfaction
3. Digital Classroom Integration: 91% completion, 4.2/5.0 satisfaction

---

Document Version: 1.0 Last Updated: September 2025 Purpose: Prototype development guidance and demo preparation