What you have here is a classical N:M relation.
This means you have multiple products which belong to the same category and multiple
categories which belong to the same product.
You can use two tables (with redundancy), but I would use three tables.
1) Product table
2) Category table
3) Link table which consists only of Product.id (prod_id) and Category.id (cat_id)
And then get the desired product with category name with:
Product.id = Link.prod_id
AND Link.cat_id = Category.id
AND Product.name = 'desired'
PS: Sorry for the late answer, I was really busy.
Ben-Nes Michael wrote:
> so if i want to make product table and category table where product can be in 1
> category to 10 (product can have more then 1 category).
> So i need to do 2 tables one is product with all the detail and second table for
> category with two columns 1 product ID and second category ?
> Is it possible to make some connection between them hard/soft link or something ?
> Philip Hallstrom wrote:
> > In article <37BC63EE.560F0278@stripped> you write:
> > >Hi
> > >
> > >I want two tables that will share the same key and if i will delete a
> > >product from one table the second table will be affected too.
> > >
> > >is it possible ?
> > >where can i read more on it ?
> > I'm pretty sure this isn't possible with mysqql since it doesn't support
> > FORIEGN KEYS or triggers. The manual actually has a section on why this is the
> > case for F.keys... I think they might add triggers at some point.
> > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html"
> Ben-Nes Michael - Manager
|• Re: Two tables||Christian Mack||8 Sep|