skills_assessment_schema

skills_assessment_schema.sql

-- skills_assessment_schema.sql
-- Use the database
--CREATE DATABASE IF NOT EXISTS skills_assessment;
--USE skills_assessment;

-- Table for storing local user information
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    idUserInstitution VARCHAR(255) UNIQUE, -- Assuming this is unique per institution
    idUser VARCHAR(255) UNIQUE, -- Assuming this is globally unique
    username VARCHAR(255) UNIQUE,
    studentref VARCHAR(255),
    email VARCHAR(255),
    firstname VARCHAR(255),
    lastname VARCHAR(255),
    address1 VARCHAR(255),
    address2 VARCHAR(255),
    address3 VARCHAR(255),
    address4 VARCHAR(255),
    postcode VARCHAR(50),
    city VARCHAR(100),
    country VARCHAR(100),
    phone1 VARCHAR(50),
    phone2 VARCHAR(50),
    dateofbirthiso DATE,
    ninumber VARCHAR(50),
    gender VARCHAR(10),
    lastaccess DATETIME,
    roleTitle VARCHAR(100),
    hidden TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Table for logging API requests and responses
CREATE TABLE IF NOT EXISTS api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    endpoint VARCHAR(255),
    request_data JSON, -- Use JSON type if available (MySQL 5.7+), otherwise TEXT
    response_data JSON, -- Use JSON type if available, otherwise TEXT
    status_code INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table for storing user group memberships
CREATE TABLE IF NOT EXISTS user_groups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL, -- Foreign key to the local users table
    ncfe_group_id VARCHAR(255), -- The group ID from NCFE (used in assignUserToGroupID)
    ncfe_group_name VARCHAR(255), -- The group name from NCFE (used in assignUserToGroup)
    ncfe_role_id VARCHAR(255) NOT NULL, -- The role ID (1=Learner, 2=Tutor, etc.)
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Ensure a user is only assigned to a specific group (by ID or Name) once with a role
    -- Using group_id for uniqueness if available, otherwise group_name
    -- Note: API allows assigning by ID or Name, responses give ID or Name back.
    -- Storing both and using a combined unique key might be safest if both are used.
    -- Assuming group_id is the primary identifier if known.
    UNIQUE KEY uk_user_group (user_id, ncfe_group_id),
    -- If group_id is not always available when assigning by name, you might need:
    -- UNIQUE KEY uk_user_group_name (user_id, ncfe_group_name),
    -- Or handle this logic in your application layer.
    -- Let's stick to group_id for uniqueness if possible.

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table for storing assessment records (Screening, IA, Diagnostic)
CREATE TABLE IF NOT EXISTS assessments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL, -- Foreign key to the local users table
    ncfe_id_assessment_user VARCHAR(255) UNIQUE NOT NULL, -- Unique ID for this user's assessment attempt
    ncfe_id_user_diagnostic VARCHAR(255), -- ID grouping attempts for the same user/diagnostic type
    assessment_title VARCHAR(255),
    level VARCHAR(100),
    assessment_area VARCHAR(100),
    start_dt DATETIME,
    finish_dt DATETIME,
    time_taken BIGINT,
    subject_title VARCHAR(100),
    assessment_type VARCHAR(100),
    percentage DECIMAL(5,2),
    course VARCHAR(255),
    diagnostic VARCHAR(255),
    assessment VARCHAR(255),
    raw_response_data JSON, -- Use JSON type if available, otherwise TEXT
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_user_id (user_id),
    INDEX idx_ncfe_id_assessment_user (ncfe_id_assessment_user),
    INDEX idx_assessment_type (assessment_type),

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table for storing assessment section results
CREATE TABLE IF NOT EXISTS assessment_sections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    assessment_id INT NOT NULL, -- Foreign key to the local assessments table
    ncfe_id_assessment_user_section VARCHAR(255) UNIQUE NOT NULL, -- Unique ID for this section result
    ncfe_id_assessment_user VARCHAR(255), -- Reference to the parent assessment NCFE ID (not a FK)
    section_name VARCHAR(255),
    percentage DECIMAL(5,2),
    level VARCHAR(100),
    date DATETIME,
    ncfe_id_user_diagnostic VARCHAR(255),
    ncfe_id_user_inst VARCHAR(255),
    course VARCHAR(255),
    diagnostic VARCHAR(255),
    assessment VARCHAR(255),
    raw_response_data JSON, -- Use JSON type if available, otherwise TEXT
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_assessment_id (assessment_id),
    INDEX idx_ncfe_id_assessment_user_section (ncfe_id_assessment_user_section),

    FOREIGN KEY (assessment_id) REFERENCES assessments(id) ON DELETE CASCADE
);

-- Table for storing ISP Topic Access Dates
CREATE TABLE IF NOT EXISTS user_isp_topic_access (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL, -- Foreign key to the local users table
    ncfe_id_user_inst VARCHAR(255), -- NCFE's idUserInst from the response
    module_name VARCHAR(255),
    topic_name VARCHAR(255),
    first_access DATE,
    last_access DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uk_user_module_topic (user_id, module_name, topic_name),

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Table for storing assigned Units and Modules
CREATE TABLE IF NOT EXISTS user_assigned_units_modules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL, -- Foreign key to the local users table
    ncfe_unit_id VARCHAR(255),
    ncfe_module_id VARCHAR(255),
    ncfe_diag_id VARCHAR(255),
    progress_status INT,
    assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_user_id (user_id),
    INDEX idx_ncfe_unit_id (ncfe_unit_id),
    INDEX idx_ncfe_module_id (ncfe_module_id),
    INDEX idx_ncfe_diag_id (ncfe_diag_id),

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Add indexes to the 'users' table for common lookup fields
ALTER TABLE users ADD INDEX idx_username (username);
ALTER TABLE users ADD INDEX idx_email (email);
ALTER TABLE users ADD INDEX idx_studentref (studentref);
ALTER TABLE users ADD INDEX idx_ninumber (ninumber);
ALTER TABLE users ADD INDEX idx_iduserinstitution (idUserInstitution);
ALTER TABLE users ADD INDEX idx_iduser (idUser);