createSchema.sql

CREATE TABLE IF NOT EXISTS assignedTasks ( 
	id INTEGER PRIMARY KEY, 
	userId INTEGER NOT NULL, 
	assignedTaskId INTEGER NOT NULL, 
	CONSTRAINT userId_fkey FOREIGN KEY (userId) REFERENCES users (userId), 
	CONSTRAINT taskId_fkey FOREIGN KEY (assignedTaskId) REFERENCES tasks (taskId)
);

CREATE TABLE IF NOT EXISTS tasks (
	taskId INTEGER PRIMARY KEY,
	taskName TEXT,
	question TEXT,
	taskType INTEGER
);

CREATE TABLE IF NOT EXISTS users (
	userId INTEGER PRIMARY KEY,
	screenname TEXT UNIQUE NOT NULL,
	password TEXT NOT NULL,
	fname TEXT,
	lname TEXT,
	isadmin BIT
);

CREATE TABLE IF NOT EXISTS elements (
	elementId INTEGER PRIMARY KEY,
	elementText TEXT NOT NULL,
	taskId INTEGER NOT NULL,
	externalId TEXT
);

CREATE TABLE IF NOT EXISTS labels (
	labelId INTEGER PRIMARY KEY,
	labelText TEXT NOT NULL,
	taskId INTEGER NOT NULL,
	parentLabel INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS elementLabels (
	elementLabelId INTEGER PRIMARY KEY,
	elementId INTEGER NOT NULL,
	labelId INTEGER NOT NULL,
	userId INTEGER NOT NULL,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

CREATE TABLE IF NOT EXISTS pairs (
	pairId INTEGER PRIMARY KEY,
	taskId INTEGER NOT NULL,
	leftElement INTEGER NOT NULL,
	rightElement INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS comparisons (
	compareId INTEGER PRIMARY KEY,
	pairId INTEGER NOT NULL,
	userId INTEGER NOT NULL,
	decision INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS rangeQuestions(
	rangeQuestionId INTEGER PRIMARY KEY,
	rangeQuestion TEXT NOT NULL,
	taskId INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS rangeScales(
	rangeScaleId INTEGER PRIMARY KEY,
	rangeQuestionId INTEGER NOT NULL,
	rangeValue TEXT NOT NULL,
	rangeOrder INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS rangeDecisions(
	rangeDecisionId INTEGER PRIMARY KEY,
	elementId INTEGER NOT NULL,
	rangeQuestionId INTEGER NOT NULL,
	rangeScaleId INTEGER NOT NULL,
	userId INTEGER NOT NULL,
    time TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);