vaccine

Table Name: vaccine

The vaccine table contains information about vaccines, including their attributes, creation and modification details, and status. It plays a central role in managing vaccine-related data within the healthcare system.

---

Columns

Column Name	Data Type	Constraints	Description
vaccineId	smallint(6)	Primary Key, Not Null, Auto Increment	Unique identifier for each vaccine.
createdDate	datetime	Nullable	The date and time when the vaccine record was created.
description	varchar(255)	Nullable	Detailed description of the vaccine.
fullName	varchar(50)	Nullable	Full name of the vaccine.
lastEditedDate	datetime	Nullable	The date and time when the vaccine record was last edited.
name	varchar(30)	Not Null, Unique	Unique name of the vaccine.
shortName	varchar(30)	Not Null	Short or abbreviated name of the vaccine.
createdByUserId	int(11)	Nullable, Foreign Key	Identifier of the user who created the record.
lastEditedByUserId	int(11)	Nullable, Foreign Key	Identifier of the user who last edited the record.
maxGracePeriodDays	tinyint(6)	Nullable	Maximum allowable grace period (in days) for administering the vaccine.
minGracePeriodDays	tinyint(6)	Nullable	Minimum allowable grace period (in days) for administering the vaccine.
voidReason	varchar(255)	Nullable	Reason for voiding the vaccine record (if applicable).
voided	tinyint(1)	Nullable	Indicates if the vaccine record has been voided (1 = voided, 0 = not voided).
voidedDate	datetime	Nullable	The date and time when the vaccine record was voided.
voidedByUserId	int(11)	Nullable, Foreign Key	Identifier of the user who voided the record.
isSupplementary	tinyint(1)	Nullable	Indicates if the vaccine is supplementary (1 = yes, 0 = no).
vaccine_entity	varchar(255)	Nullable	Additional categorization or entity related to the vaccine.
isMandatory	tinyint(1)	Nullable	Indicates if the vaccine is mandatory (1 = yes, 0 = no).
stockVaccineGroupId	int(11)	Nullable	Identifier of the stock or vaccine group related to the vaccine.

---

Indexes

1. Primary Key:
   • vaccineId: Ensures unique identification of each vaccine record.
2. Unique Key:
   • name: Ensures no duplicate names exist for vaccines.
3. Foreign Key Indexes:
   • vaccine_lastEditedByUserId_user_mappedId_FK: Links lastEditedByUserId to the user table.
   • vaccine_createdByUserId_user_mappedId_FK: Links createdByUserId to the user table.
   • vaccine_voidedByUserId_user_mappedId_FK: Links voidedByUserId to the user table.

---

Foreign Key Relationships

1. user table:
   • createdByUserId: Links the vaccine record to the user who created it.
   • lastEditedByUserId: Links the vaccine record to the user who last modified it.
   • voidedByUserId: Links the vaccine record to the user who voided it.

---

Usage Notes

• Purpose:
  • Stores and manages detailed information about vaccines for use in immunization programs and inventory management.
• Key Attributes:
  • isMandatory: Helps determine required vaccines for compliance.
  • isSupplementary: Identifies additional, non-mandatory vaccines.
  • minGracePeriodDays and maxGracePeriodDays: Provide rules for vaccine administration timelines.
• Query Optimization:
  • Use the name index for fast lookup of vaccines by name.
  • Use voided and isMandatory columns for reporting and filtering.