Recurring Event Database Shema

recurring_events.sql

CREATE TABLE events (
	id serial PRIMARY KEY,
	title varchar(255) NOT NULL,
	body text,
	start_date date NOT NULL,
	start_time time NOT NULL,
	end_date date default NULL,
	end_time time default NULL,
	recurrence_frequency integer default 1,
	recurrence varchar(10) NOT NULL default 'Never' CHECK(recurrence IN ('Never', 'Daily', 'Weekly', 'Monthly', 'Yearly')),
	/* 1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday ... 64 = Saturday */
	recurrence_days integer default 0, 
	recurrence_until date default NULL,
	status varchar(8) NOT NULL default 'Active' CHECK(status IN ('Active', 'Inactive'))
);

CREATE TABLE event_occurrences (
	event_id integer REFERENCES events(id) ON DELETE CASCADE ON UPDATE CASCADE,
	start_timestamp timestamp NOT NULL,
	end_timestamp  timestamp DEFAULT NULL,
	status varchar(10) NOT NULL default 'Active' CHECK(status IN('Active', 'Cancelled')),
	notes varchar(256) DEFAULT '',
	PRIMARY KEY(event_id, start_timestamp)
);