Representing heirarchical data in MySQL

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