Scholarship Portal Database Design Under Load
A government scholarship portal stores applicant records in a single table with columns: applicant_id, name, national_id, district, school, guardian_phone, bank_account, application_date, status, and a free-text remarks field. About 2.5 million rows exist, growing by 400,000 per cycle. Queries by district and status take 40 seconds. The same applicant sometimes appears twice because national_id was not enforced as unique. Bank account numbers were stored in plain text and a junior officer recently exported a spreadsheet to a personal email. Auditors now demand a full trail of who changed an applicant's status and when.
1. Identify the design problems and propose a normalised schema.
Read the worked answer
Problems: (1) no uniqueness constraint on national_id, allowing duplicates; (2) district, school and guardian details repeated per application, causing update anomalies; (3) free-text remarks mixed with structured data; (4) sensitive bank data unencrypted; (5) no audit table. A normalised design: APPLICANT(applicant_id PK, national_id UNIQUE, name, guardian_phone_encrypted); SCHOOL(school_id PK, name, district_id FK); DISTRICT(district_id PK, name); APPLICATION(application_id PK, applicant_id FK, school_id FK, application_date, status_id FK); STATUS(status_id PK, label); BANK_ACCOUNT(account_id PK, applicant_id FK, account_number_encrypted); REMARK(remark_id PK, application_id FK, text, author, created_at). This removes duplication, enforces one record per national_id, and separates sensitive data so access can be restricted.
2. How should the audit requirement be met, and what about the 40-second queries?
Read the worked answer
Audit: add an APPLICATION_AUDIT table (audit_id, application_id, old_status, new_status, changed_by, changed_at, reason) populated by a trigger on status updates, or by the application layer if triggers are not permitted. Triggers are preferable because they catch changes made outside the application. The audit table must be append-only: revoke UPDATE and DELETE from application users. Performance: add a composite index on APPLICATION(status_id, school_id) and a join path to DISTRICT; most slow queries filter by district and status, so denormalising district_id onto APPLICATION may be justified for reporting. Test with EXPLAIN before and after. Also archive closed cycles to a history table so the hot table stays small.
3. What security controls are needed for the bank account data and the export incident?
Read the worked answer
Encrypt bank account numbers at rest (column-level encryption or transparent encryption) and mask them in the UI so staff see only the last four digits unless they hold a specific role. Restrict export: disable bulk export for most roles, log every export with user, row count and timestamp, and require a second approver for exports above a threshold. The spreadsheet incident should be treated as a data breach: notify the data protection authority per the applicable law, assess whether account numbers were exposed, and consider notifying affected applicants. Add data loss prevention or at minimum block personal webmail on office machines, and train staff. Review access logs for other exports.