Course scheduler and planner

Purpose

The course scheduler is used to select courses for a term. You can add any course sections you want and it will show the time slots they take up during the week. If courses conflict, they will be shrunk to fit side-by-side, which makes it easy to identify conflicts.

The course planner is used to select courses to take over multiple years and terms. It lists the courses offered during a sample year, with the terms they were offered. A course can be added to any year during a term when it is offered. Total unit counts are displayed for each term.

Both tools can be used without an account, but when logged in, they automatically save the selected courses and sections to the user's account.

Comparison to legacy Donut

The course scheduler and planner were obviously inspired by those tools on legacy Donut. However, we added some new requested features:

• We don't delete courses from past terms, so users can keep their old schedules (and old courses they've added to their planner)
• Users can add "placeholder courses" to their planners to reflect courses that weren't offered in the past year, or to reserve units for an unknown course
• Users can see their progress towards core requirements based on their planner courses (for future Devteam's sake, I really hope these requirements don't change)
• Searching for courses happens on the client by filtering the full list of courses. We use a scoring mechanism so courses are ordered by how well they match the search.
• We show locations and grading schemes of sections in the scheduler (since the Registrar already gives us this)

The main change internally is that the scheduler and planner share the same courses data. On legacy Donut, the two tools were developed separately, so they used completely separate tables (generated by separate import scripts). Now we have courses and sections tables with all the course/section data needed for both the scheduler and planner. A single courses/utils/import_registrar.py script populates these tables.

SQL tables

In courses.sql:

courses: all current and historical courses

Column	Type	Comments
course_id	INT	PK
year	YEAR	The year this course was offered (e.g. 2020)
term	TINYINT	FA: 1, WI: 2, SP: 3
department	VARCHAR(30)	e.g. CS/IDS
course_number	VARCHAR(10)	e.g. 150a
name	VARCHAR(150)	e.g Probability and Algorithms
description	TEXT	Optional. We don't currently get this from the Registrar, so we can't display it.
units_lecture	FLOAT	Blame Psy and SS departments for offering courses with units 1.5-0-1.5
units_lab	FLOAT
units_homework	FLOAT
units	FLOAT	Computed automatically from units_lecture + units_lab + units_homework. Always seems to be an integer.

Courses can be uniquely identified by (year, term, department, course_number).

instructors: table that interns instructor names, to save on storage space

Column	Type	Comments
instructor_id	INT	PK
instructor	VARCHAR(60)	e.g. Schulman, L. Required to be unique. Some sections have 2 instructors, so needs to be long enough to store both names.

grades_types: table that interns grading types, to save on storage space

Column	Type	Comments
grades_type_id	INT	PK
grades_type	VARCHAR(30)	For now, only LETTER, PASS-FAIL, and the empty string are used. Required to be unique.

sections: sections for all courses

Column	Type	Comments
course_id	INT	References courses.course_id
section_number	TINYINT	Generally numbered 1, 2, etc.
instructor_id	INT	References instructors.instructor_id
grades_type_id	INT	References grades_types.grades_type_id
times	VARCHAR(100)	e.g. MWF 13:00 - 13:55
locations	VARCHAR(100)	e.g. 213 ANB. lol remember when courses used to meet in classrooms 😢

(course_id, section_number) is used as the primary key.

planner_courses: courses that users have added to their planners

Column	Type	Comments
user_id	INT	References members.user_id
course_id	INT	References courses.course_id
planner_year	TINYINT	Frosh: 1, Sophomore: 2, Junior: 3, Senior: 4. We may support super-senior years in the future. The term is inferred from courses.term.

(user_id, course_id, planner_year) is used as the primary key. This also prevents the same course from being added multiple times to the same year of a user's planner.

planner_placeholders: placeholder courses that users have added to their planners

Column	Type	Comments
placeholder_id	INT	PK
user_id	INT	References members.user_id
planner_year	TINYINT	Frosh: 1, Sophomore: 2, Junior: 3, Senior: 4. We may support super-senior years in the future.
term	TINYINT	FA: 1, WI: 2, SP: 3
course_name	TEXT	Placeholder text
course_units	FLOAT	Placeholder units. We don't really validate this, who knows what happens if users use negative numbers, NaN, or Infinity? 🤷

scheduler_sections: sections that users have added to their scheduler

Column	Type	Comments
user_id	INT	References members.user_id
course_id	INT	References courses.course_id and sections.course_id
section_number	TINYINT	References sections.section_number

(user_id, course_id, section_number) is used as the primary key. This also prevents the same section from being added multiple times to a user's scheduler. Note that courses.year and courses.term are used to determine which term's scheduler the section belongs to.

scheduler_notes: notes that users have added to courses

Column	Type	Comments
user_id	INT	References members.user_id
course_id	INT	References courses.course_id
notes	TEXT	Optional

(user_id, course_id) is used as the primary key.

Cascading deletes

Deleting from courses will delete corresponding sections. Deleting from courses will delete the courses from users' planners. Deleting from sections will delete the sections from users' schedulers.