campaignvaccination

Table: campaignvaccination

The campaignvaccination table stores vaccination events for children registered in campaigns.
It tracks which child received which vaccine, by whom, and when, including geolocation and audit information.

---

Columns

Column Name	Data Type	Constraints	Description
campaignVaccinationId	int(11)	NOT NULL, AUTO_INCREMENT	Unique identifier for each vaccination record
campaignChildId	int(11)	NULL	References the child receiving the vaccine
vaccineId	smallint(6)	NULL	References the vaccine administered
vaccinationStatus	varchar(20)	NULL	Status of vaccination (e.g., completed, missed)
vaccinationDate	datetime	NULL	Date when vaccination was administered
vaccinatorId	int(11)	NULL	User ID of the vaccinator who administered the vaccine
campaignId	int(11)	NULL	References the campaign under which vaccination occurred
createdByUserId	int(11)	NULL	User who created the record
createdDate	datetime	NULL	Timestamp when record was created
lastEditedByUserId	int(11)	NULL	User who last modified the record
lastEditedDate	datetime	NULL	Timestamp of last modification
latitude	varchar(45)	NULL	Latitude of vaccination location
longitude	varchar(45)	NULL	Longitude of vaccination location

---

Indexes

1. PRIMARY - Primary key on campaignVaccinationId
2. campaignvaccination_campaignChildId_campaignchild_childId_FK - Index on campaignChildId
3. campaignvaccination_campaignId_campaign_campaignId_FK - Index on campaignId
4. campaignvaccination_createdByUserId_user_mappedId_FK - Index on createdByUserId
5. campaignvaccination_lastEditedByUserId_user_mappedId_FK - Index on lastEditedByUserId
6. campaignvaccination_vaccinatorId_vaccinator_mappedId_FK - Index on vaccinatorId
7. campaignvaccination_vaccineId_vaccine_vaccineId_FK - Index on vaccineId

---

Foreign Key Relations

1. campaignvaccination_ibfk_1

   • campaignChildId → campaignchild.childId (ON UPDATE CASCADE)
   • Links vaccination to the child receiving it

2. campaignvaccination_ibfk_2

   • campaignId → campaign.campaignId
   • Links vaccination to its campaign

3. campaignvaccination_ibfk_3

   • createdByUserId → user.mappedId
   • Tracks user who created the record

4. campaignvaccination_ibfk_4

   • lastEditedByUserId → user.mappedId
   • Tracks user who last edited the record

5. campaignvaccination_ibfk_5

   • vaccinatorId → vaccinator.mappedId
   • Identifies the vaccinator who administered the vaccine

6. campaignvaccination_ibfk_6

   • vaccineId → vaccine.vaccineId
   • Identifies the vaccine administered

---

Usage Notes

• Geolocation fields (latitude, longitude) capture vaccination location for tracking purposes.
• vaccinationStatus tracks completion or missed vaccinations.
• Audit fields (createdByUserId, lastEditedByUserId, createdDate, lastEditedDate) maintain full history.
• Records link children, campaigns, vaccinators, and vaccines for reporting and compliance.
• ON UPDATE CASCADE ensures child updates are reflected in vaccination records.