Hierarchical Table

Material

Konsep mengenai tabel hierarki
  https://en.wikipedia.org/wiki/Hierarchical_database_model
Contoh : Engineering Bill of Material (EBOM)

Deskripsi

Bill of Material (BOM) mendefinisikan produk akhir yang terdiri dari daftar item, bahan atau material yang dibutuhkan untuk merakit, mencampur atau memproduksi produk akhir. Namun beberapa item pembentuknya memiliki sub-assembly. Sehingga produk akhir dalam BOM dapat digambarkan sebagai struktur pohon yang terdiri dari level-level dibawahnya.

Langkah-langkah

Desain Tabel

Permasalahan yang terjadi adalah bagaimana membentuk sebuah struktur pohon dalam database dimana memudahkan proses insert dan query. Terdapat beberapa solusi yang dapat digunakan namun solusi yang dirasa paling cocok adalah dengan menggunakan metode Path Enumeration. Metode ini menyimpan rangkaian induk yang dimulai dari komponen level teratas sampai level terbawah. Sebagai contoh pada Gambar 1, produk akhir yang akan dibentuk adalah Left Pilot Door. Komponen ini menjadi akar dari struktur pohon BOM dan menempati level teratas yaitu level 1. Sehingga yang disimpan pada hierarki node untuk komponen tersebut adalah dirinya sendiri (1/). Sedangkan pada komponen Doubler yang menjadi sub-assembly dari Outer Skin dan Left Pilot Door menyimpan rute dari produk dengan level paling atas sampai dirinya sendiri (1/5/7).

Maka dari penjelasan di atas dapat dibentuk struktur tabel seperti pada Tabel 1. Tabel tersebut menunjukkan bahwa terdapat sebuah kolom hierarchy yang isinya menyimpan rangkaian induk dari setiap komponen. Juga terdapat penambahan kolom ID yang digunakan sebagai primary key.

Mengisi Data Tabel

Tabel Ebom nantinya akan diisi dengan data yang berasal dari file eksternal. Format file eksternal yang digunakan diharapkan berbasis teks seperti txt atau csv. Kedua format tersebut memudahkan proses memindahkan data ke database. Namun berbeda dengan txt, format csv biasanya dapat dibuka menggunakan program pengolah spreadsheet. Jadi, format csv lebih disarankan. Berikut alur program untuk memasukkan data ke database sekaligus generate hierarki.

Implementasi

Tabel DDL

CREATE TABLE ebom
(
    id serial NOT NULL,
    hierarchy text,
    lvl numeric(10,0),
    i_part character varying(40),
    i_assy character varying(20),
    c_part_rev character varying(2),
    n_part character varying(80),
    q_part numeric(13,0),
    i_maturity character varying(20),
    i_part_nha character varying(50),
    c_part_stocksize character varying(20),
    c_treatment character varying(10),
    c_finish character varying(20),
    c_partmark character varying(5),
    q_weight numeric(10,2),
    c_matspec character varying(100),
    c_eff character varying(20),
    c_designated boolean,
    c_interchangeable boolean,
    c_replaceable boolean,
    c_safety boolean,
    CONSTRAINT ebom_pk PRIMARY KEY (id)
);

Function

CREATE OR REPLACE FUNCTION generate_path() RETURNS text AS
$BODY$
declare
  rec record;
  hier text;
  ebomid text;
begin
  for rec in select * from ebom order by lvl, i_assy loop
    if rec.i_part_nha is null then
      EXECUTE format ('update ebom set hierarchy=%L where i_part=%L', rec.id || '/', rec.i_part);
      select hierarchy from ebom where i_part=rec.i_part and i_part_nha=rec.i_part_nha into hier;
    else
      select hierarchy from ebom where ebom.i_part = rec.i_part_nha limit 1 into hier;
      if hier is not null then hier = hier || rec.id;
      else
        select id from ebom where ebom.i_part = rec.i_part_nha limit 1 into ebomid;
        hier = ebomid || '/' || rec.id;
      end if;
      EXECUTE format('update ebom set hierarchy=%L where i_part=%L and i_part_nha=%L', hier || '/', rec.i_part, rec.i_part_nha);
    end if;
  end loop;
return 'Sukses';
end;
$BODY$
LANGUAGE plpgsql;

Lakukan langkah berikut
  Load File ke Tabel EBOM
  Run generate_path()

Tes Query

Query ancestors of VENTACCESS

select * from ebom
where ( select hierarchy from ebom where n_part='VENT ACCESS' ) like hierarchy || '%';

Query descendants of OUTER SKIN ASSY

select * from ebom
where hierarchy like ( select hierarchy from ebom where n_part='OUTER SKIN ASSY' ) || '%';