Salah seorang member di salah satu grup di facebook pernah bertanya, bagaimana mengurutkan nomor kode jika data record dihapus. Sederhana sebenarnya, tapi kita memerlukan pendekatan yang sedikit rumit dan kemungkinan akan memakan resource dari sisi server (apabila data sangat banyak).
Ilustrasi dari pertanyaan tersebut adalah sebagai berikut:
Original data: 001 Budi 002 Rudi 003 Fahmi
Pada suatu saat, user akan menghapus data dengan kode 002. Tetapi aplikasi harus dapat mengubah nama Fahmi mempunyai kode 002 (menggantikan Rudi yang telah dihapus), dan seterusnya untuk record yang dibawahnya. Intinya, aplikasi ini harus dapat menangani sequensial nomor kode.
Sebenarnya dengan berbagai pendekatan logika dan lain-lain, hal ini bisa saja diwujudkan. Tetapi ada permasalahan lain, bagaimana jika data ini mempunyai relasi ke tabel lain. Misal, Rudi mempunyai histori di sebuah tabel, sedangkan Fahmi tidak mempunyai histori di tabel tersebut. Jika kode Fahmi (003) diganti menjadi kode nya Rudi (002), maka apakah selanjutnya Fahmi mempunyai histori di tabel tersebut?
Bisa ya bisa tidak. Jika anda seorang DB Admin yang cool, maka anda akan memikirkan masalah relasi antar tabel. Anda bisa menentukan apakah aktifitas UPDATE dan DELETE akan mempunyai efek ke tabel lain yang berelasi. Pada SQL Server nya Microsoft sudah ada fitur untuk membuat relasi antar tabel, dan pada relasi itu kita bisa mengatur Cascade Update dan Cascade Delete. Misalkan kita atur menjadi Cascade, maka perubahan data dari tabel master akan mempunyai efek ke tabel lain. Di tabel master, saya menghapus kode 002, maka pada tabel yang berhubungan, record dengan kode 002 akan terhapus.
Pada gambar di atas, dapat dilihat bahwa tabel siswa dan history berelasi melalui field idsiswa. Selanjutnya perhatikan gambar di bawah ini:
Perhatikan properties dari relasi pada gambar. Untuk Insert and Update Specification, saya buat rule Cascade untuk masing-masing Delete Rule dan Update Rule. Sampai tahap ini, pendekatan saya untuk membuat data itu terintegritas sudah cukup. Selanjutnya kita selesaikan permasalahan pengurutan nomor kode jika terjadi penghapusan data dan kode tidak menjadi urut. Urutan yang dikehendaki adalah 001, 002, 003, 004 dan seterusnya. Ingat, tipe data yang diurutkan adalah varchar, bukan Int. Ini menjadi lebih sulit lagi lho. #trust me.
Logika dan pemecahan masalah.
Jika anda berpikir untuk melakukan pengurutan dengan tipe angka adalah hal yang paling simpel, maka saya sepakat. Ada banyak pendekatan untuk langkah ini, tapi saya hanya mengambil satu pendekatan saja untuk solving masalah ini.
Pertama, saya melakukan query untuk menghapus sebuah record. Dan saya tidak perlu kuatir untuk tabel relasinya 🙂
Kedua, saya melakukan ALTER Tabel, untuk membuat sebuah id bayangan yang bertipe data INT, dan mempunyai Identity(1,1).
"ALTER TABLE siswa ADD idbayangan INT IDENTITY(1,1) "
Ketiga, setelah idbayangan terbentuk (yang harapan saya adalah mempunyai nomor yang urut), maka melalui program VB, saya melakukan update terhadap field idsiswa (pada contoh ini), dengan reformating untuk menambahkan leading zero, yang pastinya update terhadap idsiswa ini, akan berefek juga terhadap tabel histori.
Coba perhatikan gambar di bawah adalah kondisi sebelum delete:
Pada gambar terlihat, id 001 dimiliki oleh orang bernama Koko, mempunyai dua buah history di tabel history. Id 002 adalah seseorang bernama Amir, mempunyai 2 record di tabel history. Id 003 bernama Linda, memiliki sebuah record di tabel history. Saya akan mencoba menghapus ID 001, selanjutnya ID 002 akan menjadi 001, ID 001 dengan nama koko di tabel history akan hilang, dan ID 001 di history menjadi milik Amir.
Cek gambar di bawah setelah di delete:
yak, sampai disini sudah sesuai dengan yang diinginkan. File dari project ini dapat di download di : http://www.mediafire.com/?91qvfv72ba6ij2z
Catatan:
sampel ini belum di test untuk data dengan jumlah record yang banyak. Bisa di optimalkan selanjutnya dengan memakai triger maupun cursor. Belum dites juga untuk concurency. This is just a basic sample.