Hierarchal suppler representation & traversal in RDBMS

For example consider organizations like PG, Siemens and GE where the business is organized in multiple verticals and many subsidiaries across the globe. One way of identifying all these units are DUNS numbers.  Brief  definition of DUNS number is

At least in one standard (Dun & Bradstreet (D&B) provides a D-U-N-S Number,

A unique nine digit identification number, for each physical location of your business.)

Many global manufacturing companies uses this DUNS numbers to organize their suppliers & their subsidiaries. In the sequence of post I will explain relational way of mapping this suppler data to RDBMS and then to Graph database (In this prototype, I am using neo4j as a graph data base.)

In the relational mapping, each suppler entity contains following minimal set of attributes

Supplier primary_duns_number, supplier_name, address and parent duns number.

(In case of root suppler organization, primary_duns_number and parent duns’ number is same)

Following is the ORACLE specific SQL query, uses concepts like CONNECT AND CYCLE etc.,

Which brings all parent rows for given child suppler primary key.

>>

SELECT LEVEL,LPAD (' ', 2 * (LEVEL - 1)) ||  t1.PRIMARY_DUNS_NUMBER, 

           t1.SUPPLIER_NAME, 

           t1.PARENT_DUNS_NUMBER,

           t1.address,

           SYS_CONNECT_BY_PATH(t1.PRIMARY_DUNS_NUMBER, '/') "Path",

           t2.SUPPLIER_NAME AS parent_name,

           t2.PARENT_DUNS_NUMBER AS parent_id

           FROM  GQTS.SUPPLIER t1

           LEFT JOIN GQTS.SUPPLIER t2 ON t2.PRIMARY_DUNS_NUMBER = t1.PARENT_DUNS_NUMBER

          START WITH t1.PRIMARY_DUNS_NUMBER = 'DUNS_CODE' 

CONNECT BY NOCYCLE  PRIOR t1.PRIMARY_DUNS_NUMBER = t1.PARENT_DUNS_NUMBER

>>