SQL Scema for storing activity history and stats

dclias.sql

BEGIN TRANSACTION;

/* found activities we havent synced details from yet */
CREATE TABLE IF NOT EXISTS 'main'.'activity_id_queue' (
	'activity_id'	INTEGER NOT NULL,
	'character_id'	TEXT NOT NULL,
	PRIMARY KEY("character_id", "activity_id"),

	FOREIGN KEY (character_id)
       REFERENCES character (character_id)
	   ON DELETE CASCADE

);

/* member / player */
CREATE TABLE IF NOT EXISTS 'main'.'member' (
	'member_id'	INTEGER NOT NULL,
	'platform_id'	INTEGER NOT NULL,
	PRIMARY KEY("member_id")
);

/* character */
CREATE TABLE IF NOT EXISTS 'main'.'character' (
	'character_id'	INTEGER NOT NULL,
	'member_id'	INTEGER NOT NULL,
	PRIMARY KEY("character_id"),
	FOREIGN KEY (member_id)
       REFERENCES member (member_id)
	   ON DELETE CASCADE
);

/* activity / match (doesnt have all fields yet */
CREATE TABLE IF NOT EXISTS 'main'.'activity' (
	'activity_id'	INTEGER NOT NULL,
	PRIMARY KEY("activity_id")
);

/* character stats for a specific activity */
CREATE TABLE IF NOT EXISTS 'main'.'character_activity_result' (
	'character_id'	INTEGER NOT NULL,
	'activity_id'	INTEGER NOT NULL,

	FOREIGN KEY (activity_id)
       REFERENCES activity (activity_id)
	   ON DELETE CASCADE,

	PRIMARY KEY("character_id","activity_id")
);
COMMIT;