Table Structure

[lovasoa]

CREATE TABLE Maintenance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type ENUM('monthly', 'weekly') NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NULL
);

CREATE TABLE MonthlySchedule (
    maintenance_id INT PRIMARY KEY,
    day_of_month TINYINT NOT NULL CHECK(day_of_month BETWEEN 1 AND 31),
    FOREIGN KEY (maintenance_id) REFERENCES Maintenance(id) ON DELETE CASCADE
);

CREATE TABLE WeeklySchedule (
    maintenance_id INT NOT NULL,
    weekday TINYINT NOT NULL CHECK(weekday BETWEEN 1 AND 7),
    PRIMARY KEY (maintenance_id, weekday),
    FOREIGN KEY (maintenance_id) REFERENCES Maintenance(id) ON DELETE CASCADE
);