
CREATE TABLE testtypes (
	testtype_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	testtype_name VARCHAR(64),
	testtype_desc VARCHAR(64)
);

CREATE TABLE tests (
	test_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	test_name VARCHAR(64),
	testtype_id INTEGER,
	level_id INTEGER
);

CREATE TABLE test_levels (
	test_level_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	test_level_name VARCHAR(8)
);

CREATE TABLE scoretypes (
	scoretype_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	scoretype_name VARCHAR(32),
	scoretype_conv VARCHAR(128),
	scoretype_sqltype VARCHAR(16),
	scoretype_derived INTEGER
);

CREATE TABLE scores_INTEGER (
	score_INTEGER_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	score_id INTEGER,
	score INTEGER
);

CREATE TABLE scores_VARCHAR32 (
	score_VARCHAR32_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	score_id INTEGER,
	score VARCHAR(32)
);

CREATE TABLE scores (
	score_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	scoretype_id INTEGER,
	result_id INTEGER
);

CREATE TABLE results (
	result_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	ppl_id INTEGER,
	test_id INTEGER,
	timestamp INTEGER,
	UNIQUE ( ppl_id, test_id )
);

CREATE TABLE testtype2scoretype (
	testtype2scoretype_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	testtype_id INTEGER,
	scoretype_id INTEGER,
	ordering INTEGER
);

CREATE TABLE resultcomment (
	resultcomment_id INTEGER PRIMARY KEY AUTO_INCREMENT,
	resultcomment TEXT,
	result_id INTEGER,
	ppl_id INTEGER,
	timestamp INTEGER,
	agreement INTEGER
);



