MySQL DB structure with Main category and several nested sub categories

Question

I am looking to create a set of fields on my webpage identical to what eBay uses on their category selection page when you are selling something. For my purposes I am doing motorcycles, power sports, parts & accessories. So for example I would like to have three very broad categories:

Motorcycles
Powersports
Parts & Accessories

If a user selects Motorcycles it will then ask Brand:

Yamaha
Honda
Kawasaki
Harley-Davidson

Upon selecting a Brand it will then ask for model for that specific brand, so for Yamaha:

YZ
YZF
YFZ
WR
PW

So

Motorcycles -> Brand -> Model

Now if I wanted to add something to parts and accessories it would be something like

Parts & Accessories -> Brand -> Model -> Type of part -> Sub type of part

Same for Powersports:

Powersports -> ATV -> Brand -> Model

I have tried a structure like this

Main Categories(categories):

+-------+---------------------+-----------------------+
| CatID | Category            | CatDBTable            |
+-------+---------------------+-----------------------+
|     1 | Motorcycles         | motorcycle_brands     |
|     3 | Parts & Accessories | part_acc_categories   |
|     2 | Powersports         | powersport_categories |
+-------+---------------------+-----------------------+

Motorcycles(motorycle_brands):

+-------+-------+-------------------------+
| CatID | SubID | SubName                 |
+-------+-------+-------------------------+
|     1 |     1 | American Classic Motors |
|     1 |     2 | American Ironhorse      |
|     1 |     3 | Aprilia                 |
|     1 |     4 | Benelli                 |
|     1 |     5 | Big Dog                 |
+-------+-------+-------------------------+

Motorcycles Model(motorcycle_models):

+---------+--------------+------------+
| ModelID | BrandID      | BrandModel |
+---------+--------------+------------+
|       1 | Custom Built | Bobber     |
|       2 | Custom Built | Chopper    |
|       3 | Custom Built | Pro Street |
|       4 | Custom Built | Other      |
|       5 | BMW          | F-Series   |
+---------+--------------+------------+

Now, this is really simple to maintain keeping everything in separate tables but a nightmare for me to figure out how to query it properly. I have posted this question on another site and a user suggested using one large table which I think is a terrible suggestion but then again I am not a DB admin. I am hoping some of the experts here that do this on a daily basis can help me come up with a structure that is easy to query and maintain at the same time. I hope all of this makes sense. I have search around but can not find anything on doing several nested sub categories only a main category and a sub category.

Answer 1

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:

1. Adding new product categories in the future requires only adding records, not the addition of new tables.

2. If formalizes the relationship hierarchy of category-brand-model instead of relying on shaky and maintenance-intensive query design to do so.

3. 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.

Answer 2

I think that this can be easily done by using just a single table. This table would require three columns i.e. category_id, category_name and parent_category_id.

In this case, you will need to use a self join while querying the database. Querying the database would be very easy with this approach as well. But there is a con to this approach which is that it will be a little performance heavy. The approach suggested by Thomas Cleberg however, is better if you are preferring performance in this case.

In case you want to implement the one table approach, you will have to properly index your table to maximize performance while querying.

Both the approaches have their pros and cons and you must select the approach that best fits your application's performance requirements.