What Are Databases? (A Simple Explanation)
If you're new to databases, think of them as organized filing cabinets for your application's data. Without a database, your data disappears every time the server restarts.
There are two main types:
SQL databases (PostgreSQL, MySQL) = Structured spreadsheets with strict rules
| id | name | email | status |
|-----|---------|-----------------|--------|
| 1 | Ahmad | [email protected] | active |
| 2 | Budi | [email protected] | idle |
NoSQL databases (Redis, MongoDB) = Flexible key-value storage
"driver:1" → { name: "Ahmad", status: "active", lastLocation: {...} }
"truck:ABC123:speed" → "67"
When to use which? That depends on your data — and this is where most developers make mistakes.
Why We Use Three Different Databases
Most tutorials use one database for everything. In the real world, different data has different needs. This approach is called polyglot persistence.
| Database | What We Store | Why This Database |
|---|---|---|
| PostgreSQL | GPS coordinates, fuel sensor data, route history | PostGIS for geospatial queries, excellent for time-series data |
| MySQL | Drivers, vehicles, invoices, schedules, users | Mature transactional support, team familiarity, Eloquent ORM |
| Redis | Cache, sessions, real-time vehicle positions, pub/sub events | Microsecond reads, perfect for "Where is truck X right now?" |
The Analogy
Think of a hospital:
- Patient records (MySQL) — structured, must be accurate, rarely change
- Heart monitor data (PostgreSQL) — high-volume, time-stamped, needs special queries
- Current room display (Redis) — needs to update instantly, temporary data
PostgreSQL: Telemetry Data
Schema Design
-- GPS telemetry — receives 1000+ rows per minute
CREATE TABLE telemetry_gps (
id BIGSERIAL PRIMARY KEY,
truck_id UUID NOT NULL,
latitude DECIMAL(10, 7) NOT NULL,
longitude DECIMAL(10, 7) NOT NULL,
speed SMALLINT DEFAULT 0, -- km/h
heading SMALLINT, -- degrees 0-360
recorded_at TIMESTAMPTZ NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Partition by month for performance
CREATE TABLE telemetry_gps_2026_05 PARTITION OF telemetry_gps
FOR VALUES FROM ('2026-05-01') TO ('2026-06-01');
CREATE TABLE telemetry_gps_2026_06 PARTITION OF telemetry_gps
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
-- Indexes for common queries
CREATE INDEX idx_gps_truck_time ON telemetry_gps (truck_id, recorded_at DESC);
CREATE INDEX idx_gps_location ON telemetry_gps USING GIST (
ST_MakePoint(longitude, latitude)
);
Why PostgreSQL for Telemetry?
-
Table Partitioning — Split millions of rows by month. Querying "last 24 hours" only scans today's partition, not 12 months of data.
-
PostGIS Extension — Geospatial queries become simple:
-- Find all trucks within 5km of a warehouse
SELECT truck_id, latitude, longitude
FROM telemetry_gps
WHERE ST_DWithin(
ST_MakePoint(longitude, latitude)::geography,
ST_MakePoint(106.8456, -6.2088)::geography, -- Warehouse location
5000 -- 5km radius
)
AND recorded_at > NOW() - INTERVAL '5 minutes';
- Time-series performance — With proper indexing, querying "show me truck ABC's route for the last 8 hours" returns in milliseconds, even with millions of rows.
MySQL: Business Data
Schema Design with Laravel Migrations
// Vehicles table
Schema::create('vehicles', function (Blueprint $table) {
$table->uuid('id')->primary();
$table->string('plate_number', 20)->unique();
$table->string('model', 100);
$table->integer('year');
$table->enum('status', ['active', 'idle', 'maintenance', 'offline'])
->default('offline');
$table->decimal('fuel_capacity', 8, 2);
$table->foreignUuid('current_driver_id')
->nullable()
->constrained('drivers')
->nullOnDelete();
$table->timestamps();
$table->softDeletes(); // Never hard-delete business data
});
// Deliveries table — the core business transaction
Schema::create('deliveries', function (Blueprint $table) {
$table->uuid('id')->primary();
$table->foreignUuid('vehicle_id')->constrained();
$table->foreignUuid('driver_id')->constrained();
$table->string('origin_name');
$table->string('destination_name');
$table->decimal('fuel_volume_liters', 10, 2);
$table->enum('status', [
'scheduled', 'in_transit', 'delivered', 'cancelled'
])->default('scheduled');
$table->timestamp('scheduled_at');
$table->timestamp('departed_at')->nullable();
$table->timestamp('delivered_at')->nullable();
$table->text('notes')->nullable();
$table->timestamps();
// Composite index for common queries
$table->index(['status', 'scheduled_at']);
$table->index(['driver_id', 'status']);
});
Why MySQL for Business Data?
-
ACID Transactions — When creating an invoice, you need to guarantee that ALL related records are saved or NONE are. MySQL excels at this.
-
Foreign Keys — Prevent orphaned records. You can't delete a driver who has active deliveries.
-
Soft Deletes — Business data should never be permanently deleted.
deleted_atcolumn lets you "hide" records while keeping them for audits.
Redis: Cache & Real-Time
Common Redis Patterns
// Pattern 1: Cache expensive database queries
async function getFleetStats(): Promise<FleetStats> {
const cacheKey = 'fleet:stats';
const cached = await redis.get(cacheKey);
if (cached) {
return JSON.parse(cached); // Cache hit — instant response
}
// Cache miss — query database
const stats = await calculateFleetStats();
await redis.setex(cacheKey, 60, JSON.stringify(stats)); // Cache for 60 seconds
return stats;
}
// Pattern 2: Store real-time vehicle positions
async function updateVehiclePosition(truckId: string, lat: number, lng: number) {
// Store the latest position (overwrites previous)
await redis.hset(`truck:${truckId}:position`, {
lat: lat.toString(),
lng: lng.toString(),
updatedAt: Date.now().toString(),
});
// Publish to all connected dashboard clients
await redis.publish('fleet:position-update', JSON.stringify({
truckId, lat, lng, timestamp: Date.now(),
}));
}
// Pattern 3: Rate limiting API requests
async function checkRateLimit(clientIp: string): Promise<boolean> {
const key = `ratelimit:${clientIp}`;
const current = await redis.incr(key);
if (current === 1) {
await redis.expire(key, 60); // Reset counter every 60 seconds
}
return current <= 100; // Max 100 requests per minute
}
Why Redis?
- Speed — Reads in ~0.1ms vs ~5ms for MySQL. For "where is truck X?", that difference matters when you have 200 trucks updating every 10 seconds.
- Pub/Sub — Built-in message broadcasting. When a truck sends new GPS data, all dashboard clients get the update instantly.
- Expiration — Data automatically deletes after a set time. Perfect for caches and temporary sessions.
Common Database Mistakes
Mistake 1: The N+1 Query Problem
// ❌ Bad — 1 query for drivers + N queries for vehicles (N+1 problem)
$drivers = Driver::all();
foreach ($drivers as $driver) {
echo $driver->vehicles->count(); // Each iteration = 1 more query!
}
// ✅ Good — 2 queries total using eager loading
$drivers = Driver::with('vehicles')->get();
foreach ($drivers as $driver) {
echo $driver->vehicles->count(); // No additional queries
}
Mistake 2: Missing Indexes
-- This query scans EVERY row without an index
SELECT * FROM deliveries WHERE status = 'in_transit' AND scheduled_at > '2026-05-01';
-- Add an index and it scans only matching rows
CREATE INDEX idx_deliveries_status_date ON deliveries (status, scheduled_at);
Rule of thumb: If you use a column in WHERE, JOIN, or ORDER BY, it probably needs an index.
Mistake 3: Storing Everything in One Database
Don't store 10 million GPS coordinates in the same MySQL instance that handles user login. The telemetry queries will slow down your login page.
What's Next
In Part 6, we'll apply SOLID principles and design patterns to refactor our code. We'll take real examples from our fleet system and show how clean code makes the difference between a maintainable system and a nightmare.
This is Part 5 of the Fleet Management System series. Understanding when to use which database is a skill that separates senior developers from those who just know SQL syntax.
