competitions_schema.sql

-- Previous tables before this simply included members, events, projects and the auxiliaries.
-- I'm beginning to consider migrating this database from SQLite3 to MySQL 5.x

CREATE TABLE IF NOT EXISTS member_teams {
        member_id INTEGER NOT NULL,
	team_id INTEGER NOT NULL,
	PRIMARY KEY (member_id, team_id),
	FOREIGN KEY (member_id) REFERENCES members (member_id),
	FOREIGN KEY (team_id) REFERENCES teams (team_id)
}

CREATE TABLE IF NOT EXISTS competitions {
	competition_id INTEGER NOT NULL,
	competition_name VARCHAR(100) NOT NULL,
	competition_desc TEXT NOT NULL,

  -- Boolean
	competition_recurring INTEGER NOT NULL DEFAULT 0
}

CREATE TABLE IF NOT EXISTS competition_instances {
	instance_id INTEGER NOT NULL,
	competition_id INTEGER NOT NULL,

	-- ISO 8601 Format
	instance_datetime TEXT NOT NULL,

	PRIMARY KEY (instance_id),
        FOREIGN KEY (competition_id) REFERENCES competitions (competition_id)
}

CREATE TABLE IF NOT EXISTS competition_instance_members {
	instance_id INTEGER NOT NULL,
	member_id INTEGER NOT NULL,
	member_rank INTEGER NOT NULL,

	PRIMARY KEY (instance_id, member_id),
	FOREIGN KEY (instance_id) REFERENCES locations (instance_id),
	FOREIGN KEY (member_id) REFERENCES members (member_id)
}

-- I have a feeling that I'll need a competition_members table as well

CREATE TABLE IF NOT EXISTS teams {
	team_id INTEGER NOT NULL,
	team_name VARCHAR(45) NOT NULL,

	PRIMARY KEY (team_id)
}

-- and a competition_teams table...