Skema adalah fondasi yang menanggung seluruh sisa proyek — dan pada proyek analisis, skema punya satu tuntutan khusus: tabel transaksi harus siap menerima data yang datang dari luar, yaitu CSV hasil ekspor aplikasi kasir. Pada bagian ini kita memutuskan dua tabel apa yang dibutuhkan, memilih constraint yang menjaga mutu data import, menuliskan semuanya ke schema.sql, lalu membuktikan bahwa penegakannya benar-benar bekerja. Materi intinya berasal dari Chapter Membuat Tabel dengan CREATE TABLE, Constraint pada Tabel, Foreign Key, dan Index dan Pencarian Cepat.

Dua Tabel dan Keputusan Desainnya #

Dari cara kerja toko, kita cukup membutuhkan dua tabel:

TabelIsiRelasi
produkdata master: delapan produk beserta kategori dan harga satuansatu produk menampung banyak penjualan
penjualansatu baris untuk satu transaksimenunjuk tepat satu produk

Tiga keputusan desain di balik tabel tersebut layak dibedah.

Kategori tinggal di produk sebagai CHECK IN. Proyek katalog perpustakaan memisahkan kategori ke tabel sendiri karena nama kategori ditulis ulang di setiap baris buku dan rawan salah ketik. Di sini kategorinya hanya tiga nilai tetap tanpa data tambahan apa pun, sehingga daftar di dalam CHECK cukup: nilai di luar Makanan, Minuman, dan Rumah Tangga langsung ditolak engine SQLite.

total_harga disimpan langsung, bukan dihitung ulang. Generated column tidak dapat membaca kolom milik tabel lain, jadi total_harga tidak mungkin dihitung dari harga_satuan tabel produk secara otomatis (batasan ini dibahas pada Chapter Generated Columns). Lebih penting dari itu: harga bisa berubah suatu saat, sedangkan riwayat transaksi harus menyimpan nilai yang benar pada saat transaksi terjadi. Aplikasi kasir menghitung total saat transaksi dan berkas CSV memuatnya — kita menyimpannya apa adanya. Konsistensinya dijaga lewat query verifikasi pada Bagian 2, bukan lewat constraint.

tanggal bertipe TEXT format ISO dengan pemeriksaan ketat. SQLite tidak punya tipe DATE; konvensinya menyimpan tanggal sebagai teks YYYY-MM-DD agar fungsi tanggal selalu membacanya (Chapter *Bekerja dengan Tanggal dan Waktu). CHECK (tanggal IS date(tanggal))memaksa format tersebut: fungsidate()mengembalikanNULLuntuk teks yang bukan tanggal ISO valid, sehingga perbandinganIS` bernilai salah dan baris ditolak.

Kode Lengkap Bagian 1 #

Simpan listing berikut sebagai schema.sql:

sql 
-- schema.sql — Analisis Penjualan
-- Bagian 1: skema database analisis penjualan + data master produk.
-- Jalankan: sqlite3 penjualan.db < schema.sql

PRAGMA foreign_keys = ON;

-- Data master: delapan produk tetap yang dijual toko.
-- Kolom kategori memakai CHECK IN agar nilai liar tertolak sejak awal.
CREATE TABLE produk (
    id           INTEGER PRIMARY KEY,
    nama         TEXT    NOT NULL,
    kategori     TEXT    NOT NULL CHECK (kategori IN ('Makanan', 'Minuman', 'Rumah Tangga')),
    harga_satuan INTEGER NOT NULL CHECK (harga_satuan > 0)
);

-- Data transaksi: satu baris = satu transaksi penjualan.
-- total_harga disimpan langsung (jumlah x harga saat transaksi),
-- sehingga riwayat tetap benar meski harga_satuan produk berubah.
CREATE TABLE penjualan (
    id          INTEGER PRIMARY KEY,
    tanggal     TEXT    NOT NULL CHECK (tanggal IS date(tanggal)),
    produk_id   INTEGER NOT NULL REFERENCES produk(id) ON DELETE RESTRICT,
    jumlah      INTEGER NOT NULL CHECK (jumlah > 0),
    total_harga INTEGER NOT NULL CHECK (total_harga >= 0)
);

-- Index untuk dua pola akses utama analisis:
-- rentang/bulan tanggal dan penggabungan dari produk ke penjualan.
CREATE INDEX idx_penjualan_tanggal   ON penjualan(tanggal);
CREATE INDEX idx_penjualan_produk_id ON penjualan(produk_id);

INSERT INTO produk (id, nama, kategori, harga_satuan) VALUES
    (1, 'Kopi Gayo 250 g',           'Minuman',     65000),
    (2, 'Teh Melati 100 g',           'Minuman',     18000),
    (3, 'Gula Aren 500 g',            'Makanan',     25000),
    (4, 'Beras Pandan Wangi 5 kg',   'Makanan',     72000),
    (5, 'Minyak Goreng 2 L',          'Rumah Tangga', 38000),
    (6, 'Sabun Mandi Cair 450 ml',    'Rumah Tangga', 22000),
    (7, 'Pasta Gigi 190 g',           'Rumah Tangga', 16000),
    (8, 'Sarden Kaleng 155 g',        'Makanan',     12000);

Mari kita bedah keputusan-keputusan pentingnya:

  1. PRAGMA foreign_keys = ON; — SQLite tidak menegakkan foreign key secara bawaan; penegakan aktif per koneksi (Chapter Foreign Key). Baris ini memastikan skema dijalankan dengan penegakan menyala, dan import.sql pada Bagian 2 mengulang pragma yang sama.
  2. CHECK (kategori IN (...)) — tiga nilai sah yang tertulis di satu tempat. Salah ketik seperti 'Rumah tangga' (huruf kecil) akan tertolak, bukan diam-diam menjadi kategori keempat.
  3. CHECK (tanggal IS date(tanggal)) — operator IS dipakai bukan = karena date() mengembalikan NULL untuk teks yang tidak valid; perbandingan = dengan NULL menghasilkan NULL yang lolos dari CHECK, sedangkan IS menghasilkan salah yang menahan baris.
  4. REFERENCES produk(id) ON DELETE RESTRICT — produk adalah data master; barisnya tidak boleh dihapus selama masih ada riwayat penjualan yang menunjuk.
  5. CHECK (jumlah > 0) dan CHECK (total_harga >= 0) — transaksi dengan nol item atau total negatif hampir pasti data rusak. total_harga boleh nol (misalnya transaksi promosi gratis) tetapi tidak boleh negatif.
  6. Dua index di akhir skema — idx_penjualan_tanggal melayani analisis yang selalu mengelompokkan per bulan atau menyaring rentang tanggal, sedangkan idx_penjualan_produk_id mempercepat penggabungan dari produk ke penjualan — SQLite tidak membuat index otomatis untuk kolom foreign key (Chapter Index dan Pencarian Cepat).
  7. INSERT delapan produk menutup skema — data master ditanam sekali di sini, bukan di berkas import, karena produk adalah acuan tetap sementara penjualan adalah arus data bulanan.

Membangun Skema #

Bangun database baru dengan skema tersebut:

bash 
sqlite3 penjualan.db < schema.sql

Perintah tersebut selesai tanpa keluhan — sukses pada SQLite memang sunyi. Struktur tabel hasilnya dapat diperiksa dengan .schema; berikut tampilan untuk tabel penjualan yang paling padat aturan:

bash 
sqlite3 penjualan.db ".schema penjualan"

Output yang Dihasilkan:

text 
CREATE TABLE penjualan (
    id          INTEGER PRIMARY KEY,
    tanggal     TEXT    NOT NULL CHECK (tanggal IS date(tanggal)),
    produk_id   INTEGER NOT NULL REFERENCES produk(id) ON DELETE RESTRICT,
    jumlah      INTEGER NOT NULL CHECK (jumlah > 0),
    total_harga INTEGER NOT NULL CHECK (total_harga >= 0)
);
CREATE INDEX idx_penjualan_tanggal   ON penjualan(tanggal);
CREATE INDEX idx_penjualan_produk_id ON penjualan(produk_id);

Data master yang ikut tertanam dapat diperiksa dengan satu query. Buka sesi interaktif pada penjualan.db, lalu:

text 
sqlite> .headers on
sqlite> .mode column
sqlite> SELECT * FROM produk ORDER BY id;
id  nama                     kategori      harga_satuan
--  -----------------------  ------------  ------------
1   Kopi Gayo 250 g          Minuman       65000       
2   Teh Melati 100 g         Minuman       18000       
3   Gula Aren 500 g          Makanan       25000       
4   Beras Pandan Wangi 5 kg  Makanan       72000       
5   Minyak Goreng 2 L        Rumah Tangga  38000       
6   Sabun Mandi Cair 450 ml  Rumah Tangga  22000       
7   Pasta Gigi 190 g         Rumah Tangga  16000       
8   Sarden Kaleng 155 g      Makanan       12000

Delapan produk pada tiga kategori sudah berdiri. Tabel penjualan masih kosong — penegakan constraint-nya belum pernah diuji.

Menguji Penegakan Constraint #

Skema yang belum diuji adalah skema yang belum selesai. Agar penjualan.db tetap bersih untuk Bagian 2, jalankan skema yang sama ke database uji terpisah, lalu buka sesi interaktif padanya:

bash 
sqlite3 uji.db < schema.sql
sqlite3 uji.db

Sesi berikut menunjukkan tiga penolakan data rusak, satu penerimaan data valid, dan nasib DELETE pada ujung relasi foreign key:

text 
sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO penjualan (tanggal, produk_id, jumlah, total_harga)
   ...> VALUES ('2025-01-03', 99, 2, 130000);
Runtime error: FOREIGN KEY constraint failed (19)
sqlite> INSERT INTO penjualan (tanggal, produk_id, jumlah, total_harga)
   ...> VALUES ('15/01/2025', 1, 2, 130000);
Runtime error: CHECK constraint failed: tanggal IS date(tanggal) (19)
sqlite> INSERT INTO penjualan (tanggal, produk_id, jumlah, total_harga)
   ...> VALUES ('2025-01-15', 1, 0, 0);
Runtime error: CHECK constraint failed: jumlah > 0 (19)
sqlite> INSERT INTO penjualan (tanggal, produk_id, jumlah, total_harga)
   ...> VALUES ('2025-01-15', 1, 2, 130000);
sqlite> SELECT id, tanggal, produk_id, jumlah, total_harga FROM penjualan;
1|2025-01-15|1|2|130000
sqlite> DELETE FROM produk WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed (19)
sqlite> DELETE FROM penjualan;
sqlite> DELETE FROM produk WHERE id = 1;
sqlite> SELECT COUNT(*) AS sisa_produk FROM produk;
7
sqlite>

Mari kita baca peristiwa dalam sesi tersebut satu per satu:

  1. INSERT pertama ditolak: produk_id 99 tidak ada di tabel produk, sehingga foreign key menahan baris tersebut.
  2. INSERT kedua ditolak karena '15/01/2025' bukan format ISO — date() mengembalikan NULL dan CHECK tanggal menolaknya. Kesalahan format DD/MM/YYYY seperti ini adalah jebakan klasik data tanggal Indonesia.
  3. INSERT ketiga ditolak: jumlah 0 melanggar CHECK (jumlah > 0).
  4. INSERT keempat lolos dan SELECT menunjukkan barisnya tersimpan dengan id 1 — auto-assign INTEGER PRIMARY KEY bekerja karena kolom id tidak diisi.
  5. DELETE FROM produk WHERE id = 1 ditolak RESTRICT: masih ada baris penjualan yang menunjuk produk tersebut.
  6. Setelah tabel penjualan dikosongkan, DELETE yang sama berhasil — RESTRICT hanya berlaku selama ada baris yang menunjuk, dan sisa_produk tinggal 7.

Terakhir, periksa integritas database yang baru dibangun:

bash 
sqlite3 penjualan.db "PRAGMA foreign_key_check;"
sqlite3 penjualan.db "PRAGMA integrity_check;"

Output yang Dihasilkan:

text 
ok

PRAGMA foreign_key_check memeriksa pelanggaran foreign key tanpa memerlukan penegakan aktif; tidak mencetak apa pun berarti tidak ada baris yatim. PRAGMA integrity_check memeriksa struktur berkas database dan menjawab ok — keduanya dibahas pada Chapter Integrity Check dan Perbaikan.

Skema kini berdiri dengan penjaga yang terbukti bekerja. Tabel penjualan masih kosong menunggu 60 baris data dari aplikasi kasir — Bagian 2 mengimpornya. Berkas lengkap bagian ini (schema.sql) tersedia pada bagian Berkas Sumber di bawah halaman.

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

schema.sql

Unduh berkas
sql 
-- schema.sql — Analisis Penjualan
-- Bagian 1: skema database analisis penjualan + data master produk.
-- Jalankan: sqlite3 penjualan.db < schema.sql

PRAGMA foreign_keys = ON;

-- Data master: delapan produk tetap yang dijual toko.
-- Kolom kategori memakai CHECK IN agar nilai liar tertolak sejak awal.
CREATE TABLE produk (
    id           INTEGER PRIMARY KEY,
    nama         TEXT    NOT NULL,
    kategori     TEXT    NOT NULL CHECK (kategori IN ('Makanan', 'Minuman', 'Rumah Tangga')),
    harga_satuan INTEGER NOT NULL CHECK (harga_satuan > 0)
);

-- Data transaksi: satu baris = satu transaksi penjualan.
-- total_harga disimpan langsung (jumlah x harga saat transaksi),
-- sehingga riwayat tetap benar meski harga_satuan produk berubah.
CREATE TABLE penjualan (
    id          INTEGER PRIMARY KEY,
    tanggal     TEXT    NOT NULL CHECK (tanggal IS date(tanggal)),
    produk_id   INTEGER NOT NULL REFERENCES produk(id) ON DELETE RESTRICT,
    jumlah      INTEGER NOT NULL CHECK (jumlah > 0),
    total_harga INTEGER NOT NULL CHECK (total_harga >= 0)
);

-- Index untuk dua pola akses utama analisis:
-- rentang/bulan tanggal dan penggabungan dari produk ke penjualan.
CREATE INDEX idx_penjualan_tanggal   ON penjualan(tanggal);
CREATE INDEX idx_penjualan_produk_id ON penjualan(produk_id);

INSERT INTO produk (id, nama, kategori, harga_satuan) VALUES
    (1, 'Kopi Gayo 250 g',           'Minuman',     65000),
    (2, 'Teh Melati 100 g',           'Minuman',     18000),
    (3, 'Gula Aren 500 g',            'Makanan',     25000),
    (4, 'Beras Pandan Wangi 5 kg',   'Makanan',     72000),
    (5, 'Minyak Goreng 2 L',          'Rumah Tangga', 38000),
    (6, 'Sabun Mandi Cair 450 ml',    'Rumah Tangga', 22000),
    (7, 'Pasta Gigi 190 g',           'Rumah Tangga', 16000),
    (8, 'Sarden Kaleng 155 g',        'Makanan',     12000);