Desain skema dan constraint

Constraint adalah pernyataan tentang apa yang benar, ditegakkan oleh satu komponen yang dilewati semua klien. Validasi di aplikasi itu permintaan; constraint itu fakta. Perbedaannya penting pada hari sebuah skrip, sebuah migrasi, atau layanan kedua menulis ke tabel itu.

Key

Primary key mengidentifikasi baris: unik dan tidak null. Ada dua aliran:

• Natural key — data yang sudah unik, seperti email atau kode negara ISO. Lebih sedikit kolom, dan tidak perlu join untuk tahu sebuah baris itu apa. Dia jebol ketika hal yang “unik” itu berubah, dan orang mengganti emailnya.
• Surrogate key — id yang dibuat mesin dan tanpa makna. Stabil selamanya, dengan biaya satu kolom tambahan dan satu join untuk bisa mengatakan apa pun tentangnya.

Pakai surrogate key dan taruh constraint UNIQUE pada yang naturalnya. Itu memberi kedua sifatnya: identitas stabil untuk dirujuk foreign key, dan jaminan yang ditegakkan basis data bahwa aturan bisnisnya berlaku.

CREATE TABLE customer (
  id     bigserial PRIMARY KEY,     -- surrogate: yang dirujuk foreign key
  email  text NOT NULL UNIQUE       -- natural: yang dianggap unik oleh bisnis
);

Membuat id-nya:

Utamakan IDENTITY di atas serial untuk skema PostgreSQL baru: serial membuat sequence yang hanya terikat longgar ke kolomnya, dan itu terasa ketika kamu men-drop atau menyalin tabelnya.

UUID acak sebagai primary key adalah biaya nyata khusus di MySQL, dan materi berikutnya menjelaskan kenapa: InnoDB menyimpan tabelnya dalam urutan primary key, jadi key acak menyebarkan tulisan ke seluruh file. UUID_TO_BIN(uuid, 1) menyusun ulang bit timestamp-nya supaya kira-kira berurutan, dan dia ada tepat untuk alasan ini.

Foreign key, dan apa yang terjadi saat penghapusan

CREATE TABLE order_item (
  order_id bigint NOT NULL REFERENCES "order"(id) ON DELETE CASCADE,
  product_id bigint NOT NULL REFERENCES product(id) ON DELETE RESTRICT
);

Pilihannya bukan soal gaya. ON DELETE CASCADE dari order ke item-nya itu benar — item tidak bermakna tanpa order-nya. ON DELETE CASCADE dari kuis ke attempt-nya itu kesalahan, karena menghapus satu kuis akan diam-diam membawa setiap hasil yang pernah didapat siapa pun dengannya. Situs ini kena persis itu dan jawabannya bukan aksi referensial yang berbeda: jawabannya berhenti menghapus. Konten yang ditarik berpindah ke draft dan mempertahankan anak-anaknya.

Itu pelajaran umumnya. Sebelum memilih CASCADE, tanyakan baris anak itu bukti dari apa. Kalau dia catatan sesuatu yang pernah terjadi, dia harus hidup lebih lama daripada hal yang dirujuknya.

Catatan MySQL: InnoDB diam-diam membuat index pada kolom foreign key kalau belum ada. PostgreSQL tidak, dan foreign key tanpa index membuat setiap penghapusan induk memindai tabel anaknya. Buat index-nya sendiri.

NOT NULL dan CHECK

NOT NULL adalah dokumentasi termurah di sebuah skema, dan dia menghapus satu kelas penuh masalah NULL dari materi 2. Jadikan dia bawaanmu, dan buat kebolehan-NULL sebagai keputusan yang bisa kamu pertanggungjawabkan — shipped_at boleh NULL karena “belum dikirim” adalah keadaan yang sungguhan.

ALTER TABLE "order" ADD CONSTRAINT ck_order_status
  CHECK (status IN ('new', 'paid', 'shipped', 'cancelled'));

MySQL sebelum 8.0.16 menerima CHECK lalu mengabaikannya. Dia ter-parse, dia muncul di DDL-nya, dan dia tidak menegakkan apa pun. Kalau kamu mewarisi skema dari zaman itu, jangan berasumsi constraint yang tertulis di sana adalah constraint. Sejak 8.0.16 dia ditegakkan.

Namai constraint-mu. ck_order_status di pesan error memberitahumu apa yang jebol; nama bikinan order_status_check1 memaksamu pergi mencari.

Memodelkan himpunan nilai yang tertutup

Tiga pilihan, dan pertukarannya nyata:

-- 1. Constraint CHECK. Portabel, butuh migrasi untuk menambah nilai.
status text NOT NULL CHECK (status IN ('new', 'paid', 'shipped'))

-- 2. Enum bawaan.
--    PostgreSQL: CREATE TYPE order_status AS ENUM ('new','paid','shipped');
--    MySQL:      status ENUM('new','paid','shipped') NOT NULL
--    Padat, dan menyusahkan untuk diubah. ENUM MySQL juga numerik di dalamnya, jadi
--    menyusun ulang daftarnya menulis ulang makna baris yang sudah ada.

-- 3. Tabel lookup ditambah foreign key.
CREATE TABLE order_status (code text PRIMARY KEY, label text NOT NULL);
-- status text NOT NULL REFERENCES order_status(code)

Tabel lookup menang setiap kali himpunannya punya atribut — label untuk ditampilkan, urutan tampil, penanda aktif. CHECK menang untuk himpunan kecil yang benar-benar tidak akan tumbuh. Enum bawaan adalah pilihan yang paling dulu diambil orang dan paling belakangan disesali.

Tipe yang menyebabkan bug

Uang

Jangan pernah float atau double. Bilangan mengapung biner tidak bisa mewakili 0,1, jadi totalnya melenceng dan dua kali eksekusi tidak sepakat.

numeric PostgreSQL punya presisi sembarang; DECIMAL MySQL tetap sesuai deklarasi. Keduanya eksak, dan itu satu-satunya sifat yang penting di sini.

Timestamp

Ini kesalahan tipe yang paling mahal.

TIMESTAMP MySQL melakukan konversi yang benar dan mati di 2038 karena dia epoch 32-bit. DATETIME punya jangkauannya tapi tanpa zona, jadi dua server di zona berbeda menulis nilai berbeda untuk satu titik waktu yang sama. Disiplin yang bisa dijalankan di MySQL: simpan UTC di DATETIME, set time_zone = '+00:00' di setiap koneksi, dan konversi hanya untuk tampilan.

Teks

PostgreSQL: pakai text. Tidak ada perbedaan kinerja antara text dan varchar(n), jadi batas panjang seharusnya hanya ada ketika dia aturan yang sungguhan.

MySQL: VARCHAR(n) butuh panjangnya, dan dia berinteraksi dengan index — index punya batas panjang key (3072 byte di InnoDB dengan format baris DYNAMIC), dan utf8mb4 menghitung 4 byte per karakter, jadi VARCHAR(1000) tidak bisa di-index sepenuhnya.

Dan jebakan charset-nya: utf8 di MySQL bukan UTF-8. Dia subset tiga byte yang tidak bisa menyimpan emoji atau banyak karakter CJK, dan memasukkan satu saja akan error atau terpotong tergantung mode ketatnya. Yang sungguhan bernama utf8mb4. Dia bawaan sejak MySQL 8.0; apa pun yang lebih tua, atau bermigrasi dari yang lebih tua, perlu diperiksa.

JSON

jsonb benar-benar berguna untuk data yang bentuknya tidak kamu kendalikan. Dia bukan pengganti kolom: kamu kehilangan pemeriksaan tipe, NOT NULL, foreign key, dan statistik yang murah. Kalau kamu tahu sebuah field pasti ada, jadikan dia kolom.

Generated column

Kedua mesin bisa menghitung kolom dari kolom lain, yang mengubah ekspresi berulang jadi sesuatu yang bisa di-index.

-- PostgreSQL (hanya STORED)
ALTER TABLE order_item
  ADD COLUMN line_cents integer GENERATED ALWAYS AS (unit_cents * quantity) STORED;

-- MySQL (STORED atau VIRTUAL)
ALTER TABLE order_item
  ADD COLUMN line_cents INT AS (unit_cents * quantity) STORED;

VIRTUAL di MySQL menghitung saat dibaca dan tetap mendukung secondary index, dan itu membuatnya jalan pintas standar untuk dua hal yang tidak dimiliki MySQL: expression index dan partial index.

Normalisasi, singkat dan praktis

Bentuk-bentuknya punya nama, dan versi kerjanya lebih pendek:

1. Satu nilai per kolom. Jangan ada daftar dipisah koma di dalam field text.
2. Setiap kolom non-key bergantung pada key secara utuh. Kalau separuh kolommu hanya bergantung pada sebagian key gabungan, mereka milik tabel lain.
3. Tidak ada kolom yang bergantung pada kolom non-key lain. Menyimpan country dan country_name bersama berarti keduanya bisa bertentangan.

Kegagalan yang dicegah normalisasi bukan pemborosan ruang — tapi dua salinan satu fakta yang perlahan berbeda. Lakukan denormalisasi dengan sadar, ketika sebuah pengukuran mengatakan join-nya terlalu mahal, lalu ambil tanggung jawab menjaga salinannya tetap konsisten. Untuk itulah materialized view atau generated column ada.

Yang perlu dibawa pulang

• Primary key surrogate, UNIQUE pada natural key-nya.
• Pilih aksi referensial dari “baris anak ini bukti dari apa”. Catatan peristiwa harus hidup lebih lama daripada induknya.
• PostgreSQL tidak meng-index foreign key untukmu. MySQL melakukannya.
• MySQL di bawah 8.0.16 mengabaikan CHECK sepenuhnya.
• Uang dalam bilangan bulat satuan terkecil atau DECIMAL, jangan float.
• timestamptz di PostgreSQL; di MySQL, UTC di DATETIME dengan zona sesi yang tetap.
• utf8 di MySQL bukan UTF-8. Pakai utf8mb4.