19 lines
998 B
SQL
19 lines
998 B
SQL
CREATE TABLE IF NOT EXISTS school_cycles (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
center_application_id INT UNSIGNED NOT NULL,
|
|
season VARCHAR(20) NOT NULL,
|
|
year SMALLINT UNSIGNED NOT NULL,
|
|
cycle_name VARCHAR(80) NOT NULL,
|
|
start_date DATE NOT NULL,
|
|
end_date DATE NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'upcoming',
|
|
archived_at DATETIME NULL,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uniq_school_cycle_center_season_year (center_application_id, season, year),
|
|
INDEX idx_school_cycles_center (center_application_id),
|
|
INDEX idx_school_cycles_status (status),
|
|
INDEX idx_school_cycles_dates (start_date, end_date),
|
|
CONSTRAINT fk_school_cycles_center_application FOREIGN KEY (center_application_id) REFERENCES center_applications(id) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|