lundi 20 juin 2016

Visualization of tree-structured / hierarchical data (e.g. bill of material) out of a MySQL database

In my MySQL database, I have tree-structured data. In this case it is a bill of materials for different products. For each element (ID) in the tree I know my parent (Parent_ID). I think it is called "Adjacency List Model".

My goal is to visualize the data in an interactive tree. Each node should be expandable equivalent to a folder structure.

I am not a good programmer, so I am looking for a easy solution. I also don't know for what I should look for. Maybe there is some html code I just have to connect with my database!?

The data looks like this:

╔════╤═══════════════╤═══════════╗
║ ID │ Element       │ Parent_ID ║
╠════╪═══════════════╪═══════════╣
║ 0  │ BoM           │           ║
╟────┼───────────────┼───────────╢
║ 1  │ Product 1     │ 0         ║
╟────┼───────────────┼───────────╢
║ 2  │ Subsystem 1.1 │ 1         ║
╟────┼───────────────┼───────────╢
║ 3  │ Part 1.1.1    │ 2         ║
╟────┼───────────────┼───────────╢
║ 4  │ Part 1.1.2    │ 2         ║
╟────┼───────────────┼───────────╢
║ 5  │ Subsystem 1.2 │ 1         ║
╟────┼───────────────┼───────────╢
║ 6  │ Product 2     │ 0         ║
╟────┼───────────────┼───────────╢
║ 7  │ Subsystem 2.1 │ 6         ║
╟────┼───────────────┼───────────╢
║ 8  │ Product 3     │ 0         ║
╟────┼───────────────┼───────────╢
║ 9  │ Subsystem 3.1 │ 8         ║
╚════╧═══════════════╧═══════════╝

Create table and data:

CREATE TABLE `BoM` (
`ID` INT(11) NULL DEFAULT NULL,
`Element` VARCHAR(50) NULL DEFAULT NULL,
`Parent_ID` INT(11) NULL DEFAULT NULL
)
;

INSERT INTO bom(id,element,parent_id) VALUES
(0,'BoM',NULL),
(1,'Product 1',0),
(2,'Subsystem 1.1',1),
(3,'Part 1.1.1',2),
(4,'Part 1.1.2',2),
(5,'Subsystem 1.2',1),
(6,'Product 2',0),
(7,'Subsystem 2.1',6),
(8,'Product 3',0),
(9,'Subsystem 3.1',8);

The result of the visualization should look similar to this:

Expanded tree

Aucun commentaire:

Enregistrer un commentaire