TestPhy App - Database & API
Architecture
1. Overview
TestPhy is an online test preparation platform supporting multiple
competitive exams (EAMCET, JEE, NEET, CAT, TOEFL, IELTS). The
mobile and web applications interact with Azure SQL Database and
Azure Blob Storage, using Azure Functions as the backend.
2. Database Design
Tables & Schema
1. Users Table
Stores user details.
CREATE TABLE Users (
user_id INT IDENTITY(1,1) PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
phone_number VARCHAR(20) UNIQUE NULL,
password_hash VARCHAR(255) NOT NULL,
profile_pic VARCHAR(500) NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
2. Subjects & Topics
CREATE TABLE Subjects (
subject_id INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(100) UNIQUE NOT NULL
);
CREATE TABLE Topics (
topic_id INT IDENTITY(1,1) PRIMARY KEY,
subject_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
FOREIGN KEY (subject_id) REFERENCES Subjects(subject_id) ON
DELETE CASCADE
);
3. Quiz & Questions
CREATE TABLE Quizzes (
quiz_id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
topic_id INT NOT NULL,
score INT DEFAULT 0,
completed BIT DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id),
FOREIGN KEY (topic_id) REFERENCES Topics(topic_id)
);
CREATE TABLE QuizQuestions (
question_id INT IDENTITY(1,1) PRIMARY KEY,
topic_id INT NOT NULL,
question_url VARCHAR(500) NOT NULL,
answer VARCHAR(255) NOT NULL,
options JSON NOT NULL,
FOREIGN KEY (topic_id) REFERENCES Topics(topic_id) ON DELETE
CASCADE
);
4. User Responses & Progress
CREATE TABLE UserResponses (
response_id INT IDENTITY(1,1) PRIMARY KEY,
quiz_id INT NOT NULL,
question_id INT NOT NULL,
user_answer VARCHAR(255) NOT NULL,
is_correct BIT NOT NULL,
answered_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (quiz_id) REFERENCES Quizzes(quiz_id),
FOREIGN KEY (question_id) REFERENCES
QuizQuestions(question_id)
);
5. Mock Tests
CREATE TABLE MockTests (
test_id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
test_url VARCHAR(500) NOT NULL,
score INT DEFAULT 0,
completed BIT DEFAULT 0,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
6. Gamification (Streaks & XP)
CREATE TABLE UserStreaks (
user_id INT PRIMARY KEY,
current_streak INT DEFAULT 0,
max_streak INT DEFAULT 0,
xp_points INT DEFAULT 0,
last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
CREATE TABLE Achievements (
achievement_id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
name VARCHAR(100) NOT NULL,
description VARCHAR(255) NOT NULL,
earned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
7. Leaderboard
CREATE TABLE Leaderboard (
leaderboard_id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT NOT NULL,
total_xp INT DEFAULT 0,
rank INT DEFAULT NULL,
period VARCHAR(50) NOT NULL,
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES Users(user_id)
);
3. API Endpoints (Azure Functions)
1. Authentication APIs
● POST /auth/register - Register a new user
● POST /auth/login - Authenticate user
● POST /auth/logout - Logout user
● POST /auth/forgot-password - Request password reset
● PUT /auth/update-profile - Update user profile
2. Subject & Topic APIs
● GET /subjects - Fetch all subjects
● GET /subjects/{subjectId} - Get details of a subject
● GET /subjects/{subjectId}/topics - Get topics under a
subject
● GET /topics/{topicId} - Get topic details
3. Quiz APIs (Quick Test & Chapter-wise)
● POST /quiz/start - Start a quiz
● GET /quiz/{quizId}/question - Fetch next question
● POST /quiz/{quizId}/answer - Submit an answer
● GET /quiz/{quizId}/result - Fetch quiz result
● GET /quiz/history - Get user’s past quiz attempts
4. Mock Test APIs
● POST /mocktest/start - Start a full-length mock test
● GET /mocktest/{testId}/question - Fetch next question
● POST /mocktest/{testId}/answer - Submit an answer
● POST /mocktest/{testId}/pause - Save test progress
● GET /mocktest/{testId}/resume - Resume an incomplete test
● GET /mocktest/{testId}/result - Get test results
5. Gamification APIs
● GET /streaks - Get user’s current streak
● POST /streaks/update - Update streak
● GET /xp - Fetch user’s XP points
● POST /xp/update - Update XP
● GET /achievements - Fetch user achievements
6. Leaderboard & Competitions APIs
● GET /leaderboard - Fetch top 10 players
● GET /competitions - Get live competitions
● POST /competitions/join - Join a competition
● GET /competitions/{id}/result - Get competition results
7. Notifications & Rewards APIs
● GET /notifications - Fetch notifications
● POST /notifications/mark-read - Mark notifications as read
● GET /rewards - Fetch available rewards
● POST /rewards/claim - Claim a reward
4. Azure Services Used
● Azure SQL Database (Relational Data Storage)
● Azure Blob Storage (Stores Questions & Mock Test Data)
● Azure Functions (API Backend)
● Azure App Service (Hosting Web & Mobile App)
● Azure Notification Hub (Push Notifications)
5. Summary & Next Steps
✅ Database tables are optimized for quizzes, mock tests, and
gamification ✅ Azure Blob Storage stores large test files, reducing SQL
load ✅ Azure Functions expose APIs for mobile & web applications ✅
Gamification boosts engagement via streaks, XP, and leaderboards
Next Steps:
● Develop and deploy Azure Functions for API endpoints
● Implement frontend for mobile & web apps
● Optimize database indexing for performance
🚀 This document serves as a blueprint for TestPhy’s backend
development. Let me know if you need changes!
