Rancangan Database Aplikasi Transaksi Penjualan

Entity Relationship Diagram ( ERD )

Skema Relasi

Tabel Barang

CREATE TABLE `barang` (
  `kode_barang` int(6) NOT NULL AUTO_INCREMENT,
  `nama_barang` varchar(25) NOT NULL,
  `kategori` varchar(25) NOT NULL,
  `harga_beli` int(10) unsigned DEFAULT NULL,
  `harga_jual` int(11) unsigned DEFAULT NULL,
  `stok` int(11) DEFAULT NULL,
  `kode_satuan` varchar(25) NOT NULL,
  `foto` blob,
  `status` tinyint(1) DEFAULT '1',
  `tanggal_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`kode_barang`,`nama_barang`),
  KEY `barang_ibfk_1` (`kode_satuan`),
  KEY `barang_ibfk_2` (`kategori`),
  CONSTRAINT `kategori` FOREIGN KEY (`kategori`) REFERENCES `kategori` (`nama_kategori`) ON UPDATE CASCADE,
  CONSTRAINT `satuan` FOREIGN KEY (`kode_satuan`) REFERENCES `satuan` (`kode_satuan`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

Tabel Satuan

CREATE TABLE `satuan` (
  `kode_satuan` varchar(25) NOT NULL,
  `keterangan` varchar(15) NOT NULL,
  `status` int(11) DEFAULT '1',
  PRIMARY KEY (`kode_satuan`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tabel Kategori

CREATE TABLE `kategori` (
  `nama_kategori` varchar(25) NOT NULL,
  `status` int(11) DEFAULT '1',
  PRIMARY KEY (`nama_kategori`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tabel Karyawan

CREATE TABLE `karyawan` (
  `nik` int(11) NOT NULL AUTO_INCREMENT,
  `nama` varchar(25) NOT NULL,
  `jenis_kelamin` varchar(1) NOT NULL,
  `tempat_lahir` varchar(20) NOT NULL,
  `tanggal_lahir` date DEFAULT NULL,
  `alamat` varchar(25) NOT NULL,
  `no_telepon` varchar(15) DEFAULT NULL,
  `foto` blob,
  `tanggal_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` int(11) DEFAULT '1',
  PRIMARY KEY (`nik`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

Tabel Supplier

CREATE TABLE `supplier` (
  `kode_supplier` int(11) NOT NULL AUTO_INCREMENT,
  `nama_supplier` varchar(25) DEFAULT NULL,
  `alamat` varchar(25) DEFAULT NULL,
  `telepon` varchar(15) DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  `website` varchar(15) DEFAULT NULL,
  `foto` blob,
  `status` tinyint(1) DEFAULT '1',
  `tanggal_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`kode_supplier`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Tabel User

CREATE TABLE `user` (

  `username` varchar(20) NOT NULL,
  `password` varchar(20) NOT NULL,
  `nik` int(11) NOT NULL,
  `tanggal_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `level_user` varchar(10) DEFAULT 'user',
  `status` int(11) DEFAULT '1',
  PRIMARY KEY (`username`),
  UNIQUE KEY `kunci_unik_user` (`username`,`nik`),
  KEY `user_ibfk_1` (`nik`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`nik`) REFERENCES `karyawan` (`nik`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tabel Transaksi Penjualan

CREATE TABLE `trans_jual` (
  `kode_trans_jual` int(11) NOT NULL AUTO_INCREMENT,
  `diskon` float unsigned NOT NULL,
  `total` float unsigned NOT NULL,
  `username` varchar(20) DEFAULT NULL,
  `tanggal_trans_jual` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`kode_trans_jual`),
  KEY `trans_jual_ibfk_1` (`username`),
  CONSTRAINT `trans_jual_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

Tabel Detail Transaksi Penjualan

CREATE TABLE `detail_trans_jual` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `kode_trans_jual` int(11) NOT NULL,
  `kode_barang` int(11) NOT NULL,
  `harga_jual` float unsigned DEFAULT '0',
  `jumlah` float unsigned DEFAULT '0',
  `sub_total` float unsigned DEFAULT '0',
  PRIMARY KEY (`no`),
  KEY `detail_trans_jual_ibfk_1` (`kode_barang`),
  KEY `detail_trans_jual_ibfk_2` (`kode_trans_jual`),
  CONSTRAINT `detail_trans_jual_ibfk_1` FOREIGN KEY (`kode_trans_jual`) REFERENCES `trans_jual` (`kode_trans_jual`) ON UPDATE CASCADE,
  CONSTRAINT `detail_trans_jual_ibfk_2` FOREIGN KEY (`kode_barang`) REFERENCES `barang` (`kode_barang`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1;

Tabel Trasnsaksi Pembelian

CREATE TABLE `trans_beli` (
  `kode_trans` varchar(6) NOT NULL,
  `kode_suplier` varchar(6) DEFAULT NULL,
  `username` varchar(20) NOT NULL,
  `total` float unsigned NOT NULL DEFAULT '0',
  `tanggal_trans_beli` date DEFAULT NULL,
  `tanggal_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`kode_trans`),
  KEY `trans_beli_ibfk_1` (`username`),
  KEY `trans_beli_ibfk_2` (`kode_suplier`),
  CONSTRAINT `trans_beli_ibfk_1` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON UPDATE CASCADE,
  CONSTRAINT `trans_beli_ibfk_2` FOREIGN KEY (`kode_suplier`) REFERENCES `suplier` (`kode_suplier`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Tabel Detail Transaksi Pembelian

CREATE TABLE `detail_trans_beli` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `kode_trans_beli` int(11) NOT NULL,
  `kode_barang` int(11) NOT NULL,
  `harga_beli` float unsigned DEFAULT '0',
  `jumlah` float unsigned DEFAULT '0',
  `sub_total` float unsigned DEFAULT '0',
  PRIMARY KEY (`no`),
  KEY `detail_trans_beli_ibfk_2` (`kode_barang`),
  KEY `detail_trans_beli_ibfk_1` (`kode_trans_beli`),
  CONSTRAINT `detail_trans_beli_ibfk_1` FOREIGN KEY (`kode_trans_beli`) REFERENCES `trans_beli` (`kode_trans`) ON UPDATE CASCADE,
  CONSTRAINT `detail_trans_beli_ibfk_2` FOREIGN KEY (`kode_barang`) REFERENCES `barang` (`kode_barang`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;