Skip to content

Core Schema Design

Ten tables are provided for you. Every team shares them, and nobody changes them. But I'm open to suggestions if you find a way to improve the design.

These tables exist because six teams cannot each invent their own idea of what a person or a course is. Once course_id means the same thing in all six areas, your tables can point at it and your queries can join across it.

Core schema diagram

The source files

  • core.dbml – the full schema in DBML, which is what dbdiagram.io renders to produce the picture above
  • core-stubs.dbml – the same ten tables with primary keys only, to paste into your team's diagram so your foreign keys have something to point at
  • models.py – the SQLModel classes, which arrive in the class repository as src/planner/core/models.py

Read core.dbml alongside the diagram. Everything the picture shows is in that file, plus the column types and constraints that a picture cannot carry.

You will not need models.py until we get to SQLModel in October, but it is here now because it is what actually builds the tables. Both files describe the same ten tables; if you ever find the two disagreeing, tell me, because one of them has a bug.

What the tables hold

Table One row is
person one human known to the system
credential one person's password hash, kept apart from their other data
student one person who is pursuing a degree
faculty one person who teaches, advises, or leads a program
term one academic term, such as Fall 2026
catalog_year one edition of the undergraduate catalog
subject one course prefix, such as CS or MATH
course the stable identity of one course, independent of catalog year
login_event one login attempt, successful or not
activity_event one request made to the API

A term is numbered the way MyMadison numbers it: a leading 1, two digits for the year, then one digit for the season, where 1 is Spring, 5 is Summer, and 8 is Fall. Examples: Fall 2026 is 1268; Spring 2027 is 1271.

A catalog year is named for the fall it takes effect, so 2026 means the 2026–27 catalog.

Reminder about foreign keys

As explained in the System Description ground rules:

  1. Your tables may reference the core schema.
  2. Your tables may not reference another team's tables.

This is what lets six teams work at once without waiting on each other. If you find yourself wanting a foreign key into another area, say so in your GP1 document rather than working around it quietly.

A few details worth noticing

person is a supertype. A person is a student if a student row exists, and a faculty member if a faculty row exists, so nobody needs a column saying which they are. A person can be both, and a person can be neither.

The authorization flags live on faculty, not on person. Advising, directing a program, and administrative access are all faculty roles, so is_advisor, is_director, and is_admin sit where they apply.

There is no session table. How long someone spent in the application is worked out afterward from activity_event, by grouping one person's requests and starting a new visit whenever the gap between them gets large enough.

The title and credits columns on course hold current values, so that you can display a course without joining through a catalog year. Anything that varies by catalog year belongs to the course catalog team.