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)
 );