MySQL DB structure with Main category and several nested sub categories





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







1















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.










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


















1















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.










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














1












1








1








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.










share|improve this 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.







mysql database-design schema






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jun 22 '15 at 20:18









RolandoMySQLDBA

144k24228385




144k24228385










asked Mar 7 '14 at 3:48









Yamaha32088Yamaha32088

13716




13716













  • 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



















  • 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

















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





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










2 Answers
2






active

oldest

votes


















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.






share|improve this answer

































    0














    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.





    share








    New contributor




    user176596 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
    Check out our Code of Conduct.





















      Your Answer








      StackExchange.ready(function() {
      var channelOptions = {
      tags: "".split(" "),
      id: "182"
      };
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function() {
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled) {
      StackExchange.using("snippets", function() {
      createEditor();
      });
      }
      else {
      createEditor();
      }
      });

      function createEditor() {
      StackExchange.prepareEditor({
      heartbeatType: 'answer',
      autoActivateHeartbeat: false,
      convertImagesToLinks: false,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: null,
      bindNavPrevention: true,
      postfix: "",
      imageUploader: {
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      },
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      });


      }
      });














      draft saved

      draft discarded


















      StackExchange.ready(
      function () {
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f60373%2fmysql-db-structure-with-main-category-and-several-nested-sub-categories%23new-answer', 'question_page');
      }
      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes









      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.






      share|improve this 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.






        share|improve this answer




























          1












          1








          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.






          share|improve this 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.







          share|improve this answer














          share|improve this answer



          share|improve this answer








          edited Jun 20 '15 at 18:09









          Colin 't Hart

          6,63682634




          6,63682634










          answered Mar 8 '14 at 14:44









          Thomas ClebergThomas Cleberg

          1,314715




          1,314715

























              0














              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.





              share








              New contributor




              user176596 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
              Check out our Code of Conduct.

























                0














                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.





                share








                New contributor




                user176596 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                Check out our Code of Conduct.























                  0












                  0








                  0







                  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.





                  share








                  New contributor




                  user176596 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.










                  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.






                  share








                  New contributor




                  user176596 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.








                  share


                  share






                  New contributor




                  user176596 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.









                  answered 2 mins ago









                  user176596user176596

                  1




                  1




                  New contributor




                  user176596 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.





                  New contributor





                  user176596 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.






                  user176596 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
                  Check out our Code of Conduct.






























                      draft saved

                      draft discarded




















































                      Thanks for contributing an answer to Database Administrators Stack Exchange!


                      • Please be sure to answer the question. Provide details and share your research!

                      But avoid



                      • Asking for help, clarification, or responding to other answers.

                      • Making statements based on opinion; back them up with references or personal experience.


                      To learn more, see our tips on writing great answers.




                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function () {
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f60373%2fmysql-db-structure-with-main-category-and-several-nested-sub-categories%23new-answer', 'question_page');
                      }
                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      Popular posts from this blog

                      Liste der Baudenkmale in Friedland (Mecklenburg)

                      Single-Malt-Whisky

                      Czorneboh