International Location Database Schema with Latitude and Longitude

CREATE TABLE Structure for Global Location Data

CREATE TABLE IF NOT EXISTS `mk_international_location` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pid` int(10) unsigned DEFAULT '0' COMMENT 'Parent ID / Superior ID',
  `path` varchar(255) DEFAULT '' COMMENT 'Path',
  `level` int(10) unsigned DEFAULT '0' COMMENT 'Hierarchy Level',
  `name` varchar(255) DEFAULT '' COMMENT 'Chinese Name',
  `name_en` varchar(255) DEFAULT '' COMMENT 'English Name',
  `name_pinyin` varchar(255) DEFAULT '' COMMENT 'Chinese Pinyin',
  `code` varchar(50) DEFAULT '' COMMENT 'Region Code',
  `zip_code` varchar(50) DEFAULT '' COMMENT 'Postal Code',
  `status` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT 'Status Value (0: Inactive, 1: Active)',
  `manager_id` int(10) unsigned DEFAULT '0' COMMENT 'Operator Admin ID',
  `manager_username` varchar(30) DEFAULT '' COMMENT 'Operator Username',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `lat` varchar(255) DEFAULT NULL COMMENT 'Latitude (Baidu)',
  `lng` varchar(255) DEFAULT NULL COMMENT 'Longitude (Baidu)',
  PRIMARY KEY (`id`),
  KEY `international_location_pid_index` (`pid`)
);

Explanation:

- id: Unique identifier for each entry.

- pid: Represents parent or superior region.

- name and name_en: Name in both Chinese and English.

- lat and lng: Latitude and longitude coordinates for precise location mapping.

The table schema is designed to support hierarchical and multilingual location-based data with added fields for administrative control.