vaccinegap

Table Name: vaccinegap

The vaccinegap table manages information related to the time gaps between vaccine administrations, including mandatory status, priority, and measurement units. It establishes relationships between vaccines and gap types.

---

Columns

Column Name	Data Type	Constraints	Description
vaccineGapTypeId	smallint(6)	Primary Key, Foreign Key, Not Null	Unique identifier for the vaccine gap type. Links to the vaccinegaptype table.
vaccineId	smallint(6)	Primary Key, Foreign Key, Not Null	Unique identifier for the vaccine. Links to the vaccine table.
gapTimeUnit	varchar(20)	Nullable	Unit of time for the gap, such as "days," "weeks," or "months."
priority	smallint(6)	Not Null	Priority of this gap rule (lower values indicate higher priority).
value	smallint(6)	Not Null	Value indicating the duration of the gap based on gapTimeUnit.
mandatory	tinyint(1)	Nullable	Indicates if the gap is mandatory (1 = mandatory, 0 = optional).

---

Indexes

1. Primary Key:
   • vaccineGapTypeId, vaccineId: Composite primary key ensuring unique combinations of gap type and vaccine.
2. Foreign Key Indexes:
   • vaccinegap_vaccineId_vaccinegaptype_vaccinegaptypeId_FK: Links vaccineGapTypeId to the vaccinegaptype table.
   • vaccine_vaccineId_vaccinegap_vaccineId_FK: Links vaccineId to the vaccine table.
3. Additional Indexes:
   • vaccinegap_value: Indexes the value column for efficient range queries.
   • vaccinegap_timeunit: Indexes the gapTimeUnit column for filtering by time unit.

---

Foreign Key Relationships

1. vaccinegaptype table:
   • vaccineGapTypeId: Links to the vaccinegaptype table to specify the type of gap.
2. vaccine table:
   • vaccineId: Links to the vaccine table, associating the gap with a specific vaccine.

---

Usage Notes

• Purpose:
  • Defines rules for the time gaps between vaccine doses.
  • Ensures proper sequencing and adherence to vaccination schedules.
• Key Attributes:
  • gapTimeUnit: Allows flexibility in specifying gap durations (e.g., "2 weeks" or "6 months").
  • mandatory: Indicates if the time gap is a strict requirement.
• Query Optimization:
  • Use the priority column to sort and identify the most critical gap rules.
  • The composite primary key ensures efficient lookups when querying gaps for specific vaccines and types.