Beginner question: Inserting in a single row with multiple data in a column

[bugzy]

Hello guys. This is just a beginner question and I need some clarification regarding this.

 

I have this tables with their columns

 

Category Table

Category_ID (PK) | Category_Name | Category_Position | Category_Visibility

 

Item Table

 

Item_ID (PK) | Category_ID (FK) | Item_Name | Item_Description | Item_Price | Item_Stocks | Item_Sale | Date_Added

 

 

 

What I want is, an item can have multiple categories so I wonder putting category_id as the FK in the item table is right? or is it the other way around?

 

I also wonder how would I able to insert an item with a multiple categories on it? Will I need to put a loop on it?

 

 

 

 

Anyone?

 

[The Little Guy]

usually you would have 3 tables for this:

 

categories:

Category_ID (PK) | Category_Name | Category_Position | Category_Visibility

 

cat_items

Category_ID | Item_ID

 

items:

Item_ID (PK) | Item_Name | Item_Description | Item_Price | Item_Stocks | Item_Sale | Date_Added

 

you would then put your Category/Item pairs in the two column table.

 

then do a query like so:

 

select * from cat_items ci 
left join categories c using(Category_ID)
left join items i using(Item_ID)
where ci.Category_ID = 123

[bugzy]

Do I still need to set Cat_ID as PK on the two columns table?

[The Little Guy]

no, I would add a unique key on both the columns like this:

 

create unique index on cat_items (Category_ID, Item_ID);

[bugzy]

Thanks you the little guy