database design - How to assign multiple ids to one row in MySQL? -

let's have table these records of tags:

category   id  apples     1 orange     2 

and have table row

data catid ...    1 

with setup can retrieve row in apples page, proper way assign both apples & orange row? need change catid field integer varchar , add second id value 1,2 , edit query like:

 select * table catid '%1%'   select * table catid '%2%' 

instead of

  select * table catid='1'   select * table catid='2' 

i'm not sure if proper way? tell how it? basically, don't want duplicate whole row, add id it.

as others have suggested, many-to-many relationship represented in physical model junction table. i'll leg work , illustrate you:

the category_item junction table. has composite pk consisting of fks migrated other 2 tables. example data...

category:

category_id category ----------- -------- 1           apple 2           orange 

item:

item_id     name -------     ---- 1           foo 2           bar 

category_item:

category_id item_id ----------- ------- 1           1 2           1 1           2 

the above means: "foo both apple , orange, bar apple".

the pk ensures given combination of category , item cannot exist more once. category either connected item of isn't - cannot connected multiple times.

since want search items of given category, order of fields in pk {category_id, item_id} underlying index can satisfy query. exact explanation why beyond scope - if interested warmly recommend reading use index, luke!.

and since innodb uses clustering, store items belonging same category physically close together, may rather beneficial i/o of query above.

(if wanted query categories of given item, you'd need flip order of fields in index.)