MySQL DB Structure – Main Category and Nested Sub Categories

database-designMySQLschema

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.

Best Answer

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.

Related Question