speedrunDatabaseSchema.sql

CREATE TABLE Users (
  UserID SERIAL PRIMARY KEY,
  Email VARCHAR(255) NOT NULL,
  Phone VARCHAR(20),
  FName VARCHAR(50) NOT NULL,
  LName VARCHAR(50) NOT NULL
);

CREATE TABLE Credentials (
  Username VARCHAR(50) PRIMARY KEY,
  Password VARCHAR(50) NOT NULL,
  UserType TEXT NOT NULL,
  UserID INTEGER REFERENCES Users(UserID) ON DELETE CASCADE
);
--TopTanking should be an int that relates a RunID
CREATE TABLE Runners (
  RunnerID SERIAL PRIMARY KEY,
  NumRuns INTEGER NOT NULL,
  TopRanking INTEGER,
  UserID INTEGER REFERENCES Users(UserID) ON DELETE CASCADE
);

CREATE TABLE Moderators (
  ModID SERIAL PRIMARY KEY,
  VerifiedRuns INTEGER NOT NULL,
  RemovedRuns INTEGER NOT NULL,
  UserID INTEGER REFERENCES Users(UserID) ON DELETE CASCADE
);

CREATE TABLE Administrators (
  AdminID SERIAL PRIMARY KEY,
  NumUsersBanned INTEGER NOT NULL,
  NumPostsRemoved INTEGER NOT NULL,
  UserID INTEGER REFERENCES Users(UserID) ON DELETE CASCADE
);

CREATE TABLE BannedUsers (
  UserID INTEGER REFERENCES Users(UserID) ON DELETE CASCADE,
  AdminID INTEGER REFERENCES Administrators(AdminID) ON DELETE CASCADE,
  BanExplanation TEXT,
  BanDuration INTERVAL
);

CREATE TABLE Games (
  GameID SERIAL PRIMARY KEY,
  Name VARCHAR(50) NOT NULL,
  Genre VARCHAR(50)
);

CREATE TABLE Runs (
  RunID SERIAL PRIMARY KEY,
  Time INTERVAL NOT NULL,
  UserSubmitted VARCHAR(50) NOT NULL,
  Category VARCHAR(50) NOT NULL,
  Verified BOOLEAN NOT NULL,
  Ranking INTEGER,
  GameID INTEGER REFERENCES Games(GameID) ON DELETE CASCADE,
  UserID INTEGER REFERENCES Users(UserID) ON DELETE CASCADE
);

CREATE TABLE Comments (
  CommentID SERIAL PRIMARY KEY,
  RunID INTEGER REFERENCES Runs(RunID) ON DELETE CASCADE,
  UserID INTEGER REFERENCES Users(UserID) ON DELETE CASCADE,
  Comment TEXT
);

--RunID refers to the top run of that game
CREATE TABLE GameRuns (
  GameID INTEGER REFERENCES Games(GameID) ON DELETE CASCADE,
  RunID INTEGER REFERENCES Runs(RunID) ON DELETE CASCADE,
  TotalRuns INTEGER NOT NULL
);