Skip to content
This repository was archived by the owner on Mar 2, 2022. It is now read-only.

Data Models

Jump to bottom
Gregory Kelly edited this page Dec 9, 2021 · 1 revision

User

User

The user model represents a registered user

SQL Table

Name: users

Field

Type

Null

Key

Default

Description

id

varchar(36)

NO

PRI

uuid()

Unique ID

name

varchar(50)

NO

UNI


User's Name
email varchar(254) NO UNI
User's Email

pass

varchar(60)

NO



Salted+Hashed Password

school

varchar(36)

NO

schools(id); UC/DR


User's school

Creation Statement  Expand source

CREATE TABLE `users` (
    `id`      VARCHAR(36) PRIMARY KEY DEFAULT (uuid())
                COMMENT 'Unique ID',
    `name`    VARCHAR(50) NOT NULL
                COMMENT 'User\'s Name',
    `email`   VARCHAR(254) NOT NULL
                COMMENT 'User\'s Email', 
    `pass`    VARCHAR(60) NOT NULL
                COMMENT 'Salted+Hashed Password',
    `school`  VARCHAR(36) NOT NULL
                COMMENT 'User\'s school',
    
    FOREIGN KEY (`school`)
                   REFERENCES `schools`(`id`)
                   ON UPDATE CASCADE ON DELETE RESTRICT
);

School

School

The school model represents a supported school

SQL Table

Name: schools

Field

Type

Null

Key

Default

Description

id

varchar(36)

NO

PRI

uuid()

Unique ID

name

varchar(64)

NO



School's Name

Creation Statement  Expand source

CREATE TABLE `schools` (
    `id`      VARCHAR(36) PRIMARY KEY DEFAULT (uuid())
                COMMENT 'Unique ID',
    `name`    VARCHAR(64) NOT NULL
                COMMENT 'School\'s Name' 
);

Course

Course

The course model represents an uploaded course

SQL Table

Name: courses

Field

Type

Null

Key

Default

Description

id

varchar(36)

NO

PRI

uuid()

Unique ID
name varchar(64) NO

Course name
code varchar(16) NO

Course code
year year NO
now() Academic year
term enum('Wint', 'Spri', 'Summ', 'Fall') NO

Course term
prof varchar(32) NO
'' Course professor

owner

varchar(36)

NO

users(id); UC/DR


User who created/maintains course information

school

varchar(36)

NO

schools(id); UC/DR


School course is at
modified datetime NO
CURRENT_TIMESTAMP Date and time of last modification, updates on changes to connected events

To prevent duplicate entries for a course by a user, the combination of code, owner, year, and term must be unique.

Creation Statement  Expand source

CREATE TABLE `courses` (
    `id`      VARCHAR(36) PRIMARY KEY DEFAULT (uuid())
                COMMENT 'Unique ID',
    `name`    VARCHAR(64) NOT NULL
                COMMENT 'Course name', 
    `code`    VARCHAR(16) NOT NULL
                COMMENT 'Course code', 
	`year`	  YEAR NOT NULL
				COMMENT 'Academic year',
	`term`    ENUM('Wint', 'Spri', 'Summ', 'Fall') NOT NULL
				COMMENT 'Course term',
    `prof`    VARCHAR(32) NOT NULL
                COMMENT 'Course professor',      
	`owner`   VARCHAR(36) NOT NULL
                COMMENT 'User who created/maintains course information',
    `school`  VARCHAR(36) NOT NULL
                COMMENT 'School course is at',
	`modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
				COMMENT 'Date and time of last modification, updates on changes to connected events', 

    -- User can only have one entry for each course, to prevent accidental duplicates
    UNIQUE (`code`, `owner`, `year`, `term`),

    FOREIGN KEY (`owner`)
                   REFERENCES `profiles`(`id`)
                   ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`school`)
                   REFERENCES `schools`(`id`)
                   ON UPDATE CASCADE ON DELETE RESTRICT
);

-- Configure updating for modified, should be run after creation of events table
CREATE TRIGGER `course_mod_ins` AFTER INSERT on `events`
	FOR EACH ROW UPDATE courses SET modified=CURRENT_TIMESTAMP WHERE `id`=NEW.`course`;
CREATE TRIGGER `course_mod_upd` AFTER UPDATE on `events`
	FOR EACH ROW UPDATE courses SET modified=CURRENT_TIMESTAMP WHERE `id`=NEW.`course`;
CREATE TRIGGER `course_mod_del` AFTER DELETE on `events`
	FOR EACH ROW UPDATE courses SET modified=CURRENT_TIMESTAMP WHERE `id`=OLD.`course`;

Subscription

Subscription

The subscription model represents a subscription to a course by a user

This table is not reflected in the application

SQL Table

Name: subscriptions

Field

Type

Null

Key

Default

Description

user

varchar(36)

NO

users(id); UC/DC


The user subscribed to the course

course

varchar(36)

NO

courses(id); UC/DR


The course the user is subscribed to

Primary key is hybrid key with user and course

Creation Statement  Expand source

CREATE TABLE `subscriptions` (
    `user`    VARCHAR(36) NOT NULL
                COMMENT 'The user subscribed to the course',
    `course`  VARCHAR(36) NOT NULL
                COMMENT 'The course the user is subscribed to',

    PRIMARY KEY (`user`, `course`),

    FOREIGN KEY (`user`)
                   REFERENCES `users`(`id`)
                   ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`course`)
                   REFERENCES `courses`(`id`)
                   ON UPDATE CASCADE ON DELETE RESTRICT
);

Event

Event

The event model represents an event or assignment

SQL Table

Name: events

Field

Type

Null

Key

Default

Description

id

varchar(36)

NO

PRI

uuid()

Unique ID

course

varchar(36)

NO

courses(id); UD/DC


Course the event is for

name

varchar(64)

NO



Name of the event

type

enum('LECTURE','TUTORIAL','LAB','TEST','QUIZ','ASSIGNMENT')

NO



Type of event

weight

int

NO


0

Grade weight in course

datetime

datetime

NO



Date/time of event

endDate

datetime

YES


NULL

Date/time of event end

weekly

boolean

NO


FALSE

If true event will be repeated weekly

Creation Statement  Expand source

CREATE TABLE `events` (
    `id`      VARCHAR(36) PRIMARY KEY DEFAULT (uuid()),
    `course`  VARCHAR(36) NOT NULL,
    `name`    VARCHAR(64) NOT NULL,
    `type`    ENUM('LECTURE', 'TUTORIAL', 'LAB', 'TEST', 'QUIZ', 'ASSIGNMENT') NOT NULL,
    `weight`  INT NOT NULL DEFAULT 0,
    `datetime` DATETIME NOT NULL,
    `endDate` datetime,
    `weekly`  BOOL NOT NULL DEFAULT FALSE, 

    FOREIGN KEY (`course`)
                    REFERENCES `courses`(`id`)
                    ON UPDATE CASCADE ON DELETE CASCADE
);

Reminder

Reminder

The reminder model represents a scheduled reminder

SQL Table

Name: reminders

Field

Type

Null

Key

Default

Description

id

varchar(36)

NO

PRI

uuid()

Unique ID

user

varchar(36)

NO

users(id); UC/DC


User to deliver reminder to

event

varchar(36)

NO

events(id); UC/DC


Event reminder is for

Creation Statement  Expand source

CREATE TABLE `reminders` (
    `id`      VARCHAR(36) PRIMARY KEY DEFAULT (uuid()),
    `user`    VARCHAR(36) NOT NULL,
    `event`   VARCHAR(36) NOT NULL,

    FOREIGN KEY (`user`)
                   REFERENCES `users`(`id`)
                   ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY (`event`)
                   REFERENCES `events`(`id`)
                   ON UPDATE CASCADE ON DELETE CASCADE
);

Todo

Todo

The todo model represents an entry in a user's To-Do list

SQL Table

Name: todo

Field

Type

Null

Key

Default

Description

id

varchar(36)

NO

PRI

uuid()

Unique ID

user

varchar(36)

NO

users(id); UC/DC


User To-Do is for

name

varchar(64)

NO



Name of the To-Do

completed

boolean

NO


FALSE

Flag indicating To-Do completion

Creation Statement  Expand source

CREATE TABLE `todo` (
    `id`      VARCHAR(36) PRIMARY KEY DEFAULT (uuid()),
    `user`    VARCHAR(36) NOT NULL,
    `name`    VARCHAR(64) NOT NULL,
    `completed` BOOL NOT NULL DEFAULT FALSE,

    FOREIGN KEY (`user`)
                  REFERENCES `users`(`id`)
                  ON UPDATE CASCADE ON DELETE CASCADE
);

Session

Session

The session model connects a session tokento a user's account

This table is not reflected in the application

SQL Table

Name: sessions

Field

Type

Null

Key

Default

Description

token

varchar(36)

NO

PRI


Unique token to authenticate the user

user

varchar(36)

NO

users(id); UC/DC


The user token authenticates
created datetime NO
CURRENT_TIMESTAMP Date the token was created, used for expiry

Creation Statement  Expand source

CREATE TABLE `sessions` (
    `token`  VARCHAR(36) PRIMARY KEY
                COMMENT 'Unique token to authenticate the user', 
    `user`   VARCHAR(36) NOT NULL
                COMMENT 'The user token authenticates',
    `created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
                COMMENT 'Date the token was created, used for expiry', 

    FOREIGN KEY (`user`)
                   REFERENCES `users`(`id`)
                   ON UPDATE CASCADE ON DELETE CASCADE
);

Application

Application

Table stored in local cache with installation-specific data.

This table is not reflected in server**.**

SQL Table

Name: _application

Field

Type

Null

Key

Default

Description
lastUpdate datetime NO
CURRENT_TIMESTAMP Date/time of last update from server
newData boolean NO
false Indicates if there is new data to be uploaded
localUser varchar(36) YES users(id); UC/DR NULL UUID of the local user
token varchar(36) YES
NULL Session token used to authenticate with API

Creation Statement  Expand source

CREATE TABLE `_application` (
    `lastUpdate`	datetime DEFAULT CURRENT_TIMESTAMP,
    `localUser`		VARCHAR(36) NOT NULL,

    FOREIGN KEY (`localUser`)
                  REFERENCES `users`(`id`)
                  ON UPDATE CASCADE ON DELETE RESTRICT
);

Clone this wiki locally