Question 2

Database Design

MySQL/MariaDB schema for finding trucks within a radius, with indexing strategy, an optimised radius-search query, and design rationale.

trucks ~tens of thousands rows 
CREATE TABLE trucks (
  truck_id INT UNSIGNED
    AUTO_INCREMENT PRIMARY KEY,

  -- Fast lookup by plate number.
  -- UNIQUE doubles as the index.
  license_plate_number
    VARCHAR(20) NOT NULL,

  created_at DATETIME NULL,
  updated_at DATETIME NULL,

  UNIQUE KEY idx_trucks_plate
    (license_plate_number)
) ENGINE=InnoDB
  CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;
location_history tens of millions rows 
CREATE TABLE location_history (
  id        BIGINT UNSIGNED
              AUTO_INCREMENT PRIMARY KEY,
  truck_id  INT UNSIGNED NOT NULL,

  -- DATETIME avoids Year-2038 problem
  -- of TIMESTAMP; store UTC in app.
  timestamp DATETIME NOT NULL,

  -- DECIMAL(x,7) ≈ 1 cm precision
  latitude  DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(11,7) NOT NULL,

  address   VARCHAR(500) NULL,

  -- Key index: covers latest-location
  -- lookup AND history range queries.
  KEY idx_lh_truck_ts
    (truck_id, timestamp),

  FOREIGN KEY (truck_id)
    REFERENCES trucks(truck_id)
    ON DELETE CASCADE
) ENGINE=InnoDB CHARSET=utf8mb4;
truck_current_location one row per truck 
-- Denormalised "hot" table.
-- Upserted on every GPS ping so the
-- radius search never touches the
-- million-row history table.
CREATE TABLE truck_current_location (
  truck_id  INT UNSIGNED PRIMARY KEY,
  timestamp DATETIME NOT NULL,
  latitude  DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(11,7) NOT NULL,

  -- B-tree range scan on leading
  -- column (lat) narrows candidates
  -- before Haversine is evaluated.
  KEY idx_current_loc_lat_lng
    (latitude, longitude),

  FOREIGN KEY (truck_id)
    REFERENCES trucks(truck_id)
    ON DELETE CASCADE
) ENGINE=InnoDB;
✓
Migrations applied — all three tables exist in the logisly MySQL database.