I created two tables Category and Products for my shopping cart database. Should I build a third table CategoryProduct and save both the ID's of category and Products in it or have a column in the product table to save the category to it.
I ask because I need to display the products, category wise. Please guide or show me an example.
If the product belongs to only one category, then you don't need a third table. Just create a column CategoryId as a foreign key and references the CategoryId from the categories table.
However, if the product belongs to many categories, then you need a third table, something like CategoryProducts with columns:
Id: Primary key works as a surrogate key, it has nothing to do with the relations,ProductId (references the product id column from products table,CategoryId (references the category id column from categories table.
Creat new table with two fileds product_id and category_id this way relation can be maintain properly with cardinality many to many(many prodct related to many category).
It depends on if a Product can have more than one Category, if this is the case then you will need the 3rd table to hold id's of both. If it is one Category per product you can add a Foreign Key to the Product table to hold the category id.
