hpv_vaccination

Table: hpv_vaccination

The hpv_vaccination table records individual HPV vaccination events for children.
It tracks vaccination status, location, vaccinator, and campaign context for reporting and monitoring purposes.

---

Columns

Column Name	Data Type	Constraints	Description
vaccinationRecordNum	int(11)	NOT NULL, AUTO_INCREMENT, PRIMARY KEY	Unique identifier for each vaccination record
created_at	datetime	DEFAULT CURRENT_TIMESTAMP	Timestamp when the record was created
modified_at	datetime	DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP	Timestamp when the record was last modified
child_id	int(11)	NOT NULL	ID of the vaccinated child (references hpv_child.mappedId)
vaccinator_id	int(11)	NOT NULL	ID of the vaccinator (references vaccinator.mappedId)
vaccinationDate	date	NOT NULL	Date of the vaccination event
status	enum('VACCINATED','PENDING','REFUSED','UNAVAILABLE')	NOT NULL	Status of the vaccination
ucId	int(11)	NOT NULL	Union council ID (references location.locationId)
locationId	int(11)	NULL	Location ID where vaccination occurred (references location.locationId)
school_id	int(11)	NULL	School ID if vaccination occurred at a school (references hpv_schools.school_id)
latitude	decimal(9,6)	NULL	Latitude of vaccination location
longitude	decimal(9,6)	NULL	Longitude of vaccination location
event_type	enum('OUTREACH','FIXED','MOBILE','SCHOOL','COMMUNITY','CENTER','VAN')	NULL	Type of vaccination event
sub_event_type	enum('OUTREACH','FIXED','MOBILE','SCHOOL','COMMUNITY','CENTER','VAN')	NULL	Sub-type of vaccination event
remarks	text	NULL	Additional remarks or notes
vaccinationTime	time	NOT NULL, DEFAULT '00:00:00'	Time when the vaccination was administered

---

Indexes

1. PRIMARY - Primary key on vaccinationRecordNum
2. location_idx - Index on locationId
3. vaccinator_idx - Index on vaccinator_id
4. child_idx - Index on child_id
5. idx_vaccination_date - Index on vaccinationDate
6. uc_idx - Index on ucId
7. vaccination_school_fk_idx - Index on school_id
8. filters_date_location - Composite index on (ucId, vaccinationDate) using BTREE

---

Foreign Key Relations

1. child → hpv_child.mappedId
   • Ensures the child exists in the HPV child table
2. vaccinator → vaccinator.mappedId
   • Ensures the vaccinator exists in the system
3. location → location.locationId
   • References the vaccination location
4. uc → location.locationId
   • References the union council location
5. vaccination_school_fk → hpv_schools.school_id
   • References the school where the vaccination occurred

---

Usage Notes

• Tracks all HPV vaccination events for reporting, monitoring, and auditing purposes.
• status indicates whether the child was vaccinated, refused, unavailable, or pending.
• Location fields (locationId, ucId, school_id) support geographical analysis and reporting.
• event_type and sub_event_type differentiate between fixed, outreach, school, and community campaigns.
• Composite index (ucId, vaccinationDate) is used for efficient date-location filtering in reports.