Pindah dari satu proyek ke proyek, sayangnya, kita dihadapkan dengan kurangnya standar seragam untuk desain database, terlepas dari kenyataan bahwa SQL telah ada selama beberapa dekade. Saya menduga alasannya adalah sebagian karena sebagian besar pengembang tidak memahami arsitektur database. Selama bertahun-tahun bekerja dalam mempekerjakan pengembang, saya hanya bertemu beberapa kali dengan mereka yang dapat menormalkan database dengan benar. Jujur, ini bisa menjadi tugas yang sulit, tetapi banyak pengembang yang saya wawancarai, bahkan fasih dalam SQL, tidak memiliki keterampilan desain database.

Artikel ini bukan tentang normalisasi DB. Jika Anda ingin mempelajari ini, di sini saya secara singkat memberi tahu Anda dasar-dasarnya.

Jika Anda memiliki database yang berfungsi, maka Anda perlu menjawab pertanyaan Anda: "standar apa yang dapat diterapkan untuk memfasilitasi penggunaan database ini?". Jika standar-standar ini digunakan secara luas, maka akan mudah bagi Anda untuk menggunakan database, karena Anda tidak harus mempelajari dan mengingat set standar baru setiap kali Anda mulai bekerja dengan database baru.

Penamaan CamelCase atau garis bawah?

Saya terus-menerus menemukan database di mana tabel diberi nama dengan gaya CustomerOrders atau customer_orders . Mana yang lebih baik untuk digunakan? Mungkin Anda ingin menerapkan standar yang sudah ada, tetapi jika Anda membuat database baru, maka saya sarankan menggunakan garis bawah untuk meningkatkan aksesibilitas. Ungkapan "di bawah nilai" memiliki arti yang berbeda dibandingkan dengan "undervalue", tetapi dengan garis bawah, yang pertama akan selalu di bawah under_value , dan yang kedua akan di bawah undervalue . Dan ketika menggunakan CamelCase kita mendapatkan Undervalue dan UnderValue , yang identik dalam hal SQL case-insensitive. Selain itu, jika Anda memiliki masalah penglihatan dan terus-menerus bereksperimen dengan headset dan pin untuk menekankan kata-kata, maka menggarisbawahi jauh lebih mudah dibaca.

Akhirnya, CamelCase sulit dibaca bagi mereka yang bahasa Inggrisnya bukan bahasa asli.
Singkatnya, ini bukan rekomendasi yang ketat, tetapi preferensi pribadi.

Bentuk jamak atau tunggal dalam nama tabel?

Para ahli teori database telah berdebat sejak lama tentang apakah tabel harus tunggal (pelanggan) atau jamak (pelanggan). Biarkan saya memotong simpul Gordian ini tanpa masuk lebih dalam ke teori, hanya dengan bantuan pragmatisme: nama-nama tabel jamak kurang mungkin bertentangan dengan kata kunci yang dipesan.

Apakah Anda memiliki pengguna - users ? SQL memiliki kata kunci user . Apakah Anda memerlukan tabel kendala? constraint adalah kata yang dilindungi undang-undang. Kata audit
dicadangkan, tetapi apakah Anda memerlukan tabel audit ? Cukup gunakan bentuk jamak dari kata benda, dan kemudian sebagian besar kata-kata yang dipesan tidak akan mengganggu Anda dalam SQL. Bahkan PostgreSQL, yang memiliki parser SQL yang sangat baik, telah tersandung di tabel user .

Cukup gunakan bentuk jamak, dan kemungkinan konflik akan jauh lebih rendah.

Jangan beri nama kolom dengan ID sebagai "id"

Saya sendiri telah berdosa selama bertahun-tahun. Suatu kali saya bekerja dengan seorang klien di Paris, dan DBA mengeluh tentang saya ketika saya memberi kolom id nama id . Saya pikir dia hanya seorang pedant. Memang, nama kolom customers.id adalah unik, dan customers.customer_id adalah pengulangan informasi.

Dan kemudian saya harus men-debug ini:

 SELECT thread.* FROM email thread JOIN email selected ON selected.id = thread.id JOIN character recipient ON recipient.id = thread.recipient_id JOIN station_area sa ON sa.id = recipient.id JOIN station st ON st.id = sa.id JOIN star origin ON origin.id = thread.id JOIN star destination ON destination.id = st.id LEFT JOIN route ON ( route.from_id = origin.id AND route.to_id = destination.id ) WHERE selected.id = ? AND ( thread.sender_id = ? OR ( thread.recipient_id = ? AND ( origin.id = destination.id OR ( route.distance IS NOT NULL AND now() >= thread.datesent + ( route.distance * interval '30 seconds' ) )))) ORDER BY datesent ASC, thread.parent_id ASC 

Perhatikan masalahnya? Jika SQL menggunakan nama id lengkap, seperti email_id , star_id atau station_id , maka bug akan segera keluar saat saya menulis kode ini , dan tidak lebih kemudian, ketika saya mencoba memahami apa yang saya lakukan salah.

Bantulah diri Anda sendiri dan gunakan nama lengkap untuk ID. Terima kasih nanti

Nama kolom

Beri kolom nama deskriptif sebanyak mungkin. Katakanlah kolom temperature tidak ada hubungannya dengan ini:

 SELECT name, 'too cold' FROM areas WHERE temperature < 32; 

Saya tinggal di Prancis, dan bagi kami suhu 32 derajat akan "terlalu dingin". Karena itu, lebih baik memberi nama kolom fahrenheit .

 SELECT name, 'too cold' FROM areas WHERE fahrenheit < 32; 

Sekarang semuanya sudah jelas.

Jika Anda memiliki batasan kunci asing, berikan nama yang sama ke kolom di kedua sisi pembatasan bila memungkinkan. Berikut ini adalah SQL yang masuk akal, masuk akal:

 SELECT * FROM some_table s JOIN some_other_table o ON o.owner = s.person_id; 

Kode ini benar-benar baik-baik saja. Tetapi ketika Anda melihat definisi tabel, Anda akan melihat bahwa some_other_table.owner memiliki batasan kunci asing dengan companies.company_id . Jadi intinya SQL ini salah. Itu perlu untuk menggunakan nama yang identik:

 SELECT * FROM some_table s JOIN some_other_table o ON o.company_id = s.person_id; 

Sekarang segera jelas bahwa kami memiliki bug, Anda hanya perlu memeriksa satu baris kode dan tidak merujuk ke definisi tabel.

Namun, saya ingin mencatat bahwa ini tidak selalu bisa dilakukan. Jika Anda memiliki tabel dengan gudang sumber dan tujuan, maka Anda mungkin ingin membandingkan source_id dengan destination_id dengan warehouse_id . Dalam hal ini, lebih baik memberi nama source_warehouse_id dan destination_warehouse_id .

Perhatikan juga bahwa dalam contoh di atas, owner akan mendeskripsikan tujuannya lebih baik daripada company_id . Jika ini membingungkan Anda, Anda dapat memberi nama kolom owning_company_id . Maka nama akan memberi tahu Anda tujuan kolom.

Hindari Nilai NULL

Nasihat ini diketahui oleh banyak pengembang basis data yang berpengalaman, tetapi, sayangnya, mereka tidak cukup sering membicarakannya: tanpa alasan yang baik, jangan izinkan nilai NULL dalam basis data.
Ini adalah topik yang penting namun agak rumit. Pertama, kita membahas teori, kemudian pengaruhnya pada arsitektur database, dan sebagai kesimpulan kita akan menganalisis contoh praktis masalah serius yang disebabkan oleh adanya nilai-nilai NULL.

Jenis basis data

Basis data dapat berisi data dari berbagai jenis : INTEGER, JSON, DATETIME, dll. Jenis ini dikaitkan dengan kolom dan nilai apa pun yang ditambahkan harus sesuai dengan jenis ini.

Tapi apa jenisnya? Ini adalah nama, satu set nilai yang valid dan satu set operasi yang valid. Mereka membantu kita menghindari perilaku yang tidak diinginkan. Misalnya, apa yang terjadi di Jawa jika Anda mencoba membandingkan string dan angka?

 CustomerAccount.java:5: error: bad operand types for binary operator '>' if ( current > threshold ) { ^ first type: String second type: int 

Bahkan jika Anda tidak memperhatikan bahwa current > threshold membandingkan jenis yang tidak ada bandingnya, kompiler akan menangkap ini untuk Anda.

Ironisnya, database yang menyimpan data Anda - dan merupakan garis pertahanan terakhir Anda terhadap korupsi data - bekerja sangat buruk dengan tipe! Hanya menjijikkan. Misalnya, jika tabel customers Anda memiliki kunci pengganti, Anda dapat melakukan ini:

 SELECT name, birthdate FROM customers WHERE customer_id > weight; 

Tentu saja, ini tidak masuk akal dan pada kenyataannya Anda akan mendapatkan kesalahan kompilasi. Banyak bahasa pemrograman membuatnya lebih mudah untuk menangkap kesalahan tipe seperti itu, tetapi dengan database, yang terjadi adalah sebaliknya.

Ini adalah situasi normal di dunia basis data, mungkin karena standar SQL pertama dirilis pada tahun 1992 . Komputer lambat pada tahun-tahun itu, dan segala sesuatu yang rumit dalam implementasinya tidak diragukan lagi memperlambat database.

Dan kemudian nilai NULL muncul di tempat kejadian. Standar SQL mengimplementasikannya dengan benar hanya di satu tempat, dalam IS NOT NULL IS NULL dan IS NOT NULL . Karena nilai NULL tidak diketahui menurut definisi, Anda tidak dapat memiliki operator yang dirancang untuk itu. Jadi ada IS NULL dan IS NOT NULL bukannya = NULL dan != NULL . Dan setiap perbandingan nilai NULL mengarah ke tampilan nilai NULL baru.

Jika ini terdengar aneh bagi Anda, akan jauh lebih mudah jika Anda menulis "tidak dikenal" daripada NULL:

Membandingkan nilai-nilai NULL yang tidak diketahui menghasilkan nilai-nilai NULL yang tidak diketahui.

Ya, sekarang saya mengerti!

Apa yang dimaksud dengan nilai nol?

Berbekal remah-remah teori, kami mempertimbangkan konsekuensi praktisnya.

Anda perlu membayar bonus $ 500 untuk semua karyawan yang gajinya untuk tahun ini berjumlah lebih dari $ 50 ribu. Anda menulis kode ini:

 SELECT employee_number, name FROM employees WHERE salary > 50000; 

Dan Anda baru saja dipecat, karena bos Anda menghasilkan lebih dari $ 50 ribu, tetapi gajinya tidak ada di basis data (di kolom employees.salary Kolom NULL adalah NULL), dan operator pembanding tidak dapat membandingkan NULL dengan 50.000.

Mengapa ada NULL di kolom ini? Mungkin gaji itu rahasia. Mungkin informasinya belum tiba. Mungkin ini konsultan dan tidak dibayar. Mungkin dia memiliki upah per jam, bukan gaji. Ada banyak alasan mengapa data mungkin hilang.

Ada atau tidaknya informasi dalam kolom menunjukkan bahwa itu tergantung pada sesuatu yang lain , dan bukan pada denormalisasi kunci utama dan basis data. Dengan demikian, kolom di mana mungkin ada nilai NULL adalah kandidat yang baik untuk membuat tabel baru. Dalam hal ini, Anda mungkin memiliki tabel , _ , __ , dll. Anda masih dipecat karena menggabungkan gaji secara membabi buta dan atasan Anda tidak memilikinya. Tetapi kemudian pangkalan Anda mulai memberi Anda informasi yang cukup untuk menyarankan bahwa masalahnya lebih dari sekadar masalah gaji.

Dan ya, itu adalah contoh yang bodoh, tapi itu yang terakhir.

Nilai NULL mengarah pada situasi yang secara logis tidak mungkin

Tampaknya bagi Anda bahwa saya bertele-tele sehubungan dengan nilai-nilai NULL. Namun, mari kita lihat contoh lain yang jauh lebih dekat dengan kenyataan.

Beberapa tahun yang lalu, saya bekerja di London untuk pendaftar domain dan mencoba memahami mengapa kueri SQL 80-baris mengembalikan data yang salah. Dalam situasi itu, informasi pasti dikembalikan, tetapi ini tidak terjadi. Saya malu untuk mengakuinya, tetapi saya butuh waktu sehari untuk memahami bahwa alasannya adalah kombinasi dari beberapa kondisi:

• Saya menggunakan OUTER JOIN.
  
• Mereka dapat dengan mudah menghasilkan nilai NULL.
  
• Nilai NULL dapat menyebabkan SQL memberikan jawaban yang salah.
  

Banyak pengembang tidak menyadari aspek yang terakhir, jadi mari kita lihat contoh dari buku Database In Depth . Diagram sederhana dari dua tabel:

suppliers

parts


Sulit untuk menemukan contoh yang lebih sederhana.

Kode ini mengembalikan p1 .

 SELECT part_id FROM parts; 

Apa yang akan dilakukan kode ini?

 SELECT part_id FROM parts WHERE city = city; 

Ini tidak akan mengembalikan apa pun, karena Anda tidak dapat membandingkan nilai NULL, bahkan dengan NULL lain atau NULL yang sama. Itu terlihat aneh karena kota di setiap jalur harus sama, bahkan jika kita tidak mengetahuinya, kan? Lalu apa yang akan mengembalikan kode berikut? Cobalah untuk memahami ini sebelum membaca lebih lanjut.

 SELECT s.supplier_id, p.part_id FROM suppliers s, parts p WHERE p.city <> s.city OR p.city <> 'Paris'; 

Kami tidak mendapatkan string sebagai tanggapan, karena kami tidak dapat membandingkan kota NULL ( p.city ), dan karena itu tidak ada cabang dari WHERE mengarah pada true .

Namun, kita tahu bahwa kota yang tidak dikenal adalah Paris atau bukan Paris. Jika Paris, maka kondisi pertama akan benar ( <> 'London' ). Jika bukan Paris, maka kondisi kedua akan benar ( <> 'Paris' ). Jadi, WHERE harus true , tetapi tidak, dan sebagai hasilnya, SQL menghasilkan hasil yang mustahil secara logis.

Itu adalah bug yang saya temui di London. Setiap kali Anda menulis SQL yang bisa menghasilkan atau mengandung nilai NULL, Anda berisiko mendapatkan hasil yang salah. Ini jarang terjadi, tetapi sangat sulit untuk diidentifikasi.

Ringkasan

• Gunakan __ alih-alih CamelCase .
  
• Nama tabel harus dalam bentuk jamak.
  
• Berikan nama yang diperluas untuk bidang dengan pengidentifikasi ( item_id bukan id ).
  
• Hindari nama kolom yang ambigu.
  
• Jika memungkinkan, beri nama kolom dengan kunci asing dengan cara yang sama dengan kolom yang mereka rujuk.
  
• Jika memungkinkan, tambahkan BUKAN NULL ke semua definisi kolom.
  
• Kapan pun memungkinkan, hindari menulis SQL yang dapat menghasilkan nilai NULL.
  

Meski tidak sempurna, panduan desain basis data ini akan membuat hidup Anda lebih mudah.