Hirarki data di MySQL

Ada beberapa programmer yang dihadapkan dengan kasus membuat hirarki data dalam SQL database. Hirarki data mempunyai relasi parent-child, namun bersifat rekursif. Tidak natural dalam konsep relasional database. Hirarki data adalah data collection yang tiap itemnya mempunyai single parent atau tidak sama sekali dan mempunyai satu, dua atau lebih children. Hirarki data dapat kita jumpai dalam banyak aplikasi database, termasuk forum dan mailing list, diagram oraganisasi bisnis, produk, multi level marketing dan kode akun dalam accounting. dalam postingan ini, saya mencoba untuk membuat data hirarki dengan kasus membuat kode akun accounting. Contoh yang saya buat, kurang lebih sama dengan kasus-kasus yang lain seperti yang saya jelaskan diawal.

Sebelumnya, kita buat dulu tabel dengan nama tabelnya account (sekalian asaya buat fungsi CREATE dan INSERT jadi tinggal diikuti aja..)๐Ÿ˜€

CREATE TABLE `account` (
  `accountId` int(11) NOT NULL AUTO_INCREMENT,
  `parentId` int(11) DEFAULT NULL,
  `accountName` char(20) NOT NULL,
  PRIMARY KEY (`accountId`)
);

INSERT INTO `account` (`accountId`, `parentId`, `accountName`) VALUES
  (1, 0, 'Harta'),  (2, 1, 'Harta Lancar'),  (3, 2, 'Kas'),
  (4, 2, 'Bank'),  (5, 2, 'Piutang Teman'),  (6, 1, 'Harta Tetap'),
  (7, 6, 'Kendaraan'),  (8, 7, 'Vario Matic'),
  (9, 6, 'Product Electronic'),  (10, 6, 'Investasi'),
  (11, 0, 'Pendapatan'),  (12, 11, 'Gaji'),
  (13, 12, 'Gaji gue'),  (14, 11, 'Hasil Investasi');

 SELECT * FROM account;

 +-----------+----------+--------------------+
 | accountId | parentId | accountName        |
 +-----------+----------+--------------------+
 |         1 |        0 | Harta              |
 |         2 |        1 | Harta Lancar       |
 |         3 |        2 | Kas                |
 |         4 |        2 | Bank               |
 |         5 |        2 | Piutang Teman      |
 |         6 |        1 | Harta Tetap        |
 |         7 |        6 | Kendaraan          |
 |         8 |        7 | Vario Matic        |
 |         9 |        6 | Product Electronic |
 |        10 |        6 | Investasi          |
 |        11 |        0 | Pendapatan         |
 |        12 |       11 | Gaji               |
 |        13 |       12 | Gaji gue           |
 |        14 |       11 | Hasil Investasi    |
 +-----------+----------+--------------------+
 14 rows in set (0.00 sec)


Pada hasil dari SELECT diatas, bahwa tiap item pada tabel berisi parentId yang menunjuk ke parentnya. pada baris pertama dan 11 berisi 0. Ini berarti row tersebut tidak mempunyai parent atau bisa disebut sebagai parent itu sendiri. Model tabel diatas bisa diterjemahkan Vario Matic adalah child dari Kendaraan, Kendaraan adalah child dari Harta Tetap dan Harta Tetap adalah child dari Harta yang mana tidak mempunyai parent lagi alias parent itu sendiri. Walaupun tabel diatas sudah dapat diterjemahkan, tetapi masih dibutuhkan suatu query statement agar tampilan data collection lebih mudah untuk dilihat/dimengerti. Untuk menampilkan data tersebut dapat kita gunakan LEFT JOIN :

mysql> SELECT a1.accountName AS Parent, a2.accountName as Child1,
 ->   a3.accountName as Child2, a4.accountName as Child3
 ->   FROM account AS a1
 ->   LEFT JOIN account AS a2 ON a2.parentId = a1.accountId
 ->   LEFT JOIN account AS a3 ON a3.parentId = a2.accountId
 ->   LEFT JOIN account AS a4 ON a4.parentId = a3.accountId
 ->   WHERE a1.parentId =0;
+------------+-----------------+--------------------+-------------+
| Parent     | Child1          | Child2             | Child3      |
+------------+-----------------+--------------------+-------------+
| Harta      | Harta Lancar    | Kas                | NULL        |
| Harta      | Harta Lancar    | Bank               | NULL        |
| Harta      | Harta Lancar    | Piutang Teman      | NULL        |
| Harta      | Harta Tetap     | Kendaraan          | Vario Matic |
| Harta      | Harta Tetap     | Product Electronic | NULL        |
| Harta      | Harta Tetap     | Investasi          | NULL        |
| Pendapatan | Gaji            | Gaji gue           | NULL        |
| Pendapatan | Hasil Investasi | NULL               | NULL        |
+------------+-----------------+--------------------+-------------+
8 rows in set (0.00 sec)

Untuk menampilkan data account yang paling bawah atau account yang tidak mempunyai child, saya menggunakan LEFT JOIN:

mysql> SELECT aOne.accountName FROM
-> account AS aOne LEFT JOIN account as aTwo
 -> ON aOne.accountId = aTwo.parentId
 -> WHERE aTwo.accountId IS NULL;
+--------------------+
| accountName        |
+--------------------+
| Kas                |
| Bank               |
| Piutang Teman      |
| Vario Matic        |
| Product Electronic |
| Investasi          |
| Gaji gue           |
| Hasil Investasi    |
+--------------------+
8 rows in set (0.00 sec)

Namun, masih ada beberapa yang menurut saya kurang dari postingan ini. Bagaimana membuat query yang dinamis sehingga sewaktu menambahkan child baru tidak harus merubah struktur query yang sudah di buat.๐Ÿ˜€ akan saya posting pada kesempatan yang akan datang.๐Ÿ™‚

4 Responses to “Hirarki data di MySQL”

  1. Adi Says:

    Mas gimana cara membuat code query bisa tampil diweb ini

  2. Agus Permana Says:

    Terima kasih mas sharing nya, nambah pertanyaan nih.
    Dari data hierarchy diatas, gimana querynya bila data yang dinginkan disimpan dalam satu kolom jadi seperti ini :
    HARTA
    PENDAPATAN
    HARTALANCAR
    HARTATETAP
    GAJI
    HASIL INVESTASI
    KAS
    BANK
    PIUTANGTEMAN
    KENDARAAN
    dst-nya…
    (saya ingin menampilkan semua anggota dari yang parentID-nya =0)
    Terima kasih

  3. casper Says:

    Om mau nanya, Jika ditambah satu kolom “saldo” dan masing masing item ada saldonya,
    Bagaimna menghitung nilai saldo tersebut agar bisa ter SUM pada parentID masing-masing ??


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: