Skip to content

Latest commit

 

History

History
38 lines (24 loc) · 1.58 KB

database.md

File metadata and controls

38 lines (24 loc) · 1.58 KB
Raw

Tables

Current state of database

Users

CREATE TABLE users (id SERIAL PRIMARY KEY, name VARCHAR(25), password VARCHAR(26), email VARCHAR(255), permission_id INT, created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp(), modifed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp());

Permission

CREATE TABLE permissions (id SERIAL PRIMARY KEY, user_id INTEGER NOT NULL, permission INTEGER NOT NULL, checkpoint_id INTEGER, created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp(), modifed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp());

Checkpoint

CREATE TABLE checkpoints (id SERIAL PRIMARY KEY, name VARCHAR(50), description VARCHAR(1000), canBeVisible boolean NOT NULL, location_id INTEGER, created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp(), modifed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp());

Point

CREATE TABLE points (id SERIAL PRIMARY KEY, checkpoint_id INTEGER NOT NULL, team_id INTEGER NOT NULL, point_amount INTEGER NOT NULL, created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp(), modifed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp());

Team

CREATE TABLE teams (id SERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL, created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp(), modifed_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT clock_timestamp());

Possible future developments

  • location: id, locationData, checkpoint_id, created, modifed (Possible improvement with PostGIS)