E-Commerce

Sistem E-Commerce

Katalog produk, pesanan, pembayaran, dan pengiriman

Platform e-commerce memiliki salah satu skema database paling kompleks. Dari manajemen katalog produk multi-variasi, cart, checkout, pembayaran, hingga tracking pengiriman — tiap bagian memiliki pola relasi dan optimasi query tersendiri.

Relasi 1:N M:N dengan pivot Transaksi Indeks Window Function CTE

ERD Diagram

Entity Relationship Diagram — notasi Chen (atribut oval)

Entitas = persegi panjang · Atribut = oval · Relasi = belah ketupat · Garis ganda = atribut kunci

Struktur Tabel

5 tabel dengan kolom, tipe data, dan constraint

pelanggan 5 kolom

Kolom	Tipe Data	Constraint	Keterangan
id_pelanggan	INT	PK	Primary key
nama	VARCHAR(100)		Nama lengkap
email	VARCHAR(150)	UQ	Email unik
telepon	VARCHAR(20)		Nomor telepon
tgl_daftar	DATETIME		Waktu pendaftaran

produk 7 kolom

Kolom	Tipe Data	Constraint	Keterangan
id_produk	INT	PK	Primary key
nama_produk	VARCHAR(255)		Nama produk
id_kategori	INT	FK	→ kategori_produk
harga	DECIMAL(15,2)		Harga satuan
stok	INT		Stok tersedia
berat_kg	DECIMAL(8,3)		Berat produk (kg)
deskripsi	TEXT		Deskripsi produk

pesanan 6 kolom

Kolom	Tipe Data	Constraint	Keterangan
id_pesanan	INT	PK	Primary key
id_pelanggan	INT	FK	→ pelanggan
tgl_pesan	DATETIME		Waktu pemesanan
status	ENUM		pending/dibayar/dikirim/selesai/batal
total_harga	DECIMAL(15,2)		Total nilai pesanan
alamat_kirim	TEXT		Alamat pengiriman

detail_pesanan 6 kolom

Kolom	Tipe Data	Constraint	Keterangan
id_detail	INT	PK	Primary key
id_pesanan	INT	FK	→ pesanan
id_produk	INT	FK	→ produk
qty	INT		Jumlah item
harga_satuan	DECIMAL(15,2)		Harga pada saat order
subtotal	DECIMAL(15,2)		qty × harga_satuan

pembayaran 6 kolom

Kolom	Tipe Data	Constraint	Keterangan
id_bayar	INT	PK	Primary key
id_pesanan	INT	FK	→ pesanan
metode	VARCHAR(50)		transfer/kartu/dompet digital
jumlah	DECIMAL(15,2)		Nominal pembayaran
tgl_bayar	DATETIME		Waktu pembayaran
status	ENUM		pending/sukses/gagal

Contoh Query

4 query analitik siap pakai

1. Revenue per bulan (tahun berjalan)

Laporan pendapatan bulanan dari pesanan yang sudah dibayar.

SELECT DATE_FORMAT(p.tgl_bayar, '%Y-%m') AS bulan,
       COUNT(DISTINCT p.id_pesanan)  AS total_pesanan,
       SUM(p.jumlah)                 AS revenue
FROM   pembayaran p
WHERE  p.status = 'sukses'
  AND  YEAR(p.tgl_bayar) = YEAR(CURDATE())
GROUP  BY bulan
ORDER  BY bulan;

2. Produk terlaris (top 10)

Ranking produk berdasarkan total unit terjual.

SELECT pr.nama_produk,
       SUM(dp.qty)              AS total_terjual,
       SUM(dp.subtotal)         AS total_revenue
FROM   detail_pesanan dp
JOIN   produk pr    ON pr.id_produk  = dp.id_produk
JOIN   pesanan ps   ON ps.id_pesanan = dp.id_pesanan
WHERE  ps.status IN ('dikirim','selesai')
GROUP  BY pr.id_produk
ORDER  BY total_terjual DESC
LIMIT  10;

3. Pelanggan dengan CLV tertinggi

Customer Lifetime Value — total pembelian per pelanggan.

SELECT c.nama, c.email,
       COUNT(DISTINCT ps.id_pesanan) AS jml_pesanan,
       SUM(b.jumlah)                 AS clv
FROM   pelanggan c
JOIN   pesanan ps   ON ps.id_pelanggan = c.id_pelanggan
JOIN   pembayaran b ON b.id_pesanan    = ps.id_pesanan
WHERE  b.status = 'sukses'
GROUP  BY c.id_pelanggan
ORDER  BY clv DESC
LIMIT  10;

4. Produk stok menipis (< 10 unit)

Alert untuk restocking produk dengan stok kritis.

SELECT pr.nama_produk, pr.stok,
       COALESCE(SUM(dp.qty), 0) AS terjual_bulan_ini
FROM   produk pr
LEFT JOIN detail_pesanan dp ON dp.id_produk = pr.id_produk
LEFT JOIN pesanan ps        ON ps.id_pesanan = dp.id_pesanan
                           AND MONTH(ps.tgl_pesan) = MONTH(CURDATE())
WHERE  pr.stok < 10
GROUP  BY pr.id_produk
ORDER  BY pr.stok ASC;

Case Study Lainnya

Sistem Perpustakaan → Sistem Rumah Sakit →