84 lines
2.7 KiB
SQL
84 lines
2.7 KiB
SQL
-- SSCTopper Database Schema
|
|
-- Complete SSC CGL Question Bank
|
|
|
|
CREATE TABLE IF NOT EXISTS subjects (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
name TEXT NOT NULL UNIQUE,
|
|
tier TEXT NOT NULL,
|
|
description TEXT,
|
|
target_questions INTEGER DEFAULT 0
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS subtopics (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
subject_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
FOREIGN KEY (subject_id) REFERENCES subjects(id),
|
|
UNIQUE(subject_id, name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS topics (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
subtopic_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
FOREIGN KEY (subtopic_id) REFERENCES subtopics(id),
|
|
UNIQUE(subtopic_id, name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS question_types (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
topic_id INTEGER NOT NULL,
|
|
name TEXT NOT NULL,
|
|
FOREIGN KEY (topic_id) REFERENCES topics(id),
|
|
UNIQUE(topic_id, name)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS questions (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
question_type_id INTEGER NOT NULL,
|
|
question_text TEXT NOT NULL,
|
|
option_a TEXT NOT NULL,
|
|
option_b TEXT NOT NULL,
|
|
option_c TEXT NOT NULL,
|
|
option_d TEXT NOT NULL,
|
|
correct_option TEXT NOT NULL CHECK(correct_option IN ('A','B','C','D')),
|
|
explanation TEXT,
|
|
difficulty INTEGER DEFAULT 1 CHECK(difficulty BETWEEN 1 AND 3),
|
|
year_appeared TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (question_type_id) REFERENCES question_types(id)
|
|
);
|
|
|
|
-- Performance indexes
|
|
CREATE INDEX IF NOT EXISTS idx_questions_type ON questions(question_type_id);
|
|
CREATE INDEX IF NOT EXISTS idx_questions_difficulty ON questions(difficulty);
|
|
CREATE INDEX IF NOT EXISTS idx_topics_subtopic ON topics(subtopic_id);
|
|
CREATE INDEX IF NOT EXISTS idx_subtopics_subject ON subtopics(subject_id);
|
|
CREATE INDEX IF NOT EXISTS idx_qtypes_topic ON question_types(topic_id);
|
|
|
|
-- User Management
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
username TEXT NOT NULL UNIQUE,
|
|
email TEXT NOT NULL UNIQUE,
|
|
password_hash TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Progress Tracking (Question level)
|
|
CREATE TABLE IF NOT EXISTS user_answers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id INTEGER NOT NULL,
|
|
question_id INTEGER NOT NULL,
|
|
is_correct BOOLEAN NOT NULL,
|
|
time_taken REAL DEFAULT 0.0,
|
|
answered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (user_id) REFERENCES users(id),
|
|
FOREIGN KEY (question_id) REFERENCES questions(id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_answers_user ON user_answers(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_answers_question ON user_answers(question_id);
|