-- -----------------------------------------------------------------------------
-- Global TrainIT Learning Management System Schema
-- Designed for global integration using explicit Business Keys (BK).
-- -----------------------------------------------------------------------------
-- Table Name: Users
CREATE TABLE Users (
-- Primary Key (PK) - Technical Surrogate Key
user_id UUID PRIMARY KEY COMMENT 'Internal unique identifier for the user entity.',
-- Business Key (BK) - Stable, Global Identifier
employee_code VARCHAR(50) NOT NULL UNIQUE COMMENT 'Globally unique employee ID from HR/Identity System (Business Key).',
-- Table Name field (Implicitly the table name itself, explicitly noted here for context)
table_name AS ('Users') STORED,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
region_code CHAR(3) NOT NULL COMMENT 'Geographic region/operating unit code.',
department_id INT NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
-- Foreign Key
FOREIGN KEY (department_id) REFERENCES Departments(dept_id)
);
-- Table Name: Departments
CREATE TABLE Departments (
-- Primary Key (PK) - Technical Surrogate Key
dept_id INT PRIMARY KEY COMMENT 'Internal unique identifier for the department.',
-- Business Key (BK) - Stable, Global Identifier
dept_code VARCHAR(20) NOT NULL UNIQUE COMMENT 'Globally unique code for the organizational department (Business Key).',
-- Table Name field
table_name AS ('Departments') STORED,
dept_name VARCHAR(100) NOT NULL,
region_code CHAR(3) NOT NULL
);
-- Table Name: Roles
CREATE TABLE Roles (
-- Primary Key (PK) - Technical Surrogate Key
role_id INT PRIMARY KEY COMMENT 'Internal unique identifier for the role.',
-- Business Key (BK) - Stable, Global Identifier
role_name VARCHAR(50) NOT NULL UNIQUE COMMENT 'Globally consistent role name (e.g., "Learner", "Admin") (Business Key).',
-- Table Name field
table_name AS ('Roles') STORED,
role_description VARCHAR(255)
);
-- Table Name: UserRoles
CREATE TABLE UserRoles (
-- Primary Key (PK) - Technical Surrogate Key
user_role_id UUID PRIMARY KEY COMMENT 'Unique audit ID for the user-role linkage.',
-- Business Key (BK) - Stable, Global Identifier
user_role_ref VARCHAR(100) NOT NULL UNIQUE COMMENT 'Unique audit reference for this specific assignment (Business Key).',
-- Table Name field
table_name AS ('UserRoles') STORED,
user_id UUID NOT NULL,
role_id INT NOT NULL,
assigned_date DATE NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
-- Foreign Keys
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (role_id) REFERENCES Roles(role_id),
-- Ensure a user can only have a specific role once at a time (implicit unique constraint via BK reference if complex)
UNIQUE (user_id, role_id, is_active)
);
-- Table Name: Courses
CREATE TABLE Courses (
-- Primary Key (PK) - Technical Surrogate Key
course_id UUID PRIMARY KEY COMMENT 'Internal unique identifier for the course version.',
-- Business Key (BK) - Stable, Global Identifier
course_version_code VARCHAR(100) NOT NULL UNIQUE COMMENT 'Composite code: Curriculum ID + Version (e.g., HSE-101-V2024) (Business Key).',
-- Table Name field
table_name AS ('Courses') STORED,
course_title VARCHAR(255) NOT NULL,
duration_hours DECIMAL(5, 2),
is_mandatory BOOLEAN NOT NULL DEFAULT FALSE,
creation_date DATE NOT NULL
);
-- Table Name: Modules
CREATE TABLE Modules (
-- Primary Key (PK) - Technical Surrogate Key
module_id INT PRIMARY KEY COMMENT 'Internal unique identifier for the module.',
-- Business Key (BK) - Stable, Global Identifier
module_unique_code VARCHAR(150) NOT NULL UNIQUE COMMENT 'Unique code identifying module within its course version (e.g., HSE-101-M03) (Business Key).',
-- Table Name field
table_name AS ('Modules') STORED,
course_id UUID NOT NULL,
module_title VARCHAR(255) NOT NULL,
sequence_number INT NOT NULL,
estimated_time_min INT,
-- Foreign Key
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
-- Table Name: ContentItems
CREATE TABLE ContentItems (
-- Primary Key (PK) - Technical Surrogate Key
item_id UUID PRIMARY KEY COMMENT 'Internal unique identifier for the content item.',
-- Business Key (BK) - Stable, Global Identifier
content_asset_hash VARCHAR(255) NOT NULL UNIQUE COMMENT 'Permanent identifier for the digital asset (e.g., SHA-256 hash or repository ID) (Business Key).',
-- Table Name field
table_name AS ('ContentItems') STORED,
module_id INT NOT NULL,
content_type VARCHAR(50) NOT NULL COMMENT 'e.g., Video, SCORM, Document, Quiz.',
asset_url VARCHAR(512) NOT loss,
-- Foreign Key
FOREIGN KEY (module_id) REFERENCES Modules(module_id)
);
-- Table Name: Instructors
CREATE TABLE Instructors (
-- Primary Key (PK) - Technical Surrogate Key
instructor_id UUID PRIMARY KEY COMMENT 'Internal unique identifier for the instructor entity.',
-- Business Key (BK) - Stable, Global Identifier
instructor_auth_id VARCHAR(50) NOT NULL UNIQUE COMMENT 'Stable, verified authentication ID for the instructor (Business Key).',
-- Table Name field
table_name AS ('Instructors') STORED,
user_id UUID NOT NULL UNIQUE COMMENT 'Links to the internal Users table.',
qualification_level VARCHAR(50),
is_certified BOOLEAN NOT NULL DEFAULT FALSE,
-- Foreign Key
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
-- Table Name: Enrollments
CREATE TABLE Enrollments (
-- Primary Key (PK) - Technical Surrogate Key
enrollment_id UUID PRIMARY KEY COMMENT 'Internal unique identifier for the enrollment record.',
-- Business Key (BK) - Stable, Global Identifier
enrollment_number VARCHAR(100) NOT NULL UNIQUE COMMENT 'Globally sequential, auditable reference number (Business Key).',
-- Table Name field
table_name AS ('Enrollments') STORED,
user_id UUID NOT NULL,
course_id UUID NOT NULL,
enrollment_date TIMESTAMP NOT NULL,
status VARCHAR(20) NOT NULL COMMENT 'e.g., IN_PROGRESS, COMPLETED, DROPPED.',
completion_date TIMESTAMP,
-- Foreign Keys
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (course_id) REFERENCES Courses(course_id),
-- Constraint: A user can only enroll in a specific course once (enforcement depends on business rule)
UNIQUE (user_id, course_id)
);
-- Table Name: UserProgress
CREATE TABLE UserProgress (
-- Primary Key (PK) - Technical Surrogate Key
progress_id INT PRIMARY KEY COMMENT 'Internal unique identifier for the progress record.',
-- Business Key (BK) - Stable, Global Identifier
progress_record_id VARCHAR(100) NOT NULL UNIQUE COMMENT 'Stable reference ID for a specific completion or activity event (Business Key).',
-- Table Name field
table_name AS ('UserProgress') STORED,
enrollment_id UUID NOT NULL,
module_id INT NOT NULL,
progress_percent INT NOT NULL DEFAULT 0,
is_completed BOOLEAN NOT NULL DEFAULT FALSE,
last_accessed TIMESTAMP,
-- Foreign Keys
FOREIGN KEY (enrollment_id) REFERENCES Enrollments(enrollment_id),
FOREIGN KEY (module_id) REFERENCES Modules(module_id)
);
-- Table Name: Assessments
CREATE TABLE Assessments (
-- Primary Key (PK) - Technical Surrogate Key
assessment_id INT PRIMARY KEY COMMENT 'Internal unique identifier for the assessment.',
-- Business Key (BK) - Stable, Global Identifier
assessment_code VARCHAR(50) NOT NULL UNIQUE COMMENT 'Standardized code for the test (e.g., FINAL_EXAM_A) (Business Key).',
-- Table Name field
table_name AS ('Assessments') STORED,
course_id UUID NOT NULL,
assessment_name VARCHAR(100) NOT NULL,
passing_score DECIMAL(5, 2) NOT NULL,
-- Foreign Key
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);
-- Table Name: AssessmentResults
CREATE TABLE AssessmentResults (
-- Primary Key (PK) - Technical Surrogate Key
result_id UUID PRIMARY KEY COMMENT 'Internal unique identifier for the assessment result.',
-- Business Key (BK) - Stable, Global Identifier
result_transaction_ref VARCHAR(150) NOT NULL UNIQUE COMMENT 'Unique reference for the graded attempt, linking to audit records (Business Key).',
-- Table Name field
table_name AS ('AssessmentResults') STORED,
enrollment_id UUID NOT NULL,
assessment_id INT NOT NULL,
score DECIMAL(5, 2) NOT NULL,
is_passed BOOLEAN NOT NULL,
completion_timestamp TIMESTAMP NOT NULL,
-- Foreign Keys
FOREIGN KEY (enrollment_id) REFERENCES Enrollments(enrollment_id),
FOREIGN KEY (assessment_id) REFERENCES Assessments(assessment_id),
-- Constraint: An enrollment/assessment combination should be unique (assuming one final attempt or tracking attempts elsewhere)
UNIQUE (enrollment_id, assessment_id)
);
-- Table Name: Certifications
CREATE TABLE Certifications (
-- Primary Key (PK) - Technical Surrogate Key
cert_id INT PRIMARY KEY COMMENT 'Internal unique identifier for the certification standard.',
-- Business Key (BK) - Stable, Global Identifier
cert_standard_code VARCHAR(50) NOT NULL UNIQUE COMMENT 'Standardized code for the qualification (e.g., ISO-9001) (Business Key).',
-- Table Name field
table_name AS ('Certifications') STORED,
cert_name VARCHAR(100) NOT NULL,
renewal_period_months INT
);
-- Table Name: UserCertificates
CREATE TABLE UserCertificates (
-- Primary Key (PK) - Technical Surrogate Key
user_cert_id UUID PRIMARY KEY COMMENT 'Internal unique identifier for the user's certificate record.',
-- Business Key (BK) - Stable, Global Identifier
certificate_serial_number VARCHAR(100) NOT NULL UNIQUE COMMENT 'The unique, legal serial number printed on the certificate document (Business Key).',
-- Table Name field
table_name AS ('UserCertificates') STORED,
user_id UUID NOT NULL,
cert_id INT NOT NULL,
issue_date DATE NOT NULL,
expiry_date DATE,
-- Foreign Keys
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (cert_id) REFERENCES Certifications(cert_id)
);
-- Table Name: ComplianceRules
CREATE TABLE ComplianceRules (
-- Primary Key (PK) - Technical Surrogate Key
rule_id INT PRIMARY KEY COMMENT 'Internal unique identifier for the compliance rule.',
-- Business Key (BK) - Stable, Global Identifier
compliance_rule_code VARCHAR(100) NOT NULL UNIQUE COMMENT 'Code identifying the regulatory requirement (e.g., REG-US-OSHA) (Business Key).',
-- Table Name field
table_name AS ('ComplianceRules') STORED,
jurisdiction VARCHAR(50) NOT NULL,
description TEXT,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);
