MySQL DB structure with Main category and several nested sub categories
I would generalize the design, I.e. instead of a motorcycle_brand table, You would want a category_brand table, and instead of a motorcycle_model table, you would have a category_brand_model table with an fk on the category_brand pk. This allows for several advantages:


Adding new product categories in the future requires only adding records, not the addition of new tables.
If formalizes the relationship hierarchy of category-brand-model instead of relying on shaky and maintenance-intensive query design to do so.
The queries would be very, very simple to write.


To do this, you would combine each of the three category brand tables you have now and create a new table categoy_brand with an fk on catid.