Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

Parts & Accessories

If a user selects Motorcycles it will then ask Brand:


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



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 |


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

share|improve this question
Don't mix data and metadata -- i.e. don't put what should be data into table or colum names. – Colin 't Hart Jun 20 '15 at 18:09
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.