location_hierarchy_ancester
Table Name: location_hierarchy_ancester
The location_hierarchy_ancester table captures hierarchical relationships and ancestry information among locations. It is primarily used to define the parent-child and relative relationships between different locations, supporting features like hierarchy traversal, ancestry tracking, and relational queries.
Columns
| Column Name | Data Type | Description |
|---|---|---|
| locationId | int(11) | The unique identifier of the location. |
| name | varchar(45) | The name of the location. |
| relative | int(11) | The unique identifier of the relative location. |
| relativeName | varchar(45) | The name of the relative location. |
| locationType | int(11) | The type of the location, referencing the locationtype table. |
| relativeLocationType | int(11) | The type of the relative location, referencing the locationtype table. |
| ancestry | varchar(1024) | A string representation of the location's ancestry hierarchy (e.g., Country > State > City). |
| isRelative | int(1) | Indicates whether the relative location is directly related (1) or not (0). |
| parentId | int(11) | The identifier of the parent location in the hierarchy. |
| voided | tinyint(4) | Indicates whether the record is voided (1) or active (0). |
| dateVoided | datetime | The date when the record was voided. |
25.2 Indexes
- Key Indexes:
- locationId: Optimizes queries filtering by location.
- name: Supports searching locations by name.
- ancestry (ancestry(255)): Enables efficient queries on partial ancestry paths.
- relative: Optimizes retrieval of relationships between locations.
- locationType: Indexes location type for hierarchical queries.
- isRelative: Supports quick identification of direct relatives.
- parentId: Facilitates traversal of parent-child relationships.
- voided: Enables filtering by active or voided status.
- Composite Indexes:
- locationId, locationType: Combines location and type for better hierarchical filtering.
- locationId, relative, locationType: Optimizes relational queries involving location, relative, and type.
- locationId, relativeLocationType: Enhances queries involving location and relative location type.
Foreign Key Relations
- locationType → locationtype.locationTypeId:
Links the location's type with the locationtype table to categorize and enforce type-specific hierarchies. - relativeLocationType → locationtype.locationTypeId:
Connects the type of the relative location to its corresponding entry in the locationtype table. -
parentId → location.locationId:
Establishes a parent-child relationship within the location table, allowing recursive traversal of the hierarchy.
Usage Notes
- Hierarchy Representation:
The ancestry column provides a textual hierarchy of the location’s lineage, which can be useful for displaying or traversing hierarchical structures. - Relative Relationships:
The relative and isRelative columns allow for specifying both direct and indirect relationships between locations, which is essential for complex hierarchical navigation. - Parent-Child Connections:
The parentId column explicitly denotes the immediate parent location, aiding in recursive queries for hierarchical traversal. - Voiding and Deletion:
The voided and dateVoided columns provide soft deletion capabilities, allowing for records to be marked as inactive without permanent removal. - Efficient Searching:
The combination of ancestry, relative, and composite indexes ensures efficient querying for hierarchy and relationship-based operations. - Integration with Location Types:
The locationType and relativeLocationType fields link with the locationtype table, enforcing categorization and hierarchy within defined location types.