address
Table: address
The address table stores detailed address information for various entities. It supports hierarchical geographic data, including city, town, union council (UC), and province details. The table also accommodates metadata such as high-risk population indicators, descriptions, and timestamps for auditing.
Columns
| Column Name | Data Type | Constraints | Description |
|---|---|---|---|
| addressId | int(11) | NOT NULL AUTO_INCREMENT | Unique identifier for the address. |
| address1 | varchar(255) | Primary line of the address. | |
| address2 | varchar(255) | Secondary line of the address. | |
| address3 | varchar(255) | Tertiary line of the address. | |
| address4 | varchar(255) | Additional address details. | |
| address5 | varchar(255) | Additional address details. | |
| district | varchar(255) | District of the address. | |
| landmark | varchar(255) | Nearby landmark for better identification. | |
| ucId | int(11) | Identifier for the union council. | |
| uc | varchar(255) | Name of the union council. | |
| addressType | varchar(20) | Type of the address (e.g., residential, office). | |
| townId | int(11) | Identifier for the town. | |
| town | varchar(255) | Name of the town. | |
| cityId | int(11) | Identifier for the city (foreign key). | |
| cityName | varchar(30) | Name of the city. | |
| country | varchar(30) | Country of the address. | |
| highRiskPopulation | tinyint(1) | Indicator for high-risk population. | |
| createdDate | datetime | Date the record was created. | |
| description | varchar(255) | Additional description for the address. | |
| lastEditedDate | datetime | Last modification timestamp. | |
| lat | varchar(255) | Latitude coordinate of the address. | |
| lon | varchar(255) | Longitude coordinate of the address. | |
| mappedId | int(11) | NOT NULL | Mapped identifier linking to related records. |
| province | varchar(30) | Name of the province. | |
| provinceId | int(11) | Identifier for the province. | |
| zipcode | varchar(15) | Postal code of the address. | |
| createdByUserId | int(11) | User who created the record (foreign key). | |
| lastEditedByUserId | int(11) | User who last edited the record (foreign key). |
Indexes
- address_mappedId_IdMapper_mappedId_FK - Index on mappedId for quick lookups linking to idmapper.
- address_lastEditedByUserId_user_mappedId_FK - Index on lastEditedByUserId for tracking edits.
- address_createdByUserId_user_mappedId_FK - Index on createdByUserId for tracking record creation.
- address_cityid - Index on cityId for faster lookups by city.
- address_createDate - Index on createdDate to support date-based queries.
- address_lastEditedDate - Index on lastEditedDate to assist with audit trails.
- idx_address_ucId - Index on ucId for union council queries.
- idx_address_townId - Index on townId for town-based lookups.
Foreign Key Relations
- address_createdByUserId_user_mappedId_FK
- Links createdByUserId to the user table (mappedId).
- Tracks which user created the address record.
- address_lastEditedByUserId_user_mappedId_FK
- Links lastEditedByUserId to the user table (mappedId).
- Tracks which user last edited the record.
- address_ibfk_1
- Links cityId to the location table (locationId).
- Associates the address with a specific city.
- address_mappedId_IdMapper_mappedId_FK
- Links mappedId to the idmapper table (mappedId).
- Provides a reference for related records.
Usage Notes
- Geographic Hierarchy: The table supports a detailed geographic structure, enabling efficient address management at multiple levels, including city, town, and union council.
- Audit Trail: Columns like createdDate, lastEditedDate, createdByUserId, and lastEditedByUserId facilitate tracking changes to records.
- High-Risk Population: The highRiskPopulation column is useful for targeted interventions in specific areas.
- Flexible Addressing: Multiple address columns provide room for extensive address descriptions and variations.
- Mapped Relationships: The mappedId ensures easy integration with other related data models.