database design help

[corecoders]

Hello friendz,

 

I have to design a database structure. In the database I need fields for category, subcategory and product, category is the parent for subcategory and the parent of product is subcategory. There will be about 60 categories and 100 subcategories for each category and around 120 products for each subcategory. Which is the perfect way to design this database ? Using separate tables for each of  the field ( and connecting through Primary key and foreign key ) or a single table ( with a parent field for connecting the  fields .parent of category field will be 0 and the parent of subcategory will be 1 and parent of product will be 2). I want to make this professionally, so please help. Actually the database has too many other tables with thousands of rows but these are the fields that are accessing frequently.

 

Please provide a solution

 

Thank you guys 

 

[mapleleaf]

I would go for the one table. Less code for all your queries and simpler queries.

MYSQL tends to be fast when indexed.

[jl5501]

Categories and subcategories can all go in one table ( I can provide sample structure if you like), and products go in a separate table.

[ignace]

If you go for one table like jl5501 suggested you need to make sure that whenever a user adds a product it's listed under a subcategory because if the use is able to place it under a category then your product may well never be shown

[jl5501]

With the design I use, you can have products at any category level, including top level. Additionally you can have as many levels of subcategory as you like, again with products at any level