Membuat Model Basis Data

Belajar SQL | Bagian kedua dari rangkaian artikel tentang konsep database relational dan dasar-dasar SQL Pada bagian pertama kita sudah membahas tentang bagaimana cara membuat relasi antar tabel dan melakukan normalisasi terhadap data yang kita miliki. Bagian kedua ini akan membahas cara kita memodelkan data dan kardinalitas dari relasi, serta membuat diagram konseptual (conceptual model) menjadi diagram logikal (logical model), dan akhirnya menjadi diagram fisik (physical model). Kardinalitas (Cardinality) Antar Tabel Seperti sudah kita ketahui sebelumnya, database relasional menyimpan data dalam bentuk tabel dua dimensi tersusun atas baris dan kolom. Sebuah tabel juga memiliki primary key berupa satu atau beberapa kolom yang memastikan keunikan dari setiap baris dalam sebuah tabel. Dua buah tabel bisa berelasi jika memiliki kolom yang serupa, dan kolom yang serupa tersebut di satu tabel bertindak sebagai foreign key yang nilainya mengacu pada kolom primary key di tabel lainnya. Relasi antar tabel memiliki rasio, perbandingan jumlah baris di satu tabel dengan tabel lainnya, disebut juga kardinalitas. Macam-macam kardinalitas relasi antar tabel yang mungkin adalah: one-to-many, one-to-one, dan many-to-many. One-to-many (1:M). Kardinalitas relasi yang paling umum adalah relasi one-to-many. Coba perhatikan contoh relasi antar tabel EMPLOYEE dan DEPARTMENT seperti yang tampak pada gambar di bawah ini. Kolom yang serupa di antara kedua tabel tersebut adalah DEPARTMENT_ID, bertindak sebagai foreign key di tabel EMPLOYEE, dan sebagai primary key di tabel DEPARTMENT. Satu baris yang ada di tabel DEPARTMENT bisa berelasi dengan beberapa baris di tabel EMPLOYEE karena pastinya banyak karwaran yang bekerja di satu departemen yang sama. Gambar 1: Relasi 1:M - One to Many One-to-one (1:1). Dalam relasi 1:1 bisa dicontohkan dalam kasus data pasfoto terakhir karyawan antara table EMPLOYEE dan EMPLOYEE_PICTURE. Kolom EMPLOYEE_ID di table EMPLOYEE_PICTURE bertindah sekaligus sebagai primary key dan foreign key. Jadi setiap baris data karyawan di table EMPLOYEE hanya memiliki satu baris data pasangannya di tabel EMPLOYEE_PICTURE. Gambar 2: Relasi 1:1 - One to One Umumnya relasi one-to-one tidak dipisahkan menjadi dua buah tabel, namun disatukan saja dalam satu tabel agar lebih sederhana. Namun ada kasus dimana sebaiknya kita memisahkan kolom BLOB atau BINARY LARGE OBJECT dari tabel utama, ke tabel baru dan membuat relasi one-to-one, dengan tujuan untuk efisiensi pada saat query database. Many-to-many (M:M). Dalam dunia bisnis sangatlah umum jika seorang karyawan terlibat dalam beberapa proyek, dan pastinya sebuah proyek akan ditangani oleh beberapa karyawan. Pada kasus seperti inilah relasi many-to-many dapat kita gunakan. Untuk mendukung relasi many-to-many, maka kita perlu membuat tabel tambahan (kadang disebut tabel associative, intermediate, atau intersection). Jadi untuk mendukung relasi many-to-many antara tabel EMPLOYEE dengan tabel PROJECT maka kita harus membuat tabel bernama EMPLOYEE_PROJECT. Gambar 3: Relasi M:M - Many to Many Dalam associative table EMPLOYEE_PROJECT ini terdapat kolom EMPLOYEE_ID yang merupakan foreign key ke tabel EMPLOYEE, dan kolom PROJECT_ID yang merupakan foreign key ke tabel PROJECT. Primary key untuk tabel ini adalah composite key gabungan dari kedua kolom tersebut. Dengan desain seperti ini, maka kita bisa memastikan bahwa setiap proyek bisa ditangani beberapa karyawan, dan setiap karyawan bisa ditugaskan dalam beberapa proyek dengan tugas tertentu. Kardinalitas (Cardinality) Sebuah Tabel Setelah kita pahami kardinalitas relasi antar tabel seperti yang dijelaskan di atas, maka kita juga perlu memahami bahwa sebuah tabel yang terlibat dalam relasi juga memiliki kardinalitasnya sendiri, antara lain: zero or one, contoh kasusnya adalah seorang karyawan maksimal hanya akan bertugas di satu departemen saja, namun bisa jadi ada karyawan lain yang tidak terlibat di departemen apapun ketika menjabat sebagai direksi - ***one to (zero or one)***. one and only one, contoh kasusnya adalah pemisahan tabel pasfoto terakhir yang kita simpan dalam field BLOB di tabel EMPLOYEE_PICTURE mengahruskan setiap karyawan harus memiliki satu saja pasfoto terakhir - ***one to (one and only one)***. zero or one or many, contoh kasusnya adalah seorang karyawan back office mungkin tidak akan terlibat dalam satu project tertentu, namun karyawan lain yang bertugas di lapangan minimal akan menangani satu proyek, atau beberapa proyek sekaligus - ***one to (zero or one or many)***. one or many, contoh kasusnya adalah relasi kemampuan berbahasa seorang karyawan, yaitu setiap karyawan pasti menguasai minimal satu bahasa, namun bisa jadi menguasai bahasa lain - ***one to (one or many)***. Crow's Foot Notation Untuk memudahkan kita memahami relasi antar tabel, maka ada baiknya jika kita membuat diagram schema atau diagram model. Schema sendiri adalah kumpulan obyek-obyek database, seperti tabel, index, dsb., yang memiliki fungsi bisnis yang serupa. Ada banyak notasi yang bisa kita gunakan untuk membuat diagram schema database, antara lain notasi Chen, IDEF1X, Crow's Foot, Bachman, dan UML. Namun untuk artikel ini kita akan menggunakan notasi Crow's Foot untuk desain database schema. Notasi Crow's Foot cukup sederhana dan mudah untuk dipahami sudah menggambarkan kardinalitas dari relasi bersangkutan. Gambar di bawah ini adalah notasi Crow's Foot dengan simbol di dalam tanda kurung adalah notasi yang sama untuk UML. Gambar 4: Crow's Foot Notation Pentingnya Analisa dan Desain Sebelum kita membuat desain sebuah database, maka pertama-tama yang perlu kita lakukan requirements gathering, yaitu mengumpulkan semua kebutuhan dan keinginan para pengguna (users) kemudian melakukan analisa terhadap kebutuhan dan keinginan tersebut. Salah satu kebutuhan yang harus didapatkan dalam proses analisa ini adalah data apa saja yang ingin disimpan ke dalam database. Seorang analis sistem (system analyst) kemudian harus dapat mengidetifikasikan apa saja entitas (entity) yang terlibat dalam sistem yang ingin dikembangkan. Setelah semua entitas teridentifikasi maka kita buatkan Conceptual Model berupa relasi antar entitas tersebut. Perhatikan bahwa dalam Conceptual Model kita hanya menggambarkan relasi antar entitas saja, tidak termasuk atribut dari entitas tersebut. Gambar 5: Conceptual Model Berikutnya kita kembangkan Conceptual Model di atas menjadi Logical Model dengan menambahkan atribut-atribut (attributes) yang diperlukan oleh setiap entitas. Dalam Logical Model kita bisa langsung mendifinisikan atribut yang bertindak sebagai Primary Key, namun kita tidak perlu mendefinisikan Foreign Key dalam relasi yang ada. Sebuah diagram Logical Model yang menggambarkan relasi antar entitas di dalamnya biasanya dikenal sebagai Entity Relationship Diagram atau ERD Gambar 6: Logical Model Setelah Logical Model selesai kita buat, selanjutnya yang perlu kita lakukan adalah mengubahnya menjadi Physical Model. Dalam Physical Model ini kita menentukan tipe data dari masing-masing kolom sesuai dengan RDBMS yang kita pilih. Kita juga marus memetakan relasi Foreign Key serta membuat associative table untuk relasi many-to-many. Gambar 7: Physical Model Diagram ERD dalam artikel ini menggunakan tools ER Assistant yang bisa diunduh gratis di website ini. Natural Key vs. Surrogate Key Ada kalanya sebuah tabel sudah memiliki kolom yang nilainya unik untuk setiap baris (row) misalkan kolom DEPT_CODE yang nilainya pasti unik karena setiap departemen sudah memiliki kodenya masing-masing. Kolom seperti ini disebut Natural Key, dan langsung bisa kita jadikan sebagai Primary Key. Untuk tabel yang sudah memiliki Natural Key seperti ini, mungkin kita tidak perlu lagi menambahkan Surrogate Key. Namun sebagai best-practice ada baiknya kita tetap menambahkan Surrogate Key dan menjadikannya sebagai Primary Key. Natural Key yang ada cukup kita tambahkan unique index saja untuk memastikan keunikan nilainya. Salah satu kelemahan penggunaan Natural Key sebagai Primary Key adalah kita tidak bisa memastikan nilai dari Natural Key tidak akan berubah, sebagai contoh bisa saja kode departmen berubah ketika ada kebijakan baru. Bayangkan jika ada perubahan kode departemen, maka kita juga harus mengubah semua nilai foreign key yang mengacu ke kode departemen tersebut. Jika yang berelasi hanya satu-dua tabel mungkin tidak menjadi maslah, namun ketika database kita semakin kompleks, bisa jadi banyak sekali tabel yang harus kita ubah nilainya untuk menjaga referential integrity database kita. Normalisasi vs. Denormalisasi Seperti sudah kita ketahui sebelumnya, normalisasi data adalah proses yang dilakukan untuk menghilangkan (atau mengurangi) redundansi data berdasarkan aturan-aturan tertentu. Sebaliknya, denormalisasi justru menambahkan redundansi. Pada saat kita membuat desain database, kita harus menimbang-nimbang, apakah mengikuti semua aturan normalisasi untuk memastikan tidak adanya redundansi data.... atau membiarkan adanya terjadinya redundansi data. Ketika kita melakukan normalisasi data, maka semakin banyak tabel yang akan terbentuk, dan ketika kita memerlukan kembali data-data tersebut, maka kita perlu melakukan proses JOIN. Semakin banyak tabel yang terlibat dalam JOIN, maka kinerja (performance) query cenderung menurun. Jika jika kita membiarkan ada beberapa redundansi data, maka biasanya akan ada peningkatan di kinerja query, namun akan sulit bagi programmer untuk memastikan seluruh data redundan tetap sinkron dan menjaga referential integrity data. Pilihan sampai sejauh mana normalisasi dilakukan, dan perlu tidaknya melakukan denormalisasi sepenuhnya tergantung pada saat proses analisa dan desain Simpulan Analisa kebutuhan (requirements analysis) dan membuat model relasi antar entitas (entity relationships) - baik secara konsep, logis, dan fisik - merupakan langkah-langkah penting dan membangun desain database. Pemahaman terhadap proses bisnis (business process) dan aturan-aturan bisnis (busines rules) juga sangat membantu menentukan entitas apa saja yang terlibat, serta atribut apa saja perlu kita simpan ke dalam database. Artikel berikutnya akan membahas bagaimana kita melakukan proses temu kembali data (data retrieve) yang sudah ada di dalam database menggunakan perintah SQL SELECT (SELECT statement). Perintah SQL DDL lengkap untuk artikel ini dapat dilihat di github .