I'm desigining a database for a university testing tool – running tests in the cloud, very similar to a CI system.
It needs to store users, courses, enrolments, assessments, submissions & results.
It's not a system powering the entire institution, but it is important for a few different courses. I'd like the database to be well-structured, extensible, and "intuitive" for future maintainers.
- Two distinct courses may have the same name (but be run in different years/periods)
- A single course can only be run once per period (of which there are multiple per year)
- Assessment names must be unique within a course, but not across other courses.
- A user may be enrolled in a course as an instructor, tutor, or student, but not multiple at once. A student may become a tutor for the same course in the future.
I've included my schema below, with relevant comments where I feel they're needed.
CREATE TYPE PERIOD AS ENUM ('summer', 'first', 'autumn', 'winter', 'second', 'spring'); CREATE TYPE ASSESSMENT_TYPE AS ENUM ('assignment', 'lab'); CREATE TYPE TEST_RESULT AS ENUM ('warnings', 'errors', 'perfect'); CREATE TYPE ROLE AS ENUM ('student', 'tutor', 'convenor', 'admin'); CREATE TABLE users ( uid VARCHAR(20) PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, password BYTEA NOT NULL ); CREATE TABLE courses ( -- id is used instead of (code, period, year) to -- uniquely identify a course, since its easier. id SERIAL PRIMARY KEY, -- code is used within the university to identify a course, -- but not when it was run (a single course can be run -- multiple times, with different students/assessments/etc) code VARCHAR(20) NOT NULL, name VARCHAR(255) NOT NULL, period PERIOD NOT NULL, year INTEGER NOT NULL, -- ensure that a single course can only be run once per period. CONSTRAINT once_per_period UNIQUE (code, period, year) ); CREATE TABLE assessment ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, type ASSESSMENT_TYPE NOT NULL, comments TEXT, course_id INTEGER REFERENCES courses(id), -- assessment names must be unique within a course, -- but not across courses. CONSTRAINT unique_name_per_course UNIQUE (name, course_id) ); CREATE TABLE test_results ( id SERIAL PRIMARY KEY, result TEST_RESULT NOT NULL, warnings TEXT NOT NULL, errors TEXT NOT NULL ); CREATE TABLE submissions ( id SERIAL PRIMARY KEY, title TEXT NOT NULL, description TEXT NOT NULL, feedback TEXT NOT NULL, uid VARCHAR(20) REFERENCES users(uid), assessment_id INTEGER REFERENCES assessment(id), result_id INTEGER REFERENCES test_results(id) ); CREATE TABLE enrol ( user_uid VARCHAR(20) REFERENCES users(uid), course_id INTEGER REFERENCES courses(id), role ROLE NOT NULL, -- ensure a user cannot have multiple roles for a single course CONSTRAINT one_role_per_course_run UNIQUE (user_uid, course_id) );
I'd like some feedback on my design – what can I do better? Is there anything I missed? Any common/idiomatic design choices?
I was going to post in the code review stackexchange, but I saw a previous answer that mentioned this as a better location.