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). Jika sebelumnya kita mempelajari cara mengelola data hirarki di MySQL, maka pada artikel inimkita akan mempelajarinya di PostgreSQL. RDBMS sendiri tidak diperuntukkan untuk menyimpan data hirarki karena relasi antar table bukanlah relasi hirarki. Namun di PostgreSQL kita bisa memanfaatkan fitur Common Table Expression (CTE) untuk melakukan reksi di SQL Query.
Data Hirarki adalah sekumpulan data yang masing-masingnya memiliki satu parent dengan pengecualian untuk data root. Masing-masing 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
WITH RECURSIVE q AS (
SELECT h.*::accounts AS h
, ARRAY[]::character varying[] || h.name AS name_breadcrumb
FROM accounts h
WHERE h.parent_id IS NULL
UNION ALL
SELECT hi.*::accounts AS hi
, q_1.name_breadcrumb::character varying(255)[] || hi.name
FROM q q_1
JOIN accounts hi ON hi.parent_id = (q_1.h).id
)
SELECT (q.h).id AS id
, (q.h).code AS code
, (q.h).name AS name
, q.name_breadcrumb[1] AS name_level1
, q.name_breadcrumb[2] AS name_level2
, q.name_breadcrumb[3] AS name_level3
, q.name_breadcrumb[4] AS name_level4
FROM q
WHERE q.name_breadcrumb[1] = 'Assets'
ORDER BY (q.h).code;
Hasilnya akan tampak seperti ini
id | code | name | name_level1 | name_level2 | name_level3 | name_level4 ------+------+----------------------+-------------+----------------+----------------------+------------- 1000 | 1000 | Assets | Assets | | | 1100 | 1100 | Current Assets | Assets | Current Assets | | 1110 | 1110 | Petty Cash | Assets | Current Assets | Petty Cash | 1120 | 1120 | Savings Account | Assets | Current Assets | Savings Account | 1130 | 1130 | Deposits Account | Assets | Current Assets | Deposits Account | 1150 | 1150 | Accounts Receivables | Assets | Current Assets | Accounts Receivables | 1200 | 1200 | Fixed Assets | Assets | Fixed Assets | | 1210 | 1210 | Furnitures | Assets | Fixed Assets | Furnitures | 1220 | 1220 | Equipments | Assets | Fixed Assets | Equipments | 1230 | 1230 | Vehicles | Assets | Fixed Assets | Vehicles | 1250 | 1250 | Buildings | Assets | Fixed Assets | Buildings | 1900 | 1900 | Other Assets | Assets | Other Assets | | 1910 | 1910 | Patents | Assets | Other Assets | Patents | 1990 | 1990 | Other Assets | Assets | Other Assets | Other Assets | (14 rows)
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
WITH RECURSIVE q AS (
SELECT h.*::accounts AS h
, 1 AS level
, ARRAY[]::character varying[] || h.code AS code_breadcrumb
, ARRAY[]::character varying[] || h.name AS name_breadcrumb
FROM accounts h
WHERE h.parent_id IS NULL
UNION ALL
SELECT hi.*::accounts AS hi
, q_1.level + 1 AS level
, q_1.code_breadcrumb::character varying(255)[] || hi.code
, q_1.name_breadcrumb::character varying(255)[] || hi.name
FROM q q_1
JOIN accounts hi ON hi.parent_id = (q_1.h).id
)
SELECT (q.h).id AS id
, (q.h).code AS code
, q.name_breadcrumb[1] AS name_level1
, q.name_breadcrumb[2] AS name_level2
, q.name_breadcrumb[3] AS name_level3
, q.name_breadcrumb[4] AS name_level4
, q.level
, (q.h).name AS name
, repeat(' '::text, q.level) || (q.h).name::text AS name_indented
, q.code_breadcrumb::character varying AS code_path
FROM q
ORDER BY q.code_breadcrumb;
Hasilnya akan tampak seperti ini
id | code | name_level1 | name_level2 | name_level3 | name_level4 | level | name | name_indented | code_path
------+------+-------------+-----------------------+----------------------+-------------+-------+-----------------------+----------------------------+------------------
1000 | 1000 | Assets | | | | 1 | Assets | Assets | {1000}
1100 | 1100 | Assets | Current Assets | | | 2 | Current Assets | Current Assets | {1000,1100}
1110 | 1110 | Assets | Current Assets | Petty Cash | | 3 | Petty Cash | Petty Cash | {1000,1100,1110}
1120 | 1120 | Assets | Current Assets | Savings Account | | 3 | Savings Account | Savings Account | {1000,1100,1120}
1130 | 1130 | Assets | Current Assets | Deposits Account | | 3 | Deposits Account | Deposits Account | {1000,1100,1130}
1150 | 1150 | Assets | Current Assets | Accounts Receivables | | 3 | Accounts Receivables | Accounts Receivables | {1000,1100,1150}
1200 | 1200 | Assets | Fixed Assets | | | 2 | Fixed Assets | Fixed Assets | {1000,1200}
1210 | 1210 | Assets | Fixed Assets | Furnitures | | 3 | Furnitures | Furnitures | {1000,1200,1210}
1220 | 1220 | Assets | Fixed Assets | Equipments | | 3 | Equipments | Equipments | {1000,1200,1220}
1230 | 1230 | Assets | Fixed Assets | Vehicles | | 3 | Vehicles | Vehicles | {1000,1200,1230}
1250 | 1250 | Assets | Fixed Assets | Buildings | | 3 | Buildings | Buildings | {1000,1200,1250}
1900 | 1900 | Assets | Other Assets | | | 2 | Other Assets | Other Assets | {1000,1900}
1910 | 1910 | Assets | Other Assets | Patents | | 3 | Patents | Patents | {1000,1900,1910}
1990 | 1990 | Assets | Other Assets | Other Assets | | 3 | Other Assets | Other Assets | {1000,1900,1990}
2000 | 2000 | Liabilities | | | | 1 | Liabilities | Liabilities | {2000}
2100 | 2100 | Liabilities | Current Liabilities | | | 2 | Current Liabilities | Current Liabilities | {2000,2100}
2110 | 2110 | Liabilities | Current Liabilities | Accrued Expenses | | 3 | Accrued Expenses | Accrued Expenses | {2000,2100,2110}
2120 | 2120 | Liabilities | Current Liabilities | Accounts Payable | | 3 | Accounts Payable | Accounts Payable | {2000,2100,2120}
2130 | 2130 | Liabilities | Current Liabilities | Tax Payable | | 3 | Tax Payable | Tax Payable | {2000,2100,2130}
2200 | 2200 | Liabilities | Long-Term Liabilities | | | 2 | Long-Term Liabilities | Long-Term Liabilities | {2000,2200}
2210 | 2210 | Liabilities | Long-Term Liabilities | Equipments Payable | | 3 | Equipments Payable | Equipments Payable | {2000,2200,2210}
2220 | 2220 | Liabilities | Long-Term Liabilities | Vehicles Payable | | 3 | Vehicles Payable | Vehicles Payable | {2000,2200,2220}
2240 | 2240 | Liabilities | Long-Term Liabilities | Buildings Payable | | 3 | Buildings Payable | Buildings Payable | {2000,2200,2240}
2250 | 2250 | Liabilities | Long-Term Liabilities | Loans Payable | | 3 | Loans Payable | Loans Payable | {2000,2200,2250}
3000 | 3000 | Equity | | | | 1 | Equity | Equity | {3000}
3100 | 3100 | Equity | Capital | | | 2 | Capital | Capital | {3000,3100}
3110 | 3110 | Equity | Capital | Founders Capital | | 3 | Founders Capital | Founders Capital | {3000,3100,3110}
3120 | 3120 | Equity | Capital | Invenstors Capital | | 3 | Invenstors Capital | Invenstors Capital | {3000,3100,3120}
3190 | 3190 | Equity | Capital | Others Capital | | 3 | Others Capital | Others Capital | {3000,3100,3190}
3200 | 3200 | Equity | Earnings | | | 2 | Earnings | Earnings | {3000,3200}
3210 | 3210 | Equity | Earnings | Retained Earnings | | 3 | Retained Earnings | Retained Earnings | {3000,3200,3210}
3220 | 3220 | Equity | Earnings | Current Earnings | | 3 | Current Earnings | Current Earnings | {3000,3200,3220}
(32 rows)
Kesimpulan
Meskipun terlihat mudah, ada beberapa hal yang perlu kita perhatikan dalam mengelola data hirarki di PostgreSQL ini,
- Konsep rekursi termasuk salah satu konsep yang cukup sulit untuk dipahami, sehingga rekursi dalam SQL Query dijamin bikin pusing :)
- PostgreSQL mempunyai tipe data array yang sangat berguna dalam memproses data
- PostgreSQL termasuk kategori ORDBMS (Object RDBMS) karena kita bisa memperlakukan table sebagai object.
- 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 namun penggunaan CTE tertap lebih baik karena engine PostgreSQL tentunya sudah melakukan optimasi terhadap proses rekursi di CTE..
Semoga berguna!
.
- Log in to post comments
- 1930 kali dilihat