How could someone display and modify category hierarchies in Vision?

Hope all is well!

How could someone display a category hierarchy in vision with users adding or removing levels and depth?

To make it easier, I do not need to modify and display the hierarchy in the same component(s) or windows.

I was thinking about using a power table and click once to drill down or double click to back out. The Row Selector was my first choice but I would need to set a fixed depth and I don't want the ability to drill down on levels with minimal depth.

Below is a simple MySQL table that closely resembles the actual table.

create table categories
( id integer not null primary key
, name varchar(37) not null
, parentid integer null
, foreign key parentid_fk (parentid)
references categories (id)
);

It really depends on what you want to show for those categories.

The table structure you show seems like it was made for a tree view. Though perhaps a tree view isn’t flexible enough for you.

You could make it quite simple with a popup stack too: every popup shows info about a category, with subcategories you can open separately. Then you’re very free on how you can display the info. But perhaps you lose some visual clues on who’s the parent of who.

And I once scripted a power table to be able to “group” on certain columns. Where users could define their own grouping columns, and open/close groups to see the details. This was done through scripting: the initial data was loaded from SQL, and records were copied or summarised to show in the display dataset. Depending on what groups were open. It works well, but only for data that lends itself to be presented in a tabular form. The left column was used to open/close the groups, a bit like a tree view, but with extra data per element in the tree.

Your table structure is ideal for the tree view, fed by a query that uses a Common Table Expression to recursively arrange and connect the rows. (You may have to switch DBs–old MySQL can’t do CTEs.)

@pturmel, yea exactly! we are using MySQL 8.0.

@Sanderd17, I like your power table idea, and it gave me an idea. How about a single column power-table listing all roots or (main categories).

When the main category is selected, the power table expands by one column with the subcategories one cell down and to the right of the main category. The power-table can expand and contract columns and rows programmatically using a “map.”

If a user tries to click on a cell with no data, it will jump to the closest root or something. We could use some fancy Unicode characters for + and - if the node is expandable or is a result of items contained in the category.

Once a node is selected, the node’s key would be used to populate a different component with the details of the node.

Something like this.

+---+     +---+---+       +---+---+---+
| A |     | A |   |       | A |   |   |
| B |     | B |   |       | B |   |   |
| C |     | ->| 1 |       | ->| 1 |   |
| D |     |   | 2 |       |   | 2 |   |
+---+     |   | 3 |       |   | ->| E |
          |   | 4 |       |   |   | F |
          |   | 5 |       |   |   | G |
          | C |   |       |   | 3 |   |
          | D |   |       |   | 4 |   |
          +---+---+       |   | 5 |   |
                          | C |   |   |
                          | D |   |   |
                          +---+---+---+

Below is my test tables and data.

-- *****************************************************************************
-- mysql 8
-- Drop/Create/Use Schema
drop schema if exists `Test_Materials`; Create Schema `Test_Materials`; use `Test_Materials`;

-- *****************************************************************************
-- Create the material_categories table.
CREATE TABLE `material_categories` (
 `categoryID` bigint NOT NULL AUTO_INCREMENT,
 `parentID` bigint DEFAULT NULL,
 `itemType` varchar(20) NOT NULL,
 `name` varchar(80) NOT NULL,
 `created` datetime NOT NULL,
 PRIMARY KEY (`categoryID`),
 UNIQUE KEY `ParentID_name_UNIQUE` (`parentID`,`name`) /*!80000 INVISIBLE */,
 CONSTRAINT `mc_parentID_FK` FOREIGN KEY (`parentID`) REFERENCES `material_categories` (`categoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TRIGGER IF EXISTS `Test_Materials`.`material_category_BEFORE_INSERT`;
DELIMITER $$
USE `Test_Materials`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `material_categories_BEFORE_INSERT` BEFORE INSERT ON `material_categories` FOR EACH ROW BEGIN
   SET NEW.Created = NOW();
END$$
DELIMITER ;


-- *****************************************************************************
-- Create the material_details Table
CREATE TABLE `material_details` (
 `materialID` varchar(36) NOT NULL,
 `categoryID` bigint NOT NULL,
 `name` varchar(36) NOT NULL,
 `price` float NOT NULL,
 `created` datetime NOT NULL,
 PRIMARY KEY (`materialID`),
 KEY `md_categoryID_FK` (`categoryID`),
 UNIQUE KEY `categoryID_name_UNIQUE` (`categoryID`,`name`) /*!80000 INVISIBLE */,
 CONSTRAINT `md_categoryID_FK` FOREIGN KEY (`categoryID`) REFERENCES `material_categories` (`categoryID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

DROP TRIGGER IF EXISTS `Test_Materials`.`material_details_BEFORE_INSERT`;
DELIMITER $$
USE `Test_Materials`$$
CREATE DEFINER=`root`@`localhost` TRIGGER `material_details_BEFORE_INSERT` BEFORE INSERT ON `material_details` FOR EACH ROW BEGIN
   SET NEW.materialID = UUID();
   SET NEW.Created = NOW();
END$$
DELIMITER ;

-- *****************************************************************************
-- Insert some data into the material_category table
INSERT INTO material_categories (name, parentID, itemType) VALUES
('Transister',		NULL,	'product'),
('NPN', 			1,		'product'),
('PNP', 			1,		'product'),
('Capacitors', 		NULL,	'product'),
('Film',			2,		'product'),
('Paper',			2,    	'product'),
('electrolytic',	2,    	'product'),
('Aluminum',		7,    	'product'),
('Tantalum',		7,    	'product'),
('Niobium',			7,    	'product'),
('Mica',			2,    	'product'),
('SOME_Name1',		5,    	'service'),
('SOME_Name2',		4,    	'service');

-- *****************************************************************************
-- Insert some date into the material_details table
INSERT INTO `test_materials`.`material_details`
(`categoryID`,`name`,`Price`)
VALUES
(5,	'1uf',		0.010),
(5,	'10uf',		0.011),
(5,	'100uf',	0.012),
(6,	'1uf',		0.005),
(6,	'10uf',		0.006),
(6,	'100uf',	0.010);

/*
-- *****************************************************************************
-- Sample Query of paths
WITH RECURSIVE items (categoryID, name, itemType ,level, path) as (
 SELECT categoryID, name, itemType
 , 0 AS level
 , concat(categoryID) AS path
 FROM material_categories WHERE parentID IS NULL
 UNION ALL
 SELECT i.categoryID, i.name, i.itemType
 , level + 1
 , CONCAT(path, '.', i.categoryID) AS path
 FROM material_categories i
 INNER JOIN items itms ON itms.categoryID = i.parentID
 )
 
SELECT * FROM items ORDER BY path;

-- *****************************************************************************
-- testing
WITH RECURSIVE items (categoryID, itemType ,level, path) as (
 SELECT categoryID, itemType
 , 0 AS level
 , concat(categoryID) AS path
 FROM material_categories WHERE parentID IS NULL
 UNION ALL
 SELECT i.categoryID, i.itemType
 , level + 1
 , CONCAT(path, '.', i.categoryID) AS path
 FROM material_categories i
 INNER JOIN items itms ON itms.categoryID = i.parentID
 )

SELECT i.materialID, i.categoryID,
   j.name as categoryName,
   k.path as categoryPath,
   i.name, i.price, i.created
FROM material_details i , material_categories j, items k
where i.categoryID = j.categoryID and i.categoryID = k.categoryID
ORDER BY path;
*/