How to represent a limited-depth/width set of hierarchical data in MySQL

Question

I'm attempting to refactor and normalize a table of products in MySQL like the one below:

CREATE TABLE `products` (
`id` varchar(45) NOT NULL, # alphanumeric product codes
`productCategory` varchar(45) NOT NULL, 
`productSubCategory` varchar(45) DEFAULT NULL, 
`productRange` varchar(45) NOT NULL,
`productClass` varchar(45) DEFAULT NULL,
`name` text NOT NULL,
`size` int(11) NOT NULL,
`sizeMeasure` varchar(5) NOT NULL,
`boxQuantity` int(11) NOT NULL,
`sellingPrice` double NOT NULL,
`rrp` double DEFAULT NULL,
`ordinalValue` int(11) NOT NULL,
PRIMARY KEY (`id`)
)

What I believe needs to be refactored are the productCategory,productSubCategory, and productRange fields. The reason for this is that I need to be able to get the hierarchical structure of the categories. It is also planned to add metadata to the categories.

The table fields currently contain data as follows:

productCategory     productSubCategory      productRange
---------------------------------------------------------
retail              retail_solution1        retail_solution1_range1
retail              retail_solution2        retail_solution2_range2
retail              retail_solution3        retail_solution3_range3
professional        prof_solution1          prof_solution1_range1
professional        prof_solution1          prof_solution1_range2
professional        prof_solution1          prof_solution1_range3
professional        prof_solution2          prof_solution2_range1
professional        prof_solution2          prof_solution2_range2
professional        prof_solution2          prof_solution2_range3
...                 ...                     ...

Essentially within the productCategory RETAIL set the productSubCategory and productRange fields have the same VARCHAR(45) values.

The depth of this tree is highly unlikely to change in the future. The most common changes will likely be made to category names.

I've looked at the adjacency list and nested set models and am somehow feeling more inclined to use the nested set model.

---

What I have so far for the nested set model approach:

Adding a category:

CREATE PROCEDURE `addCategory`(IN parent VARCHAR(45), IN child VARCHAR(45))
BEGIN
    SELECT @myLeft := lft FROM product_category

    WHERE name = parent LIMIT 1;

    UPDATE product_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
    UPDATE product_category SET lft = lft + 2 WHERE lft > @myLeft;

    INSERT INTO product_category(name, lft, rgt) VALUES(child, @myLeft + 1, @myLeft + 2);
END

Deleting a category:

CREATE PROCEDURE `deleteCategory`(IN name VARCHAR(45))
BEGIN
    SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
    FROM product_category
    WHERE name = name;

    DELETE FROM product_category WHERE lft BETWEEN @myLeft AND @myRight;

    UPDATE product_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
    UPDATE product_category SET lft = lft - @myWidth WHERE lft > @myRight;
END

---

I would like to have the sql in the java web app rather than stored procedures though. I'm using Spring's JDBCTemplate for all data access. I'm not really sure at present how to approach this as I haven't used multi-statement queries with JDBCTemplate before.

Ideally I'd also want to test the data access layer using DBUnit.

What is throwing me off on this project is that somehow I feel more importance needs to be given to the top level categories. Perhaps the top level categories of RETAIL and PROFESSIONAL should be product types rather than categories?

Any guidance would be greatly appreciated. Thanks.

Answer 1

oh well, this looks ugly.

Firstly you should use category-ids to store them into your product (and not varchars), next I would use a simple hirarchical setup of the categories itself.

If you can only have two levels ( the product is either in the root category itself, or in one of its subcategories ) Simply do something like:

categoryID, name, parentId:
1, sports, NULL
2, Crossbows, 1
3, Golf, 1
4, Healthcare, NULL

Here sports and Healthcare are "master" level, as your Retail and Professional would be.

if you can have a deeper structure, I would consider using a different approach, since multi-level hirarchy is a pain in the arse in most databases, espec. in mysql (oracle has CONNECT BY PRIOR command against/for this)

If this is the case, I would store the fullpath in a string like an OS would (root/subcat1/subcat2...), this can get somehow sticky, when something is updated, but is very fast on retrival instead of the full recursive retrival of hirarchies.

Another topic that discussed this retrival problem How to do MySQL Looped Join which tests if results are complete? (with codes required)

After you resolved your category to a ID, simply store the specific (and only) id in the product itself as a foreign key.