Desain Sistem Database untuk Transaksi

Dalam menyusun sebuah database transaksi, pada saat mendesain tabel yang akan digunakan untuk transaksi, yang perlu anda perhatikan adalah: bahwa ada dua kategegori transaksi berdasarkan jumlah data record yang disimpan setiap kali terjadi transaksi, yaitu 1 record atau lebih dari 1 record.

Jika data yang disimpan hanya 1 record setiap transaksi, maka cukup menggunakan 1 tabel dan ini jarang sekali terjadi pada sebuah transaksi; Sebagai gambaran pada saat terjadi transaksi penjualan tentu tidak hanya menyimpan 1 record saja, tetapi bisa lebih dari 1 record, seandainya jumlah barang yang terjual lebih dari 1.

Pada artikel ini akan membahasa tentang, bagaimana mendesain tabel transaksi yang baik dan mudah digunakan dalam aplikasi baik untuk kebutuhan input, edit, hapus ataupun laporan.

Konsep Desain Tabel Transaksi

Perlu digambarkan terlebih dahulu secara umum bahwa sebuah transaksi mengandung 2 kelompok data yang akan disimpan dalam tabel (yang saya beri istilah seperti sebuah artikel) yaitu:

• Data Header
  
  Data header terdiri dari data unik setiap transaksi yang terjadi dan hanya menyimpan 1 record setiap kali terjadi transaksi. Misalkan transaksi penjualan, maka akan menyimpan data Nomor Penjualan, Tanggal Penjualan, Jam Penjualan, Customer, Operator, Total Penjualan dan Total Pembayaran. Data header tersebut setiap record secara keseluruhan (distinct) tidak ada yang sama, dan akan dibedakan dengan sebuah data unik (bisa 1 field atau lebih). Misalkan data header penjualan akan dibedakan setiap tranasksinya berdasar Nomor Penjualan.
  
  Dengan penjelasan dan contoh tersebut diatas, maka secara definitif Nomor Penjualan adalah Index Key yang Unique.
  
  Perlu diperhatikan bahwa setiap anda menyusun sebuah tabel yang digunakan untuk menyimpan data header transaksi, selalu anda berikan kondisi/setting field Index Key dari tabel tersebut, karena hal ini juga akan berpengaruh terdapat kecepatan dalam pencarian, join tabel dan tentu dalam kebutuhan dalam mempercepat proses laporan yang mengharuskan mengambil sebagian data tabel yang jumlah recordnya sangat banyak.
  
  Dalam memberikan Index Key dan Unique pada saat setting tabel transaksi, selain berfungsi secara pasti untuk memberikan efek kecepatan dalam pencairan, join dan kebutuhan pengambilan recordset untuk laporan dan lain-lain, juga dapat berfungsi sebagai pelapis validasi terhadap program aplikasi yang akan anda susun, yaitu validasi saat penyimpanan sebuah data transaksi secara bersamaan oleh 1 atau lebih PC yang dalam waktu bersamaan akan memproses penyimpanan data transaksi dengan Nomor Penjualan yang bersifat counter.
  
  Dengan sebagai pelapis pelapis tersebut, maka pada saat terjadi kesalahan program karena tidak mampu memvalidasi Nomor Penjualan yang Unique, maka mesin database yang akan melakukannya, sehingga penyimpanan akan dibatalkan dan dimunculkan error yang bisa anda pakai sebagai trapping error.
  

• Data Detail
  
  Data detail adalah merupakan bagian kedua dari data transaksi, yaitu berupa informasi data detail dari item-item transaksi, misalkan sebuah transaksi penjualan, maka akan menyimpan data Nomor Penjualan, Kode Barang, Satuan, Jumlah Penjualan, Harga Satuan, Discount Barang dan Total Harga. Data detail harus ada kunci relasi ke data header transaksi sebagai pedoman untuk melakukan pembacaan, penghapusan, edit data setiap transaksi yang terjadi. Dalam contoh diatas kunci relasi nya adalah Nomor Penjualan. Tetapi perlu anda perhatikan bahwa Nomor Penjualan hanya berfungsi sebagai kunci relasi saja, bukan sebagai kunci index.
  
  Pada data detail ini bisa disetting menggunakan kunci index atau tidak, tergantung dari kebutuhan sistem yang ada; jika setiap transaksi tidak boleh ada kode barang yang kembar, maka bisa kita beri setting kunci index nya adalah: Nomor Penjualan dan Kode Barang.
  
  Satu lagi yang perlu diperhatikan dalam menyusun tabel data detail transaksi, berikan 1 field untuk menyimpan data urutan barang setiap transaksi, karena pada saat pengambilan data, maka Recordset yang anda dapatkan belum tentu akan terproses secara urut, maka harus kita sort dalam query (ORDER BY).
  

Sebagai gambaran data transaksi perhatikan gambar berikut dibawah ini:

Contoh Struktur Tabel dan Relasi

Berikut ini saya akan memberikan contoh struktur tabel transaksi penjualan dan master pendukungnya (Master Barang dan Master Customer).

Data struktur tabel database di atas, maka anda bisa dapatkan relasi tabel transaksi penjualan seperti gambar berikut ini:

Artikel ini hanyalah salah satu konsep yang saya kembangkan dan bukan merupakan satu-satu nya konsep, karena dalam penyusunan sebuah tabel untuk transaksi khususnya, bisa memunculkan banyak versi konsep.

Berdasarkan pengalaman saya, konsep ini yang paling relevan dalam mengelompokan data dan mudah digunakan baik dalam proses editing data transaksi sampai dengan penggunaan pembuatan laporan.

Pembahasan sebelumnya tentang Desain Sistem Database Stok Barang dapat anda lihat disini,

Cara Mudah Memperbaiki Database Suspect MS SQL

Pada saat program aplikasi anda telah berjalan di sebuah perusahaan, database adalah merupakan bagian yang paling penting untuk dilakukan perawatan dengan sempurna, misalkan dengan backup, mirror atau dengan cara alternatif lain. Oleh karena itu anda harus berhati-hati dan mempersiapkan kiat-kiat khusus dalam mengantisipasi, jika terjadi hal-hal yang tidak diinginkan.

Tetapi pada perjalanannya, ada saja permasalahan yang datang dan harus bisa anda atasi agar database masih tetap terjaga utuh. Salah satu permasalah yang terjadi adalah Database Suspect. Dalam kondisi tersebut, anda tidak bisa melakukan backup atau import database tersebut apalagi membuka database tersebut, sehingga anda sudah tidak bisa menggunakan database tersebut sama sekali.

Tetapi hal ini bisa diatasi dengan cara yang sangat sederhana, khususnya versi SQL Server 2005 dan setelahnya.

Penyebab Database Suspect adalah beberapa kemungkinan yaitu:

• Tidak ada cukup ruang penyimpanan disk yang tersedia untuk memulihkan database SQL Server saat startup SQL Server.
• Database tidak dapat dibuka, karena file fisik tidak dapat diakses atau tidak cukup memori atau ruang disk.
• File-file fisik database .LDF atau .MDF rusak atau corupt.
• Adanya gangguan listrik sehingga server mati mendadak dan SQL Server tidak sempat Shutdown dan mesin database masih dalam keadaan bekerja.

Berikut langkah-langkah cara mengatasi Database Suspect:

Pastikan bahwa anda sudah melakukan backup file database secara dedicated dengan cara mengcopy file .LDF dan .MDF dari folder Data ke folder backup yang aman. (terlebih dahulu matikan service (Stop) mesin database SQL Server).

• Buka Microsoft SQL Server Management Studio dan koneksi ke Server, lalu menuju ke database anda yang suspect.
• Click tombol Query untuk membuat sebuah query yang digunakan untuk mengatasi database suspect tersebut.
• Paste Query dibawah ini dan ganti [Database_Suspect_Anda] dengan nama database yang akan anda perbaiki.
  

  EXEC sp_resetstatus [Database_Suspect_Anda];
  ALTER DATABASE [Database_Suspect_Anda] SET EMERGENCY
  DBCC checkdb([Database_Suspect_Anda])
  ALTER DATABASE [Database_Suspect_Anda] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
  DBCC CheckDB ([Database_Suspect_Anda], REPAIR_ALLOW_DATA_LOSS)
  ALTER DATABASE [Database_Suspect_Anda] SET MULTI_USER

• Tekan tombol Execute, tunggu beberapa saat, maka database anda akan pulih seperti sediakala.

Demikian pengalaman saya dalam mengatasi database SQL suspect yang sederhana ini dan semoga bisa membantu anda mengatasi masalah.

Desain Sistem Database Stok Barang

Dalam melakukan pembuatan program aplikasi tentu penyusunan sebuah struktur tabel database yang baik dan benar sangatlah penting, hal itu dikarenakan beberapa alasan yang menjadi dasar yaitu, bahwa program aplikasi bisa berjalan dengan cepat pada saat penyimpanan data dan bisa mendapatkan laporan dengan cepat dan tepat, serta dapat bertahan lama tidak tergantung dari jumlah record yang berkembang.

Pada artikel ini saya akan memberikan contoh desain database yang berhubungan dengan proses Stok Barang. Contoh yang saya pakai menggunakan database SQL SERVER.

Secara sederhana kebutuhan akan informasi stok barang dalam database yang perlu untuk disimpan ada 3 jenis tabel yaitu:
- File Master Barang
- Data Stok Akhir Barang
- Data Mutasi Transaksi Barang

Untuk tabel yang lain sesuai dengan kebutuhan, misalkan apakah diperlukan data pelanggan (customer), apakah dibutuhkan data gudang (multi gudang) dan lain sebagainya, itu tergantung dari kebutuhan sistem yang anda susun tentunya.

Saya akan memberikan contoh dengan kebutuhan data yang sederhana, sehingga mudah difahami dan dapat anda kembangkan sesuai dengan kebutuhan sistem.

File Master Barang

Yang diperlukan dalam tabel ini adalah Kode Barang (Unique) dan informasi barang (Nama Barang, Satuan, HPP, Harga dll. sesuai kebutuhan). Berikut contoh struktur tabel filemasterbarang:

KodeBarang   String/Character Index
NamaBarang   String/Character
Satuan       String/Character
HPP          Currency/Money
HargaJual    Currency/Money

Pada dasarnya tabel File Master Barang ini hanya digunakan untuk menyimpan informasi data barang yang digunakan.

Data Stok Akhir Barang

Dalam tabel ini digunakan untuk menyimpan data stok akhir barang, sehingga anda bisa melakukan update atau mendapatkan stok akhir dengan cepat. Berikut contoh struktur tabel stokbarang:

KodeBarang   String/Character Index
KodeGudang   String/Character Index
StokAkhir    Integer/Real (sesuai dengan kebutuhan jenis stok barang berupa nilai bulat atau pecahan)

Akses atau pemrosesan tabel Data Stok Akhir Barang ini bisa digunakan Query atau Stored Procedure yang berfungsi untuk mengupdate data posisi stok akhir barang (baik bertambah atau berkurang) sesuai transaksi yang terjadi, yang secara sederhana dapat anda lihat dalam contoh Stored Procedure (SQL SERVER) berikut ini:

CREATE PROCEDURE [dbo].[SP_UPDATE_STOKAKHIR]
@KodeBarang  varchar(15),
@KodeGudang  varchar(5),
@Jumlah  real
AS
BEGIN
      UPDATE stokbarang SET Stokakhir = Stokakhir + @Jumlah
      WHERE Kodebarang = @Kodebarang AND Kodegudang = @Kodegudang
END

Baiklah mungkin ada pertanyaan, kenapa stok akhir tidak dituliskan dalam File Master Barang saja? Hal ini didasarkan pada alasan bahwa, jika anda menggunakan gudang stok barang lebih dari 1, maka anda tidak bisa meletakkannya pada File Master Barang, karena anda tidak bisa menggunakannya secara dinamis, misalkan jenis gudang bertambah; sehingga dengan desain database sistem ini, gudang bisa bertambah tanpa harus merubah struktur tabel.

Data Mutasi Barang

Dalam tabel ini berfungsi untuk menyimpan seluruh aktiftas transaksi yang terjadi, baik data stok barang masuk atau keluar dan tidak terbatas jenis transaksi yang digunakan dalam program aplikasi.

Memang setiap transaksi yang kita buat tentu detail dari jenis dan jumlah barang yang kita input sudah tersimpan dalam tabel transaksi, tetapi dalam hal ini alasan kenapa tetap disimpan dalam Data Mutasi Barang adalah, karena untuk kebutuhan laporan dan informasi mutasi barang, yaitu bisa cukup dengan menggunakan 1 tabel saja dan tidak perlu menggunakan banyak tabel sesuai dengan transaksi yang digunakan, sehingga jauh lebih efektif dan lebih cepat.

Berikut contoh struktur tabel mutasibarang:

JenisTransaksi   Integer (digunakan untuk menentukan jenis transaksi yang tersimpan, misalkan 0-Stok Opname, 1-Pembelian dst.)
NomorTransaksi   String/Character
TanggalTransaksi DateTime
KodeGudang       String/Character
KodeBarang       String/Character
Keterangan       String/Character
Masuk            Integer/Real (sesuai dengan kebutuhan jenis mutasi barang berupa nilai bulat atau pecahan)
Keluar           Integer/Real (sesuai dengan kebutuhan jenis mutasi barang berupa nilai bulat atau pecahan)

Dengan tersusunnya 3 tabel diatas, maka sudah lengkap akan kebutuhan sistem yang berhubungan dengan Stok dan Mutasi Barang, kita tinggal menambahkan tabel-tabel lain yang diperlukan dalam database sesuai dengan kebutuhan sistem, misalkan untuk kebutuhan transaksi stok opname, pembelian, penjualan, retur dan lain sebagainya (akan dibahas di artikel yang lain).

Dan dari struktur tabel diatas, maka kita bisa menyimpan data dan mengambil laporan secara histori sesuai dengan tanggal proses yang anda maksud, yaitu dengan cara digunakan sebagai parameter laporan. Sebagai contoh, saya akan berikan sebuah Stored Procedure (SQL Server) yang berfungsi untuk mengambil laporan daftar stok secara hitoris pada tanggal tertentu.

CREATE PROCEDURE [dbo].[SP_RPT_STOKBARANG]
@TanggalLaporan datetime
AS
BEGIN
SELECT S.KodeBarang, S.KodeGudang, B.NamaBarang, B.Satuan, B.HPP, B.HargaJual, StokAkhir = SUM(D.Stokakhir) - ISNULL((SELLECT SUM(M.Masuk) FROM MUTASIBARANG M WHERE M.KodeBarang = S.KodeBarang AND M.KodeGudang = S.KodeGudang AND M.TanggalTransaksi>@TanggalLaporan),0) + ISNULL((SELLECT SUM(M.Keluar) FROM MUTASIBARANG M WHERE M.KodeBarang = S.KodeBarang AND M.KodeGudang = S.KodeGudang AND
M.TanggalTransaksi>@TanggalLaporan),0)
FROM STOKBARANG S
INNER JOIN FILEMASTERBARANG B ON S.KodeBarang = B.KodeBarang
GROUP BY S.KodeBarang, S.KodeGudang, B.NamaBarang, B.Satuan, B.HPP, B.HargaJual
END

Dari stored procedure di atas, maka kita bisa mendapatkan daftar stok akhir barang secara histori tergantung dari tanggal laporan yang ditentukan sebagai parameter, sehingga program laporan yang anda susun jauh lebih baik.

Demikian sekedar berbagi ilmu dari pengalaman saya dalam menyusun sebuah sistem database stok barang, semoga bermanfaat dan jika anda mempunyai pertanyaan atau ingin diskusi, anda bisa memberikan komentar pada artikel ini atau mengirim email kepada saya.

Kehebatan Local Variabel Saat Mengganti Tugas Parameters di Store Procedure SQL Server

Berdasarkan pengalaman saya, permasahalan pada Database SQL Server dengan jumlah data yang sangat besar (Hugh Data), pengambilan data mennggunakan Stored Procedure dengan Parameternya akan menimbulkan masalah baru, yaitu pada sisi performa kecepatan; Hal ini karena tidak dilakukan dengan cara yang tepat dalam menggunakan unsur yang tersedia; mari kita coba buktikan!

Dalam menjelaskan pembuktian ini, saya akan menggunakan cara terbalik; saya akan memberikan contoh SQL Stored Procedure dengan Clause SELECT untuk mengambil data dari 2 tabel JOIN berikut parameter yang digunakan; selanjutnya bandingkan jika seandainya tugas dari Parameters tersebut di ganti oleh Local Variables dan lihat hasilnya, sangat luar biasa -- akan lebih terlihat dengan nyata jika anda menggunakan database besar.

Parameter

Pada saat pertama kali sebuah Stored Procedure di Execute, SQL akan melihat data input Parameter akan digunakan sebagai pedoman dalam merencanakan sebuah permintaan pada Query yang digunakan. SQL Server akan selalu mengambil data input Peremeter tersebut setiap baris proses yang hasilkan (baik berfungsi sebagai data input atau sebagai filter sebuah pencarian Recordset), sehingga jika data yang ada pada database tersebut besar dan filter pencarian komplek akan mengakibatkan kinerja akan melambat.

Local Variabel

Penggunaan Local variabel dalam SQL Server, nilai yang digunakan dalam Query akan di anggap seperti sebuah konstanta; sehingga pekerjaan SQL Server cukup dilakukan dalam mengambil data nilai dari Local Variabel dan akan memproses query tersebut seolah-olah menggunakan sebuah data input berupa konstanta.

Dalam contoh ini saya menggunakan SQL Server 2005 dan saya gunakan database yang telah tersedia yaitu "AdventureWorksDW", mari kita perhatikan contoh query pada stored procedure berikut ini:

USE [AdventureWorksDW]
CREATE PROCEDURE [dbo].[SP_TEST_WITH_PARAMETER]
@FullDateAlternateKey1 varchar(10),
@FullDateAlternateKey2 varchar(10)
AS
BEGIN
SELECT * FROM FactInternetSales RS
INNER JOIN DimTime DT ON RS.DueDateKey=DT.TimeKey
INNER JOIN DimCustomer C ON RS.CustomerKey=C.CustomerKey
INNER JOIN DimGeography DG ON C.GeographyKey=DG.GeographyKey
INNER JOIN DimSalesTerritory ST ON DG.SalesTerritoryKey=ST.SalesTerritoryKey
INNER JOIN DimProduct P ON RS.ProductKey=P.ProductKey
INNER JOIN DimProductSubcategory SC ON P.ProductSubcategoryKey=SC.ProductSubcategoryKey
INNER JOIN DimProductCategory PC ON SC.ProductCategoryKey= PC.ProductCategoryKey
WHERE FullDateAlternateKey BETWEEN @FullDateAlternateKey1 AND @FullDateAlternateKey2
END

Jalankan Stored Procedure diatas dalam Query tools dengan perintah:
EXECUTE SP_TEST_WITH_PARAMETER '01/01/2001', '01/01/2005'.

Bandingkan dengan Stored Procedure seperti berikut ini:

USE [AdventureWorksDW]
PROCEDURE [dbo].[SP_TEST_NO_PARAMETER]
AS
BEGIN
  SELECT * FROM FactInternetSales RS
  INNER JOIN DimTime DT ON RS.DueDateKey=DT.TimeKey
  INNER JOIN DimCustomer C ON RS.CustomerKey=C.CustomerKey
  INNER JOIN DimGeography DG ON C.GeographyKey=DG.GeographyKey
  INNER JOIN DimSalesTerritory ST ON DG.SalesTerritoryKey=ST.SalesTerritoryKey
  INNER JOIN DimProduct P ON RS.ProductKey=P.ProductKey
  INNER JOIN DimProductSubcategory SC ON P.ProductSubcategoryKey=SC.ProductSubcategoryKey
  INNER JOIN DimProductCategory PC ON SC.ProductCategoryKey= PC.ProductCategoryKey
  WHERE FullDateAlternateKey BETWEEN '01/01/2001' AND '01/01/2005'
END

Jalankan dalam Query tools dengan perintah:
EXECUTE SP_TEST_NO_PARAMETER

Maka Stored Procedure yang terakhir yang akan lebih cepat performance nya dibanding Stored Procedure diatasnya, walaupun data yang diambil (recordset) adalah sama.

Nah sekarang masalahnya adalah, dalam Stored Procedure tersebut kita menggunakan sebuah pedoman yang akan digunakan sebagai permintaan Query, yaitu InvoiceID dan ItemID; sehingga kita harus menggunakan Parameter. Permasalahan ini terpecahkan dengan cara menggunakan Local Variable (seperti dijelaskan di atas).

Tambahkan Local Variable dan definisikan Parameter yang dilewatkan kedalamnya, maka selanjutnya anda akan mendapatkan hasil yang sangat luar biasa performanya; seperti contoh berikut ini :

USE [AdventureWorksDW]
CREATE PROCEDURE [dbo].[SP_TEST_WITH_LOCAL_VARIABLE]

@FullDateAlternateKey1 varchar(10),
@FullDateAlternateKey2 varchar(10)
AS
SET NOCOUNT ON
DECLARE @LOCAL_FullDateAlternateKey1 varchar(10)
DECLARE @LOCAL_FullDateAlternateKey2 varchar(10)
SET @LOCAL_FullDateAlternateKey1=@FullDateAlternateKey1
SET @LOCAL_FullDateAlternateKey2=@FullDateAlternateKey2
BEGIN
SELECT * FROM FactInternetSales RS
  INNER JOIN DimTime DT ON RS.DueDateKey=DT.TimeKey
  INNER JOIN DimCustomer C ON RS.CustomerKey=C.CustomerKey
  INNER JOIN DimGeography DG ON C.GeographyKey=DG.GeographyKey
  INNER JOIN DimSalesTerritory ST ON DG.SalesTerritoryKey=ST.SalesTerritoryKey
  INNER JOIN DimProduct P ON RS.ProductKey=P.ProductKey
  INNER JOIN DimProductSubcategory SC ON P.ProductSubcategoryKey=SC.ProductSubcategoryKey
  INNER JOIN DimProductCategory PC ON SC.ProductCategoryKey= PC.ProductCategoryKey
  WHERE FullDateAlternateKey BETWEEN @LOCAL_FullDateAlternateKey1 AND @LOCAL_FullDateAlternateKey2
END

SET NOCOUNT 

SET NOCOUNT {ON | OFF} adalah setting dalam sebuah Query Clause SELECT yang berfungsi untuk mengembalikan nilai balik berpa jumlah baris Recordset yang dihasilkan oleh Stored Procedure; When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned. Jika status ON, maka tentu performance Query lebih cepat; tetapi itu semua tergantung dari kebutuhan sistem, apakah memang memerlukan nilai balik jumlah baris Recordset atau tidak.

Pada artikel ini saya telah membuktikan dan berdasarkan pengalaman saya saat pekerjaan saya sebagai programmer yang mengharuskan bisa mengoptimalkan pengelolaan data yang terus membesar; saran saya, jangan meremehkan penulisan Query yang benar walaupun data kecil, karena suatu saat itulah yang akan memberi masalah kepada anda; saya harap artikel ini bermanfaat.