Academic Suite Database Design

Source: Dev.to

The database serves as the primary foundation of Academic Suite. In an online exam system, improper database schema design can lead to serious bottlenecks, especially when hundreds to thousands of students submit answers at nearly the same time.

In this chapter, we discuss database design from a system domain perspective, focusing on how the data schema supports scalability, consistency, and observability needs of an online exam system.

2.1 Data Model Overview

Academic Suite is built with a relational approach using PostgreSQL, due to its capability to handle transactions, complex relations, and strong data consistency.

The data model is divided into four main groups:

• Organizational Models – Institutions, users, and classes
• Academic Models – Subjects, quizzes, and question banks
• Exam Models – Exam execution and student attempts
• Audit & Monitoring Models – Answers and anti‑cheating activities

This separation helps isolate responsibilities and facilitates advanced feature development.

2.2 Entity Relationship Diagram (ERD)

The diagram below illustrates the main relationships between entities in the Academic Suite system.

erDiagram
    INSTITUTION ||--|{ USER : has
    INSTITUTION ||--|{ SUBJECT : offers

    USER ||--|{ CLASS : "teaches/enrolled"
    USER ||--o{ ATTEMPT : takes

    SUBJECT ||--|{ CLASS : has
    SUBJECT ||--|{ QUIZ : contains

    QUIZ ||--|{ QUESTION : contains
    QUIZ ||--|{ EXAM_BATCH : scheduled_as

    QUESTION ||--|{ OPTION : has

    EXAM_BATCH ||--o{ ATTEMPT : records
    CLASS ||--|{ EXAM_BATCH : participates_in

    ATTEMPT ||--|{ ANSWER : contains
    ATTEMPT ||--|{ EVENT_LOG : generates

This ERD serves as the main reference throughout this chapter and subsequent chapters.

2.3 Organizational Models (User, Institution, Class)

Institution & User

Every user must be tied to a single institution, enabling multi‑tenancy for future expansion.

Table institutions

• id (PK)
• name
• created_at

Table users

• id (PK)
• email (unique)
• role (admin, teacher, student)
• institution_id (FK)

These relationships ensure data isolation between institutions without adding excessive complexity in the initial stages.

Class

The Class entity groups students and facilitates exam scheduling.

• student_ids is stored as JSON Text containing an array of student IDs.

Design Decision
Using JSON instead of a junction table (class_students) optimizes read performance during exams. Under high load, reading a single row and parsing JSON is often faster than performing a JOIN on a large junction table. This sacrifices foreign‑key constraints at the database level; consistency validation is performed in the application.

2.4 Academic Models (Subject, Quiz, Question)

Subject

Represents the subjects offered by the institution.

Quiz

Quiz functions as the exam blueprint and stores global configurations:

• time_limit
• passing_score
• exam_type

Question & Option

Each Question stores the content and type (mcq, essay, etc.). For multiple‑choice questions, answer options are stored in a separate table (question_options), providing flexibility in the number of options and supporting future question types.

2.5 Exam Models (ExamBatch & Attempt)

ExamBatch (Exam Session)

ExamBatch represents a concrete execution of a quiz.

• A single quiz can be scheduled multiple times for different classes.
• Contains a token used as the exam entry code.
• Lifecycle status: scheduled → active → finished.

Attempt (Exam Attempt)

An Attempt record is created each time a student starts an exam.

Key fields:

• status: ACTIVE, SUBMITTED
• remaining_time: snapshot of remaining time in seconds (ensures consistency after page refresh or disruption)
• score: final score

2.6 Answer & EventLog (Critical Data & Anti‑Cheating)

Answer

The answers table stores student answers for each question.

• Very large data volume
• High write intensity during the exam

Optimization: Composite index on (attempt_id, question_id) to keep queries fast when reloading the exam page.

EventLog

Suspicious activities are recorded in the event_logs table, including:

• FOCUS_LOST
• TAB_SWITCH
• DEVICE_CHANGE

This data underpins the real‑time monitoring feature and exam integrity evaluation discussed in later chapters.

Chapter Summary

We have covered the Academic Suite database design, spanning organizational structures, academic models, exam transaction models, and activity logging. The design balances performance, data consistency, and development flexibility.

Next up: Chapter 3 – building the authentication and authorization system, the main gateway for securing access to all Academic Suite data and features.