@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;
*/