This document outlines the complete database table structure for the Exam Checking System, based on the actual database schema from localhost:3306 ExamChecker database. All table names and column names match exactly with the database implementation.
Table Name: Users
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | VARCHAR(50) | PRIMARY KEY | Unique user identifier |
| Username | VARCHAR(100) | UNIQUE, NOT NULL | User login name |
| VARCHAR(255) | UNIQUE, NOT NULL | User email address | |
| FullName | VARCHAR(255) | NOT NULL | User's full name |
| UserType | VARCHAR(20) | NOT NULL | User type: examiner, moderator, admin, student |
| PasswordHash | VARCHAR(255) | NOT NULL | Hashed password |
| IsActive | BOOLEAN | DEFAULT TRUE | User account status |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Account creation time |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update time |
| LastLogin | TIMESTAMP | NULL | Last login timestamp |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE INDEX idx_username (Username)
- UNIQUE INDEX idx_email (Email)
- INDEX idx_user_type (UserType)
- INDEX idx_is_active (IsActive)
Table Name: AnswerSheets
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | VARCHAR(50) | PRIMARY KEY | Unique answer sheet identifier |
| Name | VARCHAR(255) | NOT NULL | Answer sheet name/student name |
| PaperCode | VARCHAR(50) | NOT NULL | Paper code identifier |
| Code | VARCHAR(50) | NOT NULL | Answer sheet code |
| Url | TEXT | NOT NULL | PDF file URL |
| TotalPages | INT | DEFAULT 0 | Total number of pages |
| Status | VARCHAR(20) | DEFAULT 'pending' | Status: pending, checked, moderated |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
| ExaminerId | VARCHAR(50) | NULL | Assigned examiner ID |
| ModeratorId | VARCHAR(50) | NULL | Assigned moderator ID |
| ExamDate | DATE | NULL | Exam date |
| Subject | VARCHAR(100) | NULL | Subject name |
| MaxMarks | INT | DEFAULT 100 | Maximum marks for the exam |
| MarksObtained | DECIMAL(5,2) | DEFAULT 0 | Total marks obtained |
Indexes:
- PRIMARY KEY (Id)
- INDEX idx_papercode (PaperCode)
- INDEX idx_status (Status)
- INDEX idx_examiner (ExaminerId)
- INDEX idx_moderator (ModeratorId)
Table Name: AnswerSheetPages
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique page identifier |
| AnswerSheetId | VARCHAR(50) | NOT NULL, FK | Reference to AnswerSheets.Id |
| PageNumber | INT | NOT NULL | Page number in the document |
| IsBlank | BOOLEAN | DEFAULT FALSE | Whether page is blank |
| HasDrawings | BOOLEAN | DEFAULT FALSE | Whether page has drawings |
| HasMarks | BOOLEAN | DEFAULT FALSE | Whether page has marks |
| HasText | BOOLEAN | DEFAULT FALSE | Whether page has text annotations |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE KEY unique_answerSheet_page (AnswerSheetId, PageNumber)
- FOREIGN KEY (AnswerSheetId) REFERENCES AnswerSheets(Id) ON DELETE CASCADE
- INDEX idx_answerSheet_id (AnswerSheetId)
- INDEX idx_page_number (PageNumber)
Table Name: PageOverlays
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique overlay identifier |
| AnswerSheetId | VARCHAR(50) | NOT NULL, FK | Reference to answerSheet.Id |
| PageNumber | INT | NOT NULL | Page number |
| OverlayData | JSON | NOT NULL | Complete overlay data (JSON) |
| Version | INT | DEFAULT 1 | Version number for rollback |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE KEY unique_answerSheet_page_version (AnswerSheetId, PageNumber, Version)
- FOREIGN KEY (AnswerSheetId) REFERENCES AnswerSheets(Id) ON DELETE CASCADE
- INDEX idx_answerSheet_id (AnswerSheetId)
- INDEX idx_page_number (PageNumber)
- INDEX idx_version (Version)
Table Name: PagePaths
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique path identifier |
| PageOverlayId | BIGINT | NOT NULL, FK | Reference to PageOverlays.Id |
| PathType | VARCHAR(20) | NOT NULL | Type: path, oval, rect, line, star |
| PathData | JSON | NOT NULL | Path drawing data (JSON) |
| StrokeWidth | INT | DEFAULT 2 | Stroke width for drawing |
| StrokeColor | VARCHAR(20) | DEFAULT '#1565c0' | Stroke color |
| Coordinates | JSON | NOT NULL | Coordinate data (JSON) |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
Indexes:
- PRIMARY KEY (Id)
- FOREIGN KEY (PageOverlayId) REFERENCES PageOverlays(Id) ON DELETE CASCADE
- INDEX idx_page_overlay_id (PageOverlayId)
- INDEX idx_path_type (PathType)
Table Name: PageMarks
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique mark identifier |
| PageOverlayId | BIGINT | NOT NULL, FK | Reference to page_overlays.Id |
| MarkType | VARCHAR(20) | NOT NULL | Type: number, tick, cross, question |
| QuestionNumber | VARCHAR(20) | NULL | Question number |
| XCoordinate | DECIMAL(10,2) | NOT NULL | X coordinate position |
| YCoordinate | DECIMAL(10,2) | NOT NULL | Y coordinate position |
| MarkValue | DECIMAL(5,2) | DEFAULT 0 | Mark value |
| MarkSign | VARCHAR(1) | DEFAULT '+' | Sign: + or - |
| Numerator | INT | NULL | Fraction numerator |
| Denominator | INT | NULL | Fraction denominator |
| IsFraction | BOOLEAN | DEFAULT FALSE | Whether mark is a fraction |
| ExaminerId | VARCHAR(50) | NULL | Examiner who placed the mark |
| ModeratorId | VARCHAR(50) | NULL | Moderator who placed the mark |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- FOREIGN KEY (PageOverlayId) REFERENCES PageOverlays(Id) ON DELETE CASCADE
- INDEX idx_page_overlay_id (PageOverlayId)
- INDEX idx_question_number (QuestionNumber)
- INDEX idx_mark_type (MarkType)
- INDEX idx_examiner_id (ExaminerId)
- INDEX idx_moderator_id (ModeratorId)
Table Name: PageTextInputs
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique text input identifier |
| PageOverlayId | BIGINT | NOT NULL, FK | Reference to page_overlays.Id |
| TextContent | TEXT | NOT NULL | Text content |
| XCoordinate | DECIMAL(10,2) | NOT NULL | X coordinate position |
| YCoordinate | DECIMAL(10,2) | NOT NULL | Y coordinate position |
| FontSize | INT | DEFAULT 12 | Font size |
| FontColor | VARCHAR(20) | DEFAULT 'blue' | Font color |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- FOREIGN KEY (PageOverlayId) REFERENCES PageOverlays(Id) ON DELETE CASCADE
- INDEX idx_page_overlay_id (PageOverlayId)
Table Name: QuestionMarksSummaries
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique summary identifier |
| AnswerSheetId | VARCHAR(50) | NOT NULL, FK | Reference to answerSheet.Id |
| QuestionNumber | VARCHAR(20) | NOT NULL | Question number |
| TotalMarks | DECIMAL(5,2) | DEFAULT 0 | Total marks for question |
| MaxMarks | INT | DEFAULT 12 | Maximum marks for question |
| MarksObtained | DECIMAL(5,2) | DEFAULT 0 | Marks obtained for question |
| IsSubQuestion | BOOLEAN | DEFAULT FALSE | Whether it's a sub-question |
| ParentQuestion | VARCHAR(20) | NULL | Parent question number |
| ExaminerTotal | DECIMAL(5,2) | DEFAULT 0 | Examiner's total marks |
| ModeratorTotal | DECIMAL(5,2) | DEFAULT 0 | Moderator's total marks |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE KEY unique_answerSheet_question (AnswerSheetId, QuestionNumber)
- FOREIGN KEY (AnswerSheetId) REFERENCES AnswerSheets(Id) ON DELETE CASCADE
- INDEX idx_answerSheet_id (AnswerSheetId)
- INDEX idx_question_number (QuestionNumber)
- INDEX idx_parent_question (ParentQuestion)
Table Name: AuditLogs
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique audit log identifier |
| AnswerSheetId | VARCHAR(50) | NOT NULL, FK | Reference to answerSheet.Id |
| PageNumber | INT | NULL | Page number (if applicable) |
| UserId | VARCHAR(50) | NOT NULL, FK | Reference to Users.Id |
| UserType | VARCHAR(20) | NOT NULL | User type: examiner, moderator, admin, student |
| Action | VARCHAR(100) | NOT NULL | Action performed |
| ActionDetails | JSON | NULL | Action details (JSON) |
| Timestamp | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Action timestamp |
| IpAddress | VARCHAR(45) | NULL | User's IP address |
| UserAgent | TEXT | NULL | User agent string |
Indexes:
- PRIMARY KEY (Id)
- FOREIGN KEY (AnswerSheetId) REFERENCES AnswerSheets(Id) ON DELETE CASCADE
- FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE
- INDEX idx_answerSheet_id (AnswerSheetId)
- INDEX idx_user_id (UserId)
- INDEX idx_user_type (UserType)
- INDEX idx_action (Action)
- INDEX idx_timestamp (Timestamp)
Table Name: UserSessions
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique session identifier |
| UserId | VARCHAR(50) | NOT NULL, FK | Reference to Users.Id |
| SessionToken | VARCHAR(255) | NOT NULL | Session token |
| DeviceInfo | JSON | NULL | Device information (JSON) |
| IpAddress | VARCHAR(45) | NULL | User's IP address |
| UserAgent | TEXT | NULL | User agent string |
| LoginTime | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Login timestamp |
| LastActivity | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last activity timestamp |
| IsActive | BOOLEAN | DEFAULT TRUE | Session status |
Indexes:
- PRIMARY KEY (Id)
- FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE
- INDEX idx_user_id (UserId)
- INDEX idx_session_token (SessionToken)
- INDEX idx_is_active (IsActive)
Table Name: AnswerSheetAssignments
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique assignment identifier |
| AnswerSheetId | VARCHAR(50) | NOT NULL, FK | Reference to answerSheet.Id |
| UserId | VARCHAR(50) | NOT NULL, FK | Reference to users.Id |
| AssignmentType | VARCHAR(20) | NOT NULL | Type: examiner, moderator |
| AssignedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Assignment timestamp |
| CompletedAt | TIMESTAMP | NULL | Completion timestamp |
| Status | VARCHAR(20) | DEFAULT 'assigned' | Status: assigned, in_progress, completed, rejected |
| Notes | TEXT | NULL | Assignment notes |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE KEY unique_answerSheet_user_type (AnswerSheetId, UserId, AssignmentType)
- FOREIGN KEY (AnswerSheetId) REFERENCES AnswerSheets(Id) ON DELETE CASCADE
- FOREIGN KEY (UserId) REFERENCES Users(Id) ON DELETE CASCADE
- INDEX idx_answerSheet_id (AnswerSheetId)
- INDEX idx_user_id (UserId)
- INDEX idx_assignment_type (AssignmentType)
- INDEX idx_status (Status)
Table Name: ExamConfigurations
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique configuration identifier |
| ExamCode | VARCHAR(50) | UNIQUE, NOT NULL | Exam code |
| ExamName | VARCHAR(255) | NOT NULL | Exam name |
| Subject | VARCHAR(100) | NOT NULL | Subject name |
| TotalQuestions | INT | DEFAULT 26 | Total number of questions |
| TotalExamMarks | INT | DEFAULT 100 | Total exam marks |
| BestOfN | INT | DEFAULT 3 | Best of N questions |
| MarkingScheme | JSON | NULL | Marking scheme (JSON) |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE INDEX idx_exam_code (ExamCode)
- INDEX idx_subject (Subject)
Table Name: ExamQuestions
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique question identifier |
| ExamConfigurationId | BIGINT | NOT NULL, FK | Reference to ExamConfigurations.Id |
| QuestionNumber | VARCHAR(20) | NOT NULL | Question number |
| QuestionText | TEXT | NOT NULL | Question text |
| MaxMarks | DECIMAL(5,2) | NOT NULL | Maximum marks for question |
| QuestionType | VARCHAR(20) | DEFAULT 'single' | Type: single, multiple, essay, practical |
| IsSubQuestion | BOOLEAN | DEFAULT FALSE | Whether it's a sub-question |
| ParentQuestion | VARCHAR(20) | NULL | Parent question number |
| SubQuestionNumber | INT | NULL | Sub-question number |
| QuestionImageUrl | TEXT | NULL | Question image URL |
| QuestionPdfUrl | TEXT | NULL | Question PDF URL |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE KEY unique_exam_question (ExamConfigurationId, QuestionNumber)
- FOREIGN KEY (ExamConfigurationId) REFERENCES ExamConfigurations(Id) ON DELETE CASCADE
- INDEX idx_exam_configuration_id (ExamConfigurationId)
- INDEX idx_question_number (QuestionNumber)
- INDEX idx_parent_question (ParentQuestion)
Table Name: AnswerKeys
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique answer key identifier |
| ExamConfigurationId | BIGINT | NOT NULL, FK | Reference to ExamConfigurations.Id |
| QuestionNumber | VARCHAR(20) | NOT NULL | Question number |
| AnswerText | TEXT | NOT NULL | Answer text |
| MarkingPoints | JSON | NOT NULL | Marking points (JSON) |
| SampleAnswers | JSON | NULL | Sample answers (JSON) |
| Keywords | TEXT | NULL | Keywords for marking |
| MaxMarks | DECIMAL(5,2) | NOT NULL | Maximum marks |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE KEY unique_exam_answer (ExamConfigurationId, QuestionNumber)
- FOREIGN KEY (ExamConfigurationId) REFERENCES ExamConfigurations(Id) ON DELETE CASCADE
- INDEX idx_exam_configuration_id (ExamConfigurationId)
- INDEX idx_question_number (QuestionNumber)
Table Name: QuestionMarksConfigs
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique config identifier |
| ExamConfigurationId | BIGINT | NOT NULL, FK | Reference to ExamConfigurations.Id |
| QuestionNumber | VARCHAR(20) | NOT NULL | Question number |
| MaxMarks | DECIMAL(5,2) | NOT NULL | Maximum marks |
| PassingMarks | DECIMAL(5,2) | DEFAULT 0 | Passing marks |
| NegativeMarking | BOOLEAN | DEFAULT FALSE | Whether negative marking is enabled |
| NegativeMarks | DECIMAL(5,2) | DEFAULT 0 | Negative marks |
| IsCompulsory | BOOLEAN | DEFAULT TRUE | Whether question is compulsory |
| QuestionWeight | DECIMAL(3,2) | DEFAULT 1.00 | Question weight |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| UpdatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE KEY unique_exam_question_marks (ExamConfigurationId, QuestionNumber)
- FOREIGN KEY (ExamConfigurationId) REFERENCES ExamConfigurations(Id) ON DELETE CASCADE
- INDEX idx_exam_configuration_id (ExamConfigurationId)
- INDEX idx_question_number (QuestionNumber)
Table Name: AnswerSheetVersions
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique version identifier |
| AnswerSheetId | VARCHAR(50) | NOT NULL, FK | Reference to answerSheet.Id |
| VersionNumber | INT | NOT NULL | Version number |
| VersionData | JSON | NOT NULL | Version data (JSON) |
| CreatedBy | VARCHAR(50) | NOT NULL | User who created the version |
| CreatedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Creation timestamp |
| ChangeSummary | TEXT | NULL | Summary of changes |
Indexes:
- PRIMARY KEY (Id)
- UNIQUE KEY unique_answerSheet_version (AnswerSheetId, VersionNumber)
- FOREIGN KEY (AnswerSheetId) REFERENCES AnswerSheets(Id) ON DELETE CASCADE
- INDEX idx_answerSheet_id (AnswerSheetId)
- INDEX idx_version_number (VersionNumber)
- INDEX idx_created_by (CreatedBy)
Table Name: ExportLogs
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | BIGINT | PRIMARY KEY, AUTO_INCREMENT | Unique export log identifier |
| AnswerSheetId | VARCHAR(50) | NOT NULL, FK | Reference to answerSheet.Id |
| ExportType | VARCHAR(20) | NOT NULL | Type: csv, pdf, excel, json |
| ExportedBy | VARCHAR(50) | NOT NULL | User who exported |
| ExportPath | VARCHAR(500) | NULL | Export file path |
| ExportSize | BIGINT | NULL | Export file size |
| ExportMetadata | JSON | NULL | Export metadata (JSON) |
| ExportedAt | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Export timestamp |
Indexes:
- PRIMARY KEY (Id)
- FOREIGN KEY (AnswerSheetId) REFERENCES AnswerSheets(Id) ON DELETE CASCADE
- INDEX idx_answerSheet_id (AnswerSheetId)
- INDEX idx_export_type (ExportType)
- INDEX idx_exported_by (ExportedBy)
- INDEX idx_exported_at (ExportedAt)
Table Name: DocumentStates
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| Id | INT | PRIMARY KEY, AUTO_INCREMENT | Unique document state identifier |
| DocId | VARCHAR(50) | NOT NULL | Document identifier |
| Name | VARCHAR(255) | NOT NULL | Document name |
| PaperCode | VARCHAR(50) | NOT NULL | Paper code |
| Code | VARCHAR(50) | NOT NULL | Document code |
| PdfUrl | TEXT | NOT NULL | PDF URL |
| Opened | BOOLEAN | DEFAULT FALSE | Whether document is opened |
| Checked | BOOLEAN | DEFAULT FALSE | Whether document is checked |
| Overlays | JSON | DEFAULT '{}' | Overlay data (JSON) |
| UpdatedAtUtc | TIMESTAMP | DEFAULT CURRENT_TIMESTAMP | Last update timestamp |
Indexes:
- PRIMARY KEY (Id)
- INDEX idx_doc_id (DocId)
- INDEX idx_paper_code (PaperCode)
- INDEX idx_code (Code)
-
Users → UserSessions (1:N)
- Users.Id → UserSessions.UserId
-
Users → AnswerSheetAssignments (1:N)
- Users.Id → AnswerSheetAssignments.UserId
-
Users → AuditLogs (1:N)
- Users.Id → AuditLogs.UserId
-
AnswerSheets → AnswerSheetPages (1:N)
- AnswerSheets.Id → AnswerSheetPages.AnswerSheetId
-
AnswerSheets → PageOverlays (1:N)
- AnswerSheets.Id → PageOverlays.AnswerSheetId
-
AnswerSheets → QuestionMarksSummaries (1:N)
- AnswerSheets.Id → QuestionMarksSummaries.AnswerSheetId
-
AnswerSheets → AuditLogs (1:N)
- AnswerSheets.Id → AuditLogs.AnswerSheetId
-
AnswerSheets → AnswerSheetAssignments (1:N)
- AnswerSheets.Id → AnswerSheetAssignments.AnswerSheetId
-
AnswerSheets → AnswerSheetVersions (1:N)
- AnswerSheets.Id → AnswerSheetVersions.AnswerSheetId
-
AnswerSheets → ExportLogs (1:N)
- AnswerSheets.Id → ExportLogs.AnswerSheetId
-
PageOverlays → PagePaths (1:N)
- PageOverlays.Id → PagePaths.PageOverlayId
-
PageOverlays → PageMarks (1:N)
- PageOverlays.Id → PageMarks.PageOverlayId
-
PageOverlays → PageTextInputs (1:N)
- PageOverlays.Id → PageTextInputs.PageOverlayId
-
ExamConfigurations → ExamQuestions (1:N)
- ExamConfigurations.Id → ExamQuestions.ExamConfigurationId
-
ExamConfigurations → AnswerKeys (1:N)
- ExamConfigurations.Id → AnswerKeys.ExamConfigurationId
-
ExamConfigurations → QuestionMarksConfigs (1:N)
- ExamConfigurations.Id → QuestionMarksConfigs.ExamConfigurationId
- VARCHAR(50): Used for IDs and codes
- VARCHAR(255): Used for names and longer text fields
- TEXT: Used for long content like URLs and descriptions
- JSON: Used for complex nested data structures
- DECIMAL(10,2): Used for coordinates to maintain precision
- DECIMAL(5,2): Used for marks and scores
- DECIMAL(3,2): Used for weights and ratios
- TIMESTAMP: Used for all date/time fields
- BOOLEAN: Used for flags and status indicators
- PRIMARY KEY: All tables have auto-incrementing primary keys
- FOREIGN KEY: All relationships are properly constrained with CASCADE DELETE
- UNIQUE: Email, username, and composite keys are unique
- NOT NULL: Required fields are marked as NOT NULL
- DEFAULT VALUES: Most fields have appropriate default values
- Primary keys on all tables
- Foreign key indexes on all referenced columns
- Composite indexes for frequently queried combinations
- Text indexes for searchable content
- Timestamp indexes for date-based queries
This structure provides a robust foundation for the exam checking system while maintaining data integrity, performance, and scalability.