-- Add the historical national/provincial road adjustment table.
|
-- Run against the traffic_audit database on upgrade.
|
CREATE TABLE IF NOT EXISTS holiday_vehicle_adjustment (
|
id BIGINT NOT NULL AUTO_INCREMENT,
|
year INT NOT NULL COMMENT 'year',
|
flow_date DATE NOT NULL COMMENT 'holiday date',
|
adjusted_value DECIMAL(30,15) NOT NULL COMMENT 'adjusted national/provincial vehicle flow (10k vehicle trips)',
|
reason VARCHAR(500) NOT NULL COMMENT 'adjustment reason',
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
PRIMARY KEY (id),
|
UNIQUE KEY uk_holiday_vehicle_adjustment (year, flow_date),
|
KEY idx_holiday_vehicle_adjustment_date (flow_date)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='historical national/provincial vehicle flow adjustments';
|
