Using Multiple tables

[TheFreak]

I am making a list of products and every product fall under certain category.Now i want to add/edit category,but not necessary i would put product with it too.So i am guessing ill need to make another table for category.My question is,if i make another table for category then how will i link it to another table having the products and products details?

 

Suppose i have Category Motherboard and HDD,and under motherboard many products . So is there a way i can add/edit those category like i want to add Processors to it too or i want to edit HDD to harddisk and so that it affects the other table with product table too as when someone searches Motherboards,i want to show him all the products related to it

[fenway]

Why won't a join work?

[TheFreak]

Yeah well  i read about joints and i dont know how it will help me,my main problem is i have a category (suppose motherboards) then ill have a subcategory of that motherboard(suppose "Socket Type").Main catch is there can be many categories like that (like processors,HDD,RAM) and their respective subcategories,each motherboard having different details abt the subcategory...so i am not able to decide if i should make new tables for each category and their sub category or put all in one...problem to put all in one is,one category might have 2 sub category will others might have just one,so i think space will get wasted.Now if i make tables for every category there would be like 20 tables and i dont think thats good either.I know the solution is kinda simple too.

[vicodin]

why not put in one table and then have a col for the main and then another col for the sub cats and seperate all the sub cats by a comma and explode them out?

[fenway]

why not put in one table and then have a col for the main and then another col for the sub cats and seperate all the sub cats by a comma and explode them out?

Um, no... that's impossible to maintain.

[realistTheorist]

On way to handle this would be two tables, as follow:

create table product (
  product_id
  product_name, ... etc.
  category_id
)

create table category(
  category_id
  category_name, ... etc.
  parent_category_id
)

 

... categories like that (like processors,HDD,RAM) and their respective subcategories,...

Is it just Category -> Sub-Category, or can Sub-Categories be further split into Sub-sub-categories. If you have just one sub-level, the queries should be easy.

 

[TheFreak]

Yes i thought about that too,but the only things is i have 9 categories like that meaning there would be loads of table,wouldn't it harm in any way?

[fenway]

Yes i thought about that too,but the only things is i have 9 categories like that meaning there would be loads of table,wouldn't it harm in any way?

 

No, because is this model, there is only one category_table -- it's a foreign key to itself.

[TheFreak]

I dont understand can you please explain me giving an example.

[fenway]

the parent_category_id can point to another subcategory.