database schema for mcqs type exam in sql
CREATE TABLE users (
id int(10) auto_increment primary key,
username VARCHAR(45) NOT NULL ,
password VARCHAR(45) NOT NULL ,
enabled TINYINT NOT NULL DEFAULT 1
);
CREATE TABLE questions (
id int(10) auto_increment primary key,
question varchar(800) NOT NULL,
right_option int(10) NOT NULL references options(id)
);
CREATE TABLE options (
id int(10) auto_increment primary key,
question_id int(10) NOT NULL references questions(id),
`option` varchar(150) NOT NULL
);
CREATE TABLE exam_details (
id int(10) auto_increment primary key,
username varchar(45) NOT NULL references users(username),
date_of_exam date not null,
exam_result varchar(10) NOT NULL, -- PASS/FAIL
exam_score int(10) NOT NULL, -- e.g. 40
no_of_questions int(10) NOT NULL -- total no. of questions in the test
);
CREATE TABLE user_answers (
id int(10) auto_increment primary key,
userId int(10) NOT NULL references users(id),
question_id int(10) NOT NULL references questions(id),
answer int(10) NOT NULL references options(id)
);