votes.sql

create table referendum
(
	id INTEGER not null
		constraint referendum_pk
			primary key autoincrement,
	name text
);

create table questions
(
	id integer not null
		constraint questions_pk
			primary key autoincrement,
	body text not null,
	referendum_id int not null
		references referendum
);

create unique index questions_id_uindex
	on questions (id);

create unique index referendum_id_uindex
	on referendum (id);

create table voter
(
	id INTEGER not null
		constraint voter_pk
			primary key autoincrement
);

create unique index voter_id_uindex
	on voter (id);

create table votes
(
	voter_id int not null
		references voter,
	question_id int not null
		references questions,
	id integer not null
		constraint votes_pk
			primary key autoincrement,
	vote INTEGER default 0 not null,
	check (vote in (0,1))
);

create unique index vote_question__index
	on votes (voter_id, question_id);

create unique index votes_id_uindex
	on votes (id);