I am just learning about database design, and I have come across a table design challenge that I cannot seem to get past. Maybe you can help!
I am helping to design a database for a VERY small e-commerce web site. There are 1500 products, so in order for customers to be able to find what they are looking for, products must be broken into categories and subcategories. My clients have decided that Having 4 levels is enough...category, subcategory, subsubcategory, and occasionally subsubsubcategory. For instance, category shirts, subcategory t-shirts, subsubcategory cotton tshirts, and MAYBE subsubsubcategory BLUE tshirts. Each level has a description (so cotton subsubcat cotton t-shirts will have a description of the quality of the cotton). And one product can be in more than one category, and obviously one category will have more than one product.
That is my dilemma. I originally had 4 tables, one for cat & description, one for subcat & description, etc... But then I realized I was limited to only 4 tables. Maybe one day my clients would want to have a subsubsubSUBcat (dear lord!). So that didn't work. So then I figured one table for all levels, then another table linking each level to a productID. but then I had a table with the productID in there 8 times (a product in 2 categories, 2 subcats, 2 subsubcats, and 2subsubsubcats). This seemed too redundant as well. Then I tried having 3 tables, one with all the levels and their descriptions, one with all the different combinations possible, and one linking the productID to a structure. But this didn't work either b/c again I was limited to the number of COLUMNS i put in the structure table.
Does anyone have any suggestions? Again, I'm a novice so please try to use plain language when explaining!
Thanks!
nicole
I am helping to design a database for a VERY small e-commerce web site. There are 1500 products, so in order for customers to be able to find what they are looking for, products must be broken into categories and subcategories. My clients have decided that Having 4 levels is enough...category, subcategory, subsubcategory, and occasionally subsubsubcategory. For instance, category shirts, subcategory t-shirts, subsubcategory cotton tshirts, and MAYBE subsubsubcategory BLUE tshirts. Each level has a description (so cotton subsubcat cotton t-shirts will have a description of the quality of the cotton). And one product can be in more than one category, and obviously one category will have more than one product.
That is my dilemma. I originally had 4 tables, one for cat & description, one for subcat & description, etc... But then I realized I was limited to only 4 tables. Maybe one day my clients would want to have a subsubsubSUBcat (dear lord!). So that didn't work. So then I figured one table for all levels, then another table linking each level to a productID. but then I had a table with the productID in there 8 times (a product in 2 categories, 2 subcats, 2 subsubcats, and 2subsubsubcats). This seemed too redundant as well. Then I tried having 3 tables, one with all the levels and their descriptions, one with all the different combinations possible, and one linking the productID to a structure. But this didn't work either b/c again I was limited to the number of COLUMNS i put in the structure table.
Does anyone have any suggestions? Again, I'm a novice so please try to use plain language when explaining!
Thanks!
nicole