I’ve always wondered if there was a better way to manage nested data structures (such as product categories) in MySQL. Today I stumbled across a solution called the Nested Set Model.
The only addition I made to the solution is rename what they call the “category_id” and call that a “sort_id”. Then I added a primary key called “id” to the table. This way, I have an immutable ID I can use in the application (such as for URL deep linking). For example:
CREATE TABLE nested_category (
id INT AUTO_INCREMENT PRIMARY KEY,
sort_id INT NOT NULL,
name VARCHAR(20) NOT NULL,
left_sort INT NOT NULL,
right_sort INT NOT NULL