Academic Suite Database Design

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 will discuss database design from a system domain perspective, rather than just table structures. The main focus is on how the data schema supports the 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.

Broadly speaking, the Academic Suite data model is divided into four main groups:

1. Organizational Models – Institutions, users, and classes
2. Academic Models – Subjects, quizzes, and question banks
3. Exam Models – Exam execution and student attempts
4. Audit & Monitoring Models – Answers and anti-cheating activities

This approach helps separate data responsibilities and facilitates the development of advanced features.

---

2.2 Entity Relationship Diagram (ERD)

The following diagram 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 will serve as the main reference throughout this chapter and subsequent chapters.

---

2.3 Organizational Models (User, Institution, Class)

Institution & User

Every user in the system must be tied to a single institution. This design enables multi-tenancy in the future, where a single application instance can serve multiple schools or universities.

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 is used to group students and facilitate exam scheduling.

One critical design decision in this table is the storage of the student list:

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

Design Decision:
Using JSON instead of a junction table (class_students) aims to optimize read performance during exams. Under high-load conditions, reading a single row of data and parsing JSON is often faster than performing a JOIN on a large junction table.

Consequently, this approach sacrifices foreign key constraints at the database level. Consistency validation is performed at the application level.

---

2.4 Academic Models (Subject, Quiz, Question)

The academic structure is built hierarchically:

Subject → Quiz → Question

Subject

Represents the subjects offered by the institution.

Quiz

Quiz functions as the exam blueprint and stores global configurations, such as:

• Time limit (time_limit)
• Passing score (passing_score)
• Exam type (exam_type)

Question & Option

Each Question stores the question content and question type (mcq, essay, etc.). For multiple-choice questions, answer options are stored in a separate table (question_options).

This approach provides flexibility in the number of options and facilitates the development of new question types in the future.

---

2.5 Exam Models (ExamBatch & Attempt)

This section is the core of the online exam system and will receive the highest transaction load.

ExamBatch (Exam Session)

ExamBatch represents the concrete execution of a quiz.

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

Attempt (Exam Attempt)

Every time a student starts an exam, an Attempt record is created.

Important fields:

• status: ACTIVE, SUBMITTED
• remaining_time: snapshot of remaining time in seconds
• score: final score

Storing remaining_time ensures the exam remains consistent even if a page refresh or system disruption occurs.

---

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

Answer

The answers table stores student answers for each question.

Important characteristics:

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

Mandatory Optimization:

• Composite index on (attempt_id, question_id) to ensure queries remain 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 forms the basis of the real-time monitoring feature and exam integrity evaluation, which will be discussed in later chapters.

---

Chapter Summary

In this chapter, we have discussed the Academic Suite database design, ranging from organizational structure and academic models to exam transaction models and activity logging.

This design is crafted to maintain a balance between performance, data consistency, and development flexibility.

In Chapter 3, we will build the authentication and authorization system, which serves as the main gateway in securing access to all Academic Suite data and features.