[**Database**](http://pojokprogrammer.net/tags/database) [**Tips**](http://pojokprogrammer.net/tags/tips-dan-trik) | Semua programmer yang berkonsentrasi di pengembangan aplikasi perkantoran (*office application*) pasti pernah berhadapan dengan data hirarki (***hierarchical data***) ke dalam database relasional (*relational database*) seperti MySQL. RDBMS sendiri tidak diperuntukkan untuk menyimpan data hirarki karena relasi antar table bukanlah relasi hirarki. Namun kita bisa merepresentasikan data hirarki menggunakan relasi ***self-reference***, atau relasi *foreign key* ke table yang sama.
**Data Hirarki** adalah sekumpulan data yang masing-masingnya memiliki satu ***parent*** dengan pengecualian untuk data ***root***. Masing-saing data dalam hirarki juga bisa memilik satu atau beberapa ***child***, atau tidak memiliki child untuk data pada level terendah, yang biasa disebut ***leaf***. Data hirarki yang umumnya terdapat dalam aplikasi perkantoran, seperti data departemen, cabang, kategori barang, chart of accounts, dan sebagainya. Dalam artikel ini kita akan mencoba menggunakan data ***Chart of Accounts*** atau ***Perkiraan*** yang sangat diperlukan dalam sebuah aplikasi akuntansi (*Accounting System*), seperti tampak pada gambar di bawah ini,
### Adjacency List Model
**Cara termudah** merepresentasikan data hirarki di dalam database relasional adalah dengan menggunakan model ***Senarai Ketetanggaan*** (***Adjacency List***). dalam model senarai ketetanggaan ini, setiap record memiliki pointer ke *parent* dari record tersebut. Khusus untuk *root* atau data paling atas tidak memiliki parent maka kita set NULL untuk *parent* dari *root*. Untuk contoh kasus kita, maka yang menjadi ***root*** adalah record ***Assets***, ***Liabilities***, dan ***Equities***.
**Keuntungan** penggunaan senarai ketetanggaan adalah kesederhanaannya, karena kita bisa dengan mudah melihat *parent* dari *Petty Cash* adalah *Current Assets*, yang sekaligus merupakan *child* dari *Assets*. Berikut ini adalah DDL dan DML yang kita gunakan sebagai contoh kasus.
CREATE TABLE accounts
(
id integer auto_increment,
code varchar(255) NOT NULL,
name varchar(255) NOT NULL,
parent_id integer,
PRIMARY KEY (id),
FOREIGN KEY (parent_id)
REFERENCES accounts (id)
ON UPDATE CASCADE ON DELETE RESTRICT
);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1000, '1000', 'Assets', NULL);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1100, '1100', 'Current Assets', 1000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1110, '1110', 'Petty Cash', 1100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1120, '1120', 'Savings Account', 1100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1130, '1130', 'Deposits Account', 1100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1150, '1150', 'Accounts Receivables', 1100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1200, '1200', 'Fixed Assets', 1000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1210, '1210', 'Furnitures', 1200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1220, '1220', 'Equipments', 1200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1230, '1230', 'Vehicles', 1200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1250, '1250', 'Buildings', 1200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1900, '1900', 'Other Assets', 1000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1910, '1910', 'Patents', 1900);
INSERT INTO accounts (id, code, name, parent_id) VALUES(1990, '1990', 'Other Assets', 1900);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2000, '2000', 'Liabilities', NULL);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2100, '2100', 'Current Liabilities', 2000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2110, '2110', 'Accrued Expenses', 2100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2120, '2120', 'Accounts Payable', 2100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2130, '2130', 'Tax Payable', 2100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2200, '2200', 'Long-Term Liabilities', 2000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2210, '2210', 'Equipments Payable', 2200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2220, '2220', 'Vehicles Payable', 2200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2240, '2240', 'Buildings Payable', 2200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(2250, '2250', 'Loans Payable', 2200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3000, '3000', 'Equity', NULL);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3100, '3100', 'Capital', 3000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3110, '3110', 'Founders Capital', 3100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3120, '3120', 'Invenstors Capital', 3100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3190, '3190', 'Others Capital', 3100);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3200, '3200', 'Earnings', 3000);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3210, '3210', 'Retained Earnings', 3200);
INSERT INTO accounts (id, code, name, parent_id) VALUES(3220, '3220', 'Current Earnings', 3200);
### Membaca Keseluruhan Hirarki
Hal **yang pasti kita hadapi** saat mengelola data hirarki adalah menampilkan keseluruhan data, biasanya berbentuk ***tree*** dengan tambahan indentasi untuk level yang lebih rendah. Cara paling mudah untuk mencapai hal ini di MySQL adalah dengan menggunakan ***self-join***. Berikut ini **query hirarki** (***hierarchical query***) untuk mendapatkan *tree* ***Assets***
SELECT t1.id, t1.code
, t1.name AS name_level1, t2.name as name_level2
, t3.name AS name_level3, t4.name as name_level4
FROM accounts AS t1
LEFT JOIN accounts AS t2 ON t2.parent_id = t1.id
LEFT JOIN accounts AS t3 ON t3.parent_id = t2.id
LEFT JOIN accounts AS t4 ON t4.parent_id = t3.id
WHERE t1.name = 'Assets'
ORDER BY t1.code, t2.code, t3.code, t4.code
Hasilnya akan tampak seperti ini
+------+------+-------------+----------------+----------------------+-------------+
| id | code | name_level1 | name_level2 | name_level3 | name_level4 |
+------+------+-------------+----------------+----------------------+-------------+
| 1000 | 1000 | Assets | Current Assets | Petty Cash | NULL |
| 1000 | 1000 | Assets | Current Assets | Savings Account | NULL |
| 1000 | 1000 | Assets | Current Assets | Deposits Account | NULL |
| 1000 | 1000 | Assets | Current Assets | Accounts Receivables | NULL |
| 1000 | 1000 | Assets | Fixed Assets | Furnitures | NULL |
| 1000 | 1000 | Assets | Fixed Assets | Equipments | NULL |
| 1000 | 1000 | Assets | Fixed Assets | Vehicles | NULL |
| 1000 | 1000 | Assets | Fixed Assets | Buildings | NULL |
| 1000 | 1000 | Assets | Other Assets | Patents | NULL |
| 1000 | 1000 | Assets | Other Assets | Other Assets | NULL |
+------+------+-------------+----------------+----------------------+-------------+
10 rows in set (0.00 sec)
### Menambahkan *Level* dan *Path*
Jika kita ingin menambahkan kolom yang berisikan level dari masing-masing record, maka kita harus lebih kreatif dalam membangun ***Hierarchical Query*** ini. Karena MySQL belum mendukung ***Common Table Expression*** untuk melakukan ***Recursive Query***, maka yang perlu kita lakukan adalah melakukan query untuk masing-masing level dan kemudian menggabungkannya menggunakan **UNION**. Perhatikan dalam query kali ini kita menggunakan **INNER JOIN**
-- Level 1
SELECT t1.id, t1.code, t1.name, 1 AS level
, t1.name AS name_level1, null as name_level2
, null as name_level3, null as name_level4
, t1.code AS path
FROM accounts AS t1
WHERE t1.parent_id is null
-- Level 2
UNION ALL
SELECT t2.id, t2.code, t2.name, 2 AS level
, t1.name AS name_level1, t2.name as name_level2
, null as name_level3, null as name_level4
, concat(t1.code,'/',t2.code) AS path
FROM accounts AS t1
JOIN accounts AS t2 ON t2.parent_id = t1.id
WHERE t1.parent_id is null
-- Level 3
UNION ALL
SELECT t3.id, t3.code, t3.name, 3 AS level
, t1.name AS name_level1, t2.name as name_level2
, t3.name AS name_level3, null as name_level4
, concat(t1.code,'/',t2.code,'/',t3.code) AS path
FROM accounts AS t1
JOIN accounts AS t2 ON t2.parent_id = t1.id
JOIN accounts AS t3 ON t3.parent_id = t2.id
WHERE t1.parent_id is null
-- Level 3
UNION ALL
SELECT t4.id, t4.code, t4.name, 4 AS level
, t1.name AS name_level1, t2.name as name_level2
, t3.name AS name_level3, t4.name as name_level4
, concat(t1.code,'/',t2.code,'/',t3.code,'/',t4.code) AS path
FROM accounts AS t1
JOIN accounts AS t2 ON t2.parent_id = t1.id
JOIN accounts AS t3 ON t3.parent_id = t2.id
JOIN accounts AS t4 ON t4.parent_id = t3.id
WHERE t1.parent_id is null
-- Ordering Result
ORDER BY path
Hasilnya akan tampak seperti ini
+------+------+-----------------------+-------+-------------+-----------------------+----------------------+-------------+----------------+
| id | code | name | level | name_level1 | name_level2 | name_level3 | name_level4 | path |
+------+------+-----------------------+-------+-------------+-----------------------+----------------------+-------------+----------------+
| 1000 | 1000 | Assets | 1 | Assets | NULL | NULL | NULL | 1000 |
| 1100 | 1100 | Current Assets | 2 | Assets | Current Assets | NULL | NULL | 1000/1100 |
| 1110 | 1110 | Petty Cash | 3 | Assets | Current Assets | Petty Cash | NULL | 1000/1100/1110 |
| 1120 | 1120 | Savings Account | 3 | Assets | Current Assets | Savings Account | NULL | 1000/1100/1120 |
| 1130 | 1130 | Deposits Account | 3 | Assets | Current Assets | Deposits Account | NULL | 1000/1100/1130 |
| 1150 | 1150 | Accounts Receivables | 3 | Assets | Current Assets | Accounts Receivables | NULL | 1000/1100/1150 |
| 1200 | 1200 | Fixed Assets | 2 | Assets | Fixed Assets | NULL | NULL | 1000/1200 |
| 1210 | 1210 | Furnitures | 3 | Assets | Fixed Assets | Furnitures | NULL | 1000/1200/1210 |
| 1220 | 1220 | Equipments | 3 | Assets | Fixed Assets | Equipments | NULL | 1000/1200/1220 |
| 1230 | 1230 | Vehicles | 3 | Assets | Fixed Assets | Vehicles | NULL | 1000/1200/1230 |
| 1250 | 1250 | Buildings | 3 | Assets | Fixed Assets | Buildings | NULL | 1000/1200/1250 |
| 1900 | 1900 | Other Assets | 2 | Assets | Other Assets | NULL | NULL | 1000/1900 |
| 1910 | 1910 | Patents | 3 | Assets | Other Assets | Patents | NULL | 1000/1900/1910 |
| 1990 | 1990 | Other Assets | 3 | Assets | Other Assets | Other Assets | NULL | 1000/1900/1990 |
| 2000 | 2000 | Liabilities | 1 | Liabilities | NULL | NULL | NULL | 2000 |
| 2100 | 2100 | Current Liabilities | 2 | Liabilities | Current Liabilities | NULL | NULL | 2000/2100 |
| 2110 | 2110 | Accrued Expenses | 3 | Liabilities | Current Liabilities | Accrued Expenses | NULL | 2000/2100/2110 |
| 2120 | 2120 | Accounts Payable | 3 | Liabilities | Current Liabilities | Accounts Payable | NULL | 2000/2100/2120 |
| 2130 | 2130 | Tax Payable | 3 | Liabilities | Current Liabilities | Tax Payable | NULL | 2000/2100/2130 |
| 2200 | 2200 | Long-Term Liabilities | 2 | Liabilities | Long-Term Liabilities | NULL | NULL | 2000/2200 |
| 2210 | 2210 | Equipments Payable | 3 | Liabilities | Long-Term Liabilities | Equipments Payable | NULL | 2000/2200/2210 |
| 2220 | 2220 | Vehicles Payable | 3 | Liabilities | Long-Term Liabilities | Vehicles Payable | NULL | 2000/2200/2220 |
| 2240 | 2240 | Buildings Payable | 3 | Liabilities | Long-Term Liabilities | Buildings Payable | NULL | 2000/2200/2240 |
| 2250 | 2250 | Loans Payable | 3 | Liabilities | Long-Term Liabilities | Loans Payable | NULL | 2000/2200/2250 |
| 3000 | 3000 | Equity | 1 | Equity | NULL | NULL | NULL | 3000 |
| 3100 | 3100 | Capital | 2 | Equity | Capital | NULL | NULL | 3000/3100 |
| 3110 | 3110 | Founders Capital | 3 | Equity | Capital | Founders Capital | NULL | 3000/3100/3110 |
| 3120 | 3120 | Invenstors Capital | 3 | Equity | Capital | Invenstors Capital | NULL | 3000/3100/3120 |
| 3190 | 3190 | Others Capital | 3 | Equity | Capital | Others Capital | NULL | 3000/3100/3190 |
| 3200 | 3200 | Earnings | 2 | Equity | Earnings | NULL | NULL | 3000/3200 |
| 3210 | 3210 | Retained Earnings | 3 | Equity | Earnings | Retained Earnings | NULL | 3000/3200/3210 |
| 3220 | 3220 | Current Earnings | 3 | Equity | Earnings | Current Earnings | NULL | 3000/3200/3220 |
+------+------+-----------------------+-------+-------------+-----------------------+----------------------+-------------+----------------+
32 rows in set (0.01 sec)
### Mencari Semua *Leaf Node*
Kita juga bisa mendapatkan semua ***leaf node***, yaitu node pada level terbawah dan tidak memiliki **child**, dengan menggunakan **LEFT JOIN**
-- Level 1
SELECT t1.id, t1.code
, t1.name AS name_level1, t2.name as name_level2
, t3.name AS name_level3, t4.name as name_level4
FROM accounts AS t1
LEFT JOIN accounts AS t2 ON t2.parent_id = t1.id
LEFT JOIN accounts AS t3 ON t3.parent_id = t2.id
LEFT JOIN accounts AS t4 ON t4.parent_id = t3.id
WHERE t1.parent_id is null
ORDER BY t1.code, t2.code, t3.code, t4.code
Hasilnya adaah seperti ini.
+------+------+-------------+-----------------------+----------------------+-------------+
| id | code | name_level1 | name_level2 | name_level3 | name_level4 |
+------+------+-------------+-----------------------+----------------------+-------------+
| 1000 | 1000 | Assets | Current Assets | Petty Cash | NULL |
| 1000 | 1000 | Assets | Current Assets | Savings Account | NULL |
| 1000 | 1000 | Assets | Current Assets | Deposits Account | NULL |
| 1000 | 1000 | Assets | Current Assets | Accounts Receivables | NULL |
| 1000 | 1000 | Assets | Fixed Assets | Furnitures | NULL |
| 1000 | 1000 | Assets | Fixed Assets | Equipments | NULL |
| 1000 | 1000 | Assets | Fixed Assets | Vehicles | NULL |
| 1000 | 1000 | Assets | Fixed Assets | Buildings | NULL |
| 1000 | 1000 | Assets | Other Assets | Patents | NULL |
| 1000 | 1000 | Assets | Other Assets | Other Assets | NULL |
| 2000 | 2000 | Liabilities | Current Liabilities | Accrued Expenses | NULL |
| 2000 | 2000 | Liabilities | Current Liabilities | Accounts Payable | NULL |
| 2000 | 2000 | Liabilities | Current Liabilities | Tax Payable | NULL |
| 2000 | 2000 | Liabilities | Long-Term Liabilities | Equipments Payable | NULL |
| 2000 | 2000 | Liabilities | Long-Term Liabilities | Vehicles Payable | NULL |
| 2000 | 2000 | Liabilities | Long-Term Liabilities | Buildings Payable | NULL |
| 2000 | 2000 | Liabilities | Long-Term Liabilities | Loans Payable | NULL |
| 3000 | 3000 | Equity | Capital | Founders Capital | NULL |
| 3000 | 3000 | Equity | Capital | Invenstors Capital | NULL |
| 3000 | 3000 | Equity | Capital | Others Capital | NULL |
| 3000 | 3000 | Equity | Earnings | Retained Earnings | NULL |
| 3000 | 3000 | Equity | Earnings | Current Earnings | NULL |
+------+------+-------------+-----------------------+----------------------+-------------+
22 rows in set (0.00 sec)
### Kesimpulan
Meskipun **terlihat mudah**, ada beberapa hal yang perlu kita perhatikan dalam mengelola data hirarki di MySQL ini,
– Kita harus mengetahui jumlah level sejak awal
– Setiap ada penambahan level, maka kita juga harus menambahkan ***left join*** lagi ke dalam query. Perlu diketahui bahwa semakin banyak *left join* maka kinerja (*performance*) mysql akan menurun.
– Penghapusan record (*node*) tidak boleh dilakukan secara sembarangan karena ada resiko tree akan berantakan jika ada child yang mengacu ke node yang dihapus. Namun hal ini bisa diatasi dengan ***Foreign Key Constraint***.
Cara lain untuk mengembangkan query hirarki ini adalah dengan menggunakan ***stored procedure*** untuk membangun query secara dinamis sehingga bisa mengantisipasi jumlah level yang lebeih banyak dan bahkan tidak terbatas.
Semoga berguna!
.
