vaccination

Table Name: vaccination

The vaccination table stores information about vaccinations administered to children. It tracks vaccination details, status, and metadata for auditing and reporting purposes.

---

Columns

Column Name	Data Type	Constraints	Description
vaccinationRecordNum	int(11)	Primary Key, Auto Increment	A unique identifier for each vaccination record.
childId	int(11)	Not Null, Foreign Key	Links to the child table, indicating the child who received the vaccination.
createdDate	datetime	Nullable	The date and time when the vaccination record was created.
description	varchar(255)	Nullable	A brief description of the vaccination event.
epiNumber	varchar(255)	Nullable	A unique number assigned to the vaccination event, typically for reporting or tracking purposes.
height	float	Nullable	The height of the child during the vaccination event (if recorded).
lastEditedDate	datetime	Nullable	The date and time when the record was last edited.
preferredReminderTiming	varchar(255)	Nullable	The preferred time for sending vaccination reminders.
reasonNotTimelyVaccination	varchar(255)	Nullable	The reason why the vaccination was not administered on time, if applicable.
vaccinationCenterId	int(11)	Nullable, Foreign Key	Links to the vaccinationcenter table, indicating the location of the vaccination event.
vaccinationDate	datetime	Nullable	The actual date and time of the vaccination.
vaccinationDuedate	datetime	Not Null	The scheduled date for the vaccination.
vaccinationStatus	varchar(20)	Not Null	The status of the vaccination (e.g., PENDING, COMPLETED, MISSED).
vaccinatorId	int(11)	Nullable, Foreign Key	Links to the vaccinator table, indicating the individual who administered the vaccination.
vaccineId	smallint(6)	Not Null, Foreign Key	Links to the vaccine table, identifying the type of vaccine administered.
weight	float	Nullable	The weight of the child during the vaccination event (if recorded).
createdByUserId	int(11)	Nullable, Foreign Key	Links to the user table, identifying the user who created the record.
lastEditedByUserId	int(11)	Nullable, Foreign Key	Links to the user table, identifying the user who last edited the record.
isVaccinationCenterChanged	tinyint(1)	Nullable	Indicates whether the vaccination center was changed (1 for yes, 0 for no).
isFirstVaccination	tinyint(1)	Nullable	Indicates whether this was the child’s first vaccination (1 for yes, 0 for no).
hasApprovedLottery	tinyint(1)	Nullable	Indicates whether the child is approved for a lottery associated with the vaccination (1 for yes).
timelinessFactor	smallint(6)	Nullable	A factor representing the timeliness of the vaccination event.
timelinessStatus	varchar(20)	Nullable	The timeliness status of the vaccination (e.g., ONTIME, LATE).
reasonVaccineNotGiven	varchar(255)	Nullable	The reason why the vaccine was not administered, if applicable.
vaccinationEventType	varchar(255)	Nullable	The type of vaccination event (e.g., ROUTINE, CAMPAIGN).
isImmunizedByLHW	tinyint(1)	Nullable	Indicates whether the vaccination was administered by a Lady Health Worker (LHW) (1 for yes, 0 for no).
vaccinationRefusaldate	datetime	Nullable	The date when the vaccination was refused, if applicable.

---

Indexes

1. Primary Key: vaccinationRecordNum
   • Ensures each record is uniquely identifiable.
2. Foreign Key Indexes:
   • vaccination_childId_child_mappedId_FK: Optimizes lookups for childId.
   • vaccination_vaccinatorId_vaccinator_mappedId_FK: Optimizes lookups for vaccinatorId.
   • vaccination_vaccinationCenterId_vaccinationcenter_mappedId_FK: Optimizes lookups for vaccinationCenterId.
   • vaccination_vaccineId_vaccine_vaccineId_FK: Optimizes lookups for vaccineId.
   • vaccination_createdByUserId_user_mappedId_FK: Optimizes lookups for createdByUserId.
   • vaccination_lastEditedByUserId_user_mappedId_FK: Optimizes lookups for lastEditedByUserId.
3. Additional Indexes:
   • vaccination_createdDate: Optimizes queries based on the creation date of records.
   • vaccination_lastEditedDate: Optimizes queries based on the last edited date of records.
   • vaccination_vaccinationdate: Optimizes queries based on vaccination date.
   • idx_vaccinationStatus: Optimizes queries filtering by vaccination status.
   • idx_vaccination_epiNumber: Optimizes queries filtering by EPI number.

---

Foreign Key Relationships

1. child table:
   • childId: Links to child(mappedId) to associate the vaccination record with a child.
2. user table:
   • createdByUserId: Links to user(mappedId) to track the creator of the record.
   • lastEditedByUserId: Links to user(mappedId) to track who last edited the record.
3. vaccinationcenter table:
   • vaccinationCenterId: Links to vaccinationcenter(mappedId) to specify the vaccination location.
4. vaccinator table:
   • vaccinatorId: Links to vaccinator(mappedId) to identify the person administering the vaccine.
5. vaccine table:
   • vaccineId: Links to vaccine(vaccineId) to specify the vaccine administered.

---

Usage Notes

• Purpose:

  • Tracks vaccination events for children, including timeliness, center information, and outcomes.
  • Useful for reporting, auditing, and ensuring compliance with vaccination schedules.

• Timeliness and Status:

  • Columns such as timelinessFactor, timelinessStatus, and reasonNotTimelyVaccination help evaluate and document vaccination delays.

• Integration:

  • Strongly integrated with other tables like child, vaccine, and vaccinator to provide comprehensive vaccination records.

• Event Management:

  • The table captures detailed event metadata, including vaccinationEventType, isFirstVaccination, and isImmunizedByLHW.

---