[**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!
.