Logistika tizimining (DHL, FedEx kabi) yuragi uning ma'lumotlar bazasida uradi. Quyida yuqori yuklamaga mo'ljallangan, kengaytiriluvchan (scalable) Relational Database Schema (PostgreSQL) loyihasini taqdim etaman.

Bu sxema 3 ta asosiy blokga bo'lingan:

1. Core Operation (Buyurtmalar va Yuklar)

2. Tracking & History (Kuzatuv va Holatlar)

3. Logistics & Routing (Kuryerlar va Marshrutlar)

1. Entity-Relationship (ER) Diagramma Tushunchasi

Bizning sxemamizdagi asosiy bog'liqliklar:

• User (Mijoz) 1:N Shipment (Yuk)

• Shipment 1:N Package (Qadoq) (Bir yuk ichida bir nechta quti bo'lishi mumkin)

• Shipment 1:N TrackingEvent (Voqea)

• Route (Marshrut) 1:N ShipmentAssignment (Qaysi yuk qaysi mashinaga yuklangan)

2. Jadval Tuzilmalari (Batafsil)

Quyida jadvallarning SQL (PostgreSQL sintaksisida) ko'rinishi va ularning mantiqiy tushuntirilishi keltirilgan.

A. Asosiy Yuk Ma'lumotlari (shipments)

Bu tizimning eng markaziy jadvali.

SQL

CREATE TABLE shipments (
    id BIGSERIAL PRIMARY KEY,
    tracking_number VARCHAR(32) UNIQUE NOT NULL, -- Masalan: "DHL-123456789"
    sender_id BIGINT REFERENCES users(id),       -- Kim yuboryapti
    receiver_info JSONB NOT NULL,                -- Qabul qiluvchi ma'lumotlari (Ism, Tel, Manzil)
    
    -- Manzillar (JSONB afzal, chunki manzil formatlari o'zgaruvchan)
    origin_address JSONB NOT NULL,               -- Yuborilgan manzil (Snapshot)
    destination_address JSONB NOT NULL,          -- Yetkaziladigan manzil (Snapshot)
    
    current_status VARCHAR(20) DEFAULT 'CREATED', -- 'CREATED', 'PICKED_UP', 'IN_TRANSIT', 'DELIVERED', 'EXCEPTION'
    service_type VARCHAR(20),                    -- 'EXPRESS', 'STANDARD', 'OVERNIGHT'
    
    total_weight_kg DECIMAL(10, 2),              -- Umumiy og'irlik
    total_price DECIMAL(10, 2),                  -- Yetkazib berish narxi
    currency CHAR(3) DEFAULT 'UZS',
    
    estimated_delivery_date TIMESTAMP,           -- Taxminiy yetkazish vaqti
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Tez qidirish uchun indeks
CREATE INDEX idx_shipments_tracking ON shipments(tracking_number);
CREATE INDEX idx_shipments_status ON shipments(current_status);

B. Qadoqlar (packages)

Birgina tracking_number ostida bir nechta quti (box) bo'lishi mumkin. Masalan, bitta jo'natmada 3 ta quti bor.

SQL

CREATE TABLE packages (
    id BIGSERIAL PRIMARY KEY,
    shipment_id BIGINT REFERENCES shipments(id) ON DELETE CASCADE,
    barcode VARCHAR(64) UNIQUE NOT NULL,         -- Har bir qutining o'z unikal kodi
    
    weight_kg DECIMAL(10, 3) NOT NULL,
    dimensions_cm JSONB,                         -- {"length": 10, "width": 20, "height": 30}
    
    description TEXT,                            -- "Shisha idish", "Kitoblar"
    is_fragile BOOLEAN DEFAULT FALSE             -- Ehtiyotkorlik talabi
);

C. Kuzatuv Tarixi (tracking_events)

Foydalanuvchi "Yukim qayerda?" deb so'raganda chiqadigan ma'lumotlar. Bu jadval juda tez o'sadi, shuning uchun uni vaqt bo'yicha Partitioning qilish tavsiya etiladi.

SQL

CREATE TABLE tracking_events (
    id BIGSERIAL PRIMARY KEY,
    shipment_id BIGINT REFERENCES shipments(id),
    
    status_code VARCHAR(20) NOT NULL,            -- 'ARRIVED_AT_HUB', 'OUT_FOR_DELIVERY'
    location_id INT REFERENCES warehouses(id),   -- Qaysi omborda yoki filialda?
    
    description TEXT,                            -- "Toshkent taqsimlash markaziga yetib keldi"
    latitude DECIMAL(9, 6),                      -- GPS koordinata (agar kuryerda bo'lsa)
    longitude DECIMAL(9, 6),
    
    occurred_at TIMESTAMP DEFAULT NOW(),         -- Voqea sodir bo'lgan vaqt
    created_by_user_id BIGINT REFERENCES users(id) -- Kim skaner qildi (Omborchi/Kuryer)
);

D. Marshrutlash va Logistika (routes & route_stops)

Bu qism kuryerlar va yuk mashinalari harakatini boshqaradi.

1. Marshrutlar (routes): Bir kuryerning bir kunlik yoki bir reyslik ishi.

SQL

CREATE TABLE routes (
    id BIGSERIAL PRIMARY KEY,
    driver_id BIGINT REFERENCES users(id),       -- Kuryer
    vehicle_id INT REFERENCES vehicles(id),      -- Mashina (Ford Transit #404)
    
    status VARCHAR(20) DEFAULT 'PLANNED',        -- 'PLANNED', 'ACTIVE', 'COMPLETED'
    start_hub_id INT REFERENCES warehouses(id),  -- Qayerdan chiqib ketdi
    
    started_at TIMESTAMP,
    finished_at TIMESTAMP
);

2. Marshrut Bekatlari (route_stops): Marshrut ichidagi to'xtash nuqtalari ketma-ketligi.

SQL

CREATE TABLE route_stops (
    id BIGSERIAL PRIMARY KEY,
    route_id BIGINT REFERENCES routes(id),
    shipment_id BIGINT REFERENCES shipments(id),
    
    stop_sequence INT NOT NULL,                  -- 1, 2, 3... (Borish ketma-ketligi)
    stop_type VARCHAR(10) DEFAULT 'DELIVERY',    -- 'PICKUP' (olish) yoki 'DELIVERY' (berish)
    
    estimated_arrival TIMESTAMP,                 -- AI tomonidan hisoblangan vaqt
    actual_arrival TIMESTAMP,
    
    proof_of_delivery_img VARCHAR(255),          -- Imzo yoki rasm URL (S3 bucket)
    status VARCHAR(20) DEFAULT 'PENDING'         -- 'COMPLETED', 'FAILED' (Mijoz uyda yo'q)
);

3. Professional Optimization (Senior Level)

Oddiy sxemadan "Enterprise" darajaga o'tish uchun quyidagilarni qo'shish kerak:

1. JSONB dan foydalanish:

   • receiver_info va origin_address maydonlari JSONB formatida saqlangani ma'qul. Sababi, agar mijoz keyinchalik ofis manzilini o'zgartirsa ham, eski yuklar tarixida eski manzil saqlanib qolishi shart (History Preservation).

2. Geospatial Data (PostGIS):

   • Omborlar va kuryerlarning joylashuvi uchun oddiy float emas, PostgreSQL ning PostGIS kengatmasidan foydalanib GEOGRAPHY(Point, 4326) tipini ishlatish kerak. Bu "Menga eng yaqin 5 ta kuryerni top" degan so'rovni millisekundlarda bajaradi.

3. Idempotency Keys:

   • To'lov va buyurtma yaratish jadvallarida idempotency_key ustuni bo'lishi shart. Bu internet uzilib qolganda bir xil buyurtmani ikki marta yaratib qo'yishni oldini oladi.

4. Soft Deletes:

   • Hech qachon ma'lumotni o'chirmang (DELETE). Barcha jadvallarga deleted_at (TIMESTAMP, NULLABLE) ustunini qo'shing. Bu audit va xatolarni tiklash uchun juda muhim.

Xulosa

Ushbu sxema orqali siz quyidagi savollarga soniyalar ichida javob bera olasiz:

• "Mening yukim hozir qayerda?" (tracking_events orqali)

• "Bugun Ali ismli kuryer nechta yuk yetkazishi kerak?" (routes -> route_stops orqali)

• "O'tgan oyda qaysi hududga eng ko'p yuk yuborildi?" (shipments -> destination_address bo'yicha analitika).

Bu loyihani boshlash uchun mustahkam poydevordir.