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 );