women
Table Name: women
The women table stores demographic, marital, and enrollment-related information about women. It plays a crucial role in tracking data for programs such as maternal health, vaccination, and other social initiatives.
Columns
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| mappedId | int(11) | Primary Key, Not Null | A unique identifier for each woman. |
| firstName | varchar(30) | Nullable | First name of the woman. |
| middleName | varchar(30) | Nullable | Middle name of the woman. |
| lastName | varchar(30) | Nullable | Last name of the woman. |
| birthdate | datetime | Nullable | Birthdate of the woman. |
| estimatedBirthdate | tinyint(1) | Nullable | Indicates whether the birthdate is estimated (1 for true, 0 for false). |
| fatherFirstName | varchar(30) | Nullable | First name of the woman's father. |
| fatherLastName | varchar(30) | Nullable | Last name of the woman's father. |
| fatherMiddleName | varchar(30) | Nullable | Middle name of the woman's father. |
| husbandFirstName | varchar(30) | Nullable | First name of the woman's husband (if married). |
| husbandLastName | varchar(30) | Nullable | Last name of the woman's husband (if married). |
| husbandMiddleName | varchar(30) | Nullable | Middle name of the woman's husband (if married). |
| maritalStatus | varchar(30) | Nullable | Marital status of the woman (e.g., Single, Married, Divorced). |
| nic | varchar(20) | Nullable | National identification card (NIC) number of the woman. |
| nicOwnerFirstName | varchar(30) | Nullable | First name of the NIC owner (if not the woman). |
| nicOwnerLastName | varchar(30) | Nullable | Last name of the NIC owner. |
| nicOwnerRelation | varchar(50) | Nullable | Relationship between the NIC owner and the woman. |
| domicile | varchar(30) | Nullable | Domicile of the woman. |
| dateEnrolled | datetime | Nullable | Date when the woman was enrolled in the program. |
| dateOfCompletion | datetime | Nullable | Date when the woman completed the program. |
| terminationDate | datetime | Nullable | Date when the woman's enrollment was terminated. |
| terminationReason | varchar(255) | Nullable | Reason for termination of enrollment. |
| status | varchar(20) | Not Null, Default 'ENROLLMENT' | Current status of the woman in the program (e.g., ENROLLMENT, TERMINATED). |
| enrollmentVaccineId | smallint(6) | Nullable, Foreign Key | Links to the vaccine table to indicate the vaccine associated with enrollment. |
| description | varchar(255) | Nullable | Additional descriptive information about the woman. |
| createdByUserId | int(11) | Nullable, Foreign Key | Links to the user table, identifying the user who created the record. |
| createdDate | datetime | Nullable | Date when the record was created. |
| lastEditedByUserId | int(11) | Nullable, Foreign Key | Links to the user table, identifying the user who last edited the record. |
| lastEditedDate | datetime | Nullable | Date when the record was last edited. |
| pregnantStatus | varchar(45) | Nullable | Indicates the pregnancy status of the woman. |
| isRegistryEnrollment | tinyint(1) | Nullable, Default 0 | Indicates whether the woman is enrolled in a registry (1 for true, 0 for false). |
| motherTongue | varchar(45) | Nullable | The primary language spoken by the woman. |
| medicalRecordNumber | varchar(30) | Nullable | Medical record number associated with the woman. |
| isConvertedtoBirthRegistry | tinyint(1) | Nullable, Default 0 | Indicates if the woman’s data has been converted to a birth registry. |
Indexes
- Primary Key:
- mappedId: Ensures uniqueness for each record.
- Foreign Key Indexes:
- women_lastEditedByUserId_user_mappedId_FK: Optimizes lookups for lastEditedByUserId and enforces the relationship with the user table.
- women_createdByUserId_user_mappedId_FK: Optimizes lookups for createdByUserId and enforces the relationship with the user table.
- women_enrVaccId_vaccine_vaccId_idx: Links enrollmentVaccineId to the vaccine table.
Foreign Key Relationships
- user table:
- createdByUserId: Links to the user who created the record.
- lastEditedByUserId: Links to the user who last edited the record.
- vaccine table:
- enrollmentVaccineId: Links to the vaccine associated with the woman's enrollment.
Usage Notes
- Purpose:
- The women table provides a comprehensive data structure for tracking personal, medical, and program-related information about women in health programs.
- Key fields such as status, pregnantStatus, and enrollmentVaccineId facilitate monitoring and reporting.
- Data Quality:
- Ensure standardization in fields like maritalStatus and pregnantStatus by using predefined values.
- Verify consistency of nic and related owner information for proper identification.
- Query Optimization:
- Indexes on createdByUserId, lastEditedByUserId, and enrollmentVaccineId support efficient querying and relationship traversal.