Database Tips | 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.
<pre class="brush:sql">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);
</pre>
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
<pre class="brush:sql">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</pre>
Hasilnya akan tampak seperti ini
<pre class="brush:sql">+------+------+-------------+----------------+----------------------+-------------+
| 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)</pre>
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
<pre class="brush:sql">-- 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</pre>
Hasilnya akan tampak seperti ini
<pre class="brush:sql">+------+------+-----------------------+-------+-------------+-----------------------+----------------------+-------------+----------------+
| 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)</pre>
Mencari Semua Leaf Node
Kita juga bisa mendapatkan semua leaf node, yaitu node pada level terbawah dan tidak memiliki child, dengan menggunakan LEFT JOIN
<pre class="brush:sql">-- 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</pre>
Hasilnya adaah seperti ini.
<pre class="brush:sql">+------+------+-------------+-----------------------+----------------------+-------------+
| 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)</pre>
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!
.
- Log in to post comments
- 6023 kali dilihat