I have a
schedule_days table that is having
id, day, date columns.
id - auto increment, primary key
day - weekday - enum(weekdays) default not null
date - a specific date - default not null
The schedule is for the train time table. That will have the same routine for all weeks except for any specific date, such as any festival. Thus I have set both null as I want to set either
If the date is specified, then I can get the day from it, so it doesn't make sense to ask or force the user to set day, which may cause human error.
I may use TRIGGER but would like to know if there is any better way to do in MySQL - 8.0.* version
CREATE TABLE IF NOT EXISTS schedule_days ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, day ENUM ('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY') DEFAULT NULL, date DATE DEFAULT NULL, INDEX staff_index (day), CONSTRAINT day_day_check CHECK (`day` IS NULL + `date` IS NULL = 1) # check constraint ) ENGINE = INNODB;