Skip to content

Instantly share code, notes, and snippets.

@mikechambers
Created December 19, 2020 19:51
Show Gist options
  • Save mikechambers/e372c3fda1f95cf8d682d7f138b86c81 to your computer and use it in GitHub Desktop.
Save mikechambers/e372c3fda1f95cf8d682d7f138b86c81 to your computer and use it in GitHub Desktop.
SQL Scema for storing activity history and stats
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment