mysql - Efficiency question - Selecting numeric data from one field -


i have pair of tables , need search numeric values in table1 match associated ids on table2. example:

table1

id | item 1    cat 3    frog 9    dog 11   horse 

table2

category | contains group 1   1 group 2   3|9 group 3   3|9|11 

originally thinking work, if searched "1", i'd end matching "11". looked sets, mysql docs state maximum number of elements 64 , have on 200 rows of items in table1. wrap each item id character (e.g. "|1|") doesn't seem efficient. each group have unique items (e.g., there won't 2 cats in same group).

i found similar topic problem , 1 of answers suggested making table, don't understand how work. new table containing what, exactly?

the other option have split contains 6 separate columns, since there's never going more 6 items in group, i'm not sure how search 6 columns without relying on 6 or queries:

category |  c1  |  c2  |  c3  |  c4 (etc) group 1    1      null   null   null group 2    3       9     null   null group 3    3       9      11    null  select * table2 c1 = '1' or c2 = '1' or c3 = '1' etc. 

i'm not sure efficient way of handling is. use advice more experience normalizing kind of data please. thank you.

that "new" table contain 1 row each category animal belongs to.

create table animal(    animal_id   ,name   ,primary key(animal_id) )  create table category(    category_id   ,name   ,primary key(category_id) )  create table animal_categories(    animal_id   ,category_id   ,primary key(animal_id, category_id)  ) 

for example data, animal_categories table contain:

   category_id | animal_id      +-----------+------------+    |     1     |       1    |    |     2     |       3    |    |     2     |       9    |    |     3     |       3    |    |     3     |       9    |    |     3     |      11    |    +-----------+------------+ 

Comments

Popular posts from this blog

apache - Add omitted ? to URLs -

redirect - bbPress Forum - rewrite to wwww.mysite prohibits login -

php - How can I stop spam on my custom forum/blog? -