diff options
Diffstat (limited to '')
-rw-r--r-- | sql/schema.sql | 34 |
1 files changed, 22 insertions, 12 deletions
diff --git a/sql/schema.sql b/sql/schema.sql index 7514464..ecf31a2 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -1,24 +1,34 @@ +CREATE TABLE ctypes ( + name TEXT PRIMARY KEY NOT NULL +); +CREATE TABLE cgroups ( + name TEXT PRIMARY KEY NOT NULL +); CREATE TABLE courses ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, nmax INTEGER NOT NULL, - title TEXT, - ctype TEXT, - teacher TEXT, - location TEXT + title TEXT NOT NULL, + teacher TEXT NOT NULL, + location TEXT NOT NULL, + ctype TEXT NOT NULL, + FOREIGN KEY(ctype) REFERENCES ctypes(name), + cgroup TEXT NOT NULL, + FOREIGN KEY(cgroup) REFERENCES cgroups(name) ); CREATE TABLE users ( - id TEXT PRIMARY KEY NOT NULL, - name TEXT, - email TEXT, - department TEXT, + id TEXT PRIMARY KEY NOT NULL, -- should be UUID + name TEXT NOT NULL, + email TEXT NOT NULL, + department TEXT NOT NULL, session TEXT, - expr BIGINT + expr BIGINT -- seconds ); CREATE TABLE choices ( PRIMARY KEY (userid, courseid), seltime BIGINT NOT NULL, -- microseconds - userid TEXT NOT NULL, - courseid INTEGER NOT NULL, + userid TEXT NOT NULL, -- should be UUID FOREIGN KEY(userid) REFERENCES users(id), - FOREIGN KEY(courseid) REFERENCES courses(id) + courseid INTEGER NOT NULL, + FOREIGN KEY(courseid) REFERENCES courses(id), + UNIQUE (userid, courseid) ); |