Paydjo.net newbie on the blog

Ada kalanya kita diminta menampilkan data ke dalam bentuk tampilan seperti gambar di atas. Ada teman yang sengaja menyimpan data tersebut apa adanya. Jadi dia membuat field tabel sebagaimana adanya.

 CREATE TABLE `pivot_plain` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `nama` varchar(255) collate latin1_general_ci NOT NULL,
   `delphi` tinyint(1) unsigned NOT NULL default '0',
   `vb` tinyint(1) unsigned NOT NULL default '0',
   `asp` tinyint(1) unsigned NOT NULL default '0',
   `php` tinyint(1) unsigned NOT NULL default '0',
   `java` tinyint(3) unsigned NOT NULL default '0',
   PRIMARY KEY  (`id`)
 ) ENGINE=MyISAM
	

Tidak ada salahnya menyimpan data dalam bentuk seperti itu. Tapi ketika kategorinya ditambah, tentu pusing dan riskan juga kalo kita usik2 struktur tabel lagi

OK, kita cb sedikit normalisasi data tersebut. Anggep aja setelah semedi kita dapet wangsit agar dipetakan jadi 3 tabel:
1. tabel pivot_user untuk menyimpan data-data user

 CREATE TABLE `pivot_user` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `nama` varchar(255) collate latin1_general_ci NOT NULL,
   PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB
	

2. tabel pivot_user_category untuk menyimpan data-data kategori

 CREATE TABLE `pivot_category` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `nama` varchar(50) collate latin1_general_ci NOT NULL,
   PRIMARY KEY  (`id`)
 ) ENGINE=InnoDB
	

3. tabel pivot_user_category untuk menyimpan data transaksi antara user dan category

 CREATE TABLE `pivot_user_category` (
   `id` int(10) unsigned NOT NULL auto_increment,
   `user_id` int(10) unsigned NOT NULL,
   `cat_id` int(10) unsigned NOT NULL,
   PRIMARY KEY  (`id`),
   UNIQUE KEY `user_id` (`user_id`,`cat_id`),
   KEY `FK_pivot_user_category` (`cat_id`),
   CONSTRAINT `pivot_user_category_ibfk_2` FOREIGN KEY (`cat_id`) REFERENCES `pivot_user_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
   CONSTRAINT `pivot_user_category_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `pivot_user_category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
 ) ENGINE=InnoDB
	

dan kita masukkan data-data sementara:

 insert into `pivot_user`(`id`,`nama`) values (1,'ahmad');
 insert into `pivot_user`(`id`,`nama`) values (2,'budi');
 insert into `pivot_user`(`id`,`nama`) values (3,'catur');
 insert into `pivot_user`(`id`,`nama`) values (4,'doni');
 insert into `pivot_user`(`id`,`nama`) values (5,'eko');
	
 insert into `pivot_category`(`id`,`nama`) values (1,'delphi');
 insert into `pivot_category`(`id`,`nama`) values (2,'vb');
 insert into `pivot_category`(`id`,`nama`) values (3,'php');
 insert into `pivot_category`(`id`,`nama`) values (4,'asp');
 insert into `pivot_category`(`id`,`nama`) values (5,'java');
	
 insert into `pivot_user_category`(`id`,`user_id`,`cat_id`) values (1,1,1);
 insert into `pivot_user_category`(`id`,`user_id`,`cat_id`) values (2,1,3);
 insert into `pivot_user_category`(`id`,`user_id`,`cat_id`) values (3,2,2);
 insert into `pivot_user_category`(`id`,`user_id`,`cat_id`) values (4,2,4);
 insert into `pivot_user_category`(`id`,`user_id`,`cat_id`) values (5,3,5);
 insert into `pivot_user_category`(`id`,`user_id`,`cat_id`) values (6,5,1);
 insert into `pivot_user_category`(`id`,`user_id`,`cat_id`) values (7,5,5);
	

Popularity: 2% [?]

Hlm: 1 2 3

Artikel terkait:

No related posts