Categories in MySQL

[Krossfire]

I think it would be nice to change a little categories table. Right now, if I'm right, it works like this:
MAINCATEGORY1 (Range 2 - 7)
--Subcat1(Range 3 - 4)
--Subcat2(Range 5 - 6)

MAINCATEGORY2 (Range 8 - 13)
--Subcat3(Range 9 - 10)
--Subcat4(Range 11 - 12)

Which means - every category have a range, which tells what subcategories it posses. I.E.: MAINCATEGORY(2 - 7) contains all categories within range (3 - 6), and MAINCATEGORY2 contains categories within range (9 - 12).
With small amount of categories this system works great, but when you have some more categories (I need 200 - 10 categories with 20 subs each, but system just died at 73 categories), it just breaks. It also makes hard any mass-changes in MySQL database (i.e.: changing parent-categories). Category droplist broken somewhere at 40 - 50 categories.

So I have a question - will you ever change that system to something easier and more stable, like:
MainCategory, ID: 1, Parent ID: 0
MainCategory2, ID: 2, Parent ID: 0

Subcategory, ID: 3, ParentID: 1
Subcategory2, ID: 4, ParentID: 2
Subcategory3, ID: 5, ParentID: 2
Subcategory4, ID: 6, ParentID: 1

SubSubCategory, ID: 7, ParentID: 4
SubSubCategory2, ID: 7, ParentID: 6
SubSubCategory3, ID: 7, ParentID: 5

This should work great, and be more intuitive for people, who want to change something within DB (like adding + 50 categories to one parent, or changing parents of xx categories). Also script to convert tables shouldn't be hard to make (analyze table, get ranges, if (range > x && range < y) {set ParentID to XX}).

Regards.

[row1]

Hi,

Thanks for taking the time to write an in-depth request.

A long, long time ago (v1) the category structure did have the parent ID in the table, but in v2 I switched to the current nested set model (http://en.wikipedia.org/wiki/Nested_set_model).

While this model does make it hard to manually edit the database, its performance is a lot better in MySQL than storing the parent ID and having to recursively build the category hierarchy (http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/). I believe Joomla also uses nested sets for its category for this very reason.

The problem with it breaking that you speak of would also happen if I changed the database to use any other hierarchy model. The category drop down is a fundamental flaw as I never expected someone to have so many categories. So I will look to change how selecting a category is presented (I am thinking a treeview).

If you have MySQL 5 and find yourself wanting to directly work with the categories, you could create some stored procedures to manipulate the nested set. A quick Google search seems to find a few. I did want to use these in Simple Review, but I am still supporting MySQL 4.

[Krossfire]

Thanks for answer, I didn't know that nested set model is faster :-)
I'll wait for treeview update or something.