Efficiency question - Selecting numeric data from one field

I have a pair of tables and I need to search for numeric values in Table1 that match associated IDs on Table2. For example:

Table1

ID 开发者_Python百科| 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 I was thinking a LIKE would work, but if I searched for "1", I'd end up matching "11". I looked into SETs, but the MySQL docs state that the maximum number of elements is 64 and I have over 200 rows of items in Table1. I could wrap each item id with a character (e.g. "|1|") but that doesn't seem very efficient. Each Group will have unique items (e.g., there won't be two Cats in the same Group).

I found a similar topic as my problem and one of the answers suggested making another table, but I don't understand how that would work. A new table containing what, exactly?

The other option I have is to split the Contains into 6 separate columns, since there's never going to be more than 6 items in a Group, but then I'm not sure how to search all 6 columns without relying on six 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 * FROM Table2 WHERE C1 = '1' OR C2 = '1' OR C3 = '1' etc.

I'm not sure what the most efficient way of handling this is. I could use some advice from those with more experience with normalizing this kind of data please. Thank you.

I think it'd be best to create another table to normalize your data, however what you're proposing is not exactly what I'd suggest.

Realistically what you are modeling is a many-to-many relationship between table1 and table2. This means that one row in table1 can be associated with many rows in table2, and vice versa.

In order to create this kind of relation, you need a third table, which we can call rel_table1_table2 for now.

rel_table1_table2 will contain only primary key values from the two associated tables, which in this case seem to be table1.ID and table2.Category.

When you want to associate a row in table1 with a row in table2, you'd add a row to rel_table1_table2 with the primary key values from table1 and table2 respectively.

Example:

INSERT INTO rel_table1_table2 (ID, Category) VALUES (1, "Group 1")

When you need to find out what Items belong to a Category, you'd simply query your association table, for example:

SELECT i.Item from table1 t1 join rel_table1_table2 r on t1.ID=r.ID join table2 t2 on r.Category=t2.Category WHERE t2.Category="Group 3"

Does that make sense?

That "new" table would contain one row for each category an 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 your example data, the animal_categories table would contain:

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

Instead of using "like" use "REGEXP" so that you don't get "11" when looking for "1"

Break Table2.Contains in another table which joins Item and Category:

Item         Item_Category           Category
------       --------------          ---------
ID (1)----(*)ItemID                  Name
Name         CategoryID(*)-------(1) ID

Now, your query will look like:

SELECT Category.* FROM Category, Item_Category
WHERE (Item_Category.CategoryID = Category.ID)
  AND (Item_Category.ItemID IN (1, 2, 3, 11))

It seems like your problem is the way you are using the rows in Table 2. In databases it should always trigger a red flag when you find yourself using a list of values in a row.

Rather than having each category be in a single row in table 2, how about using the same category in multiple rows, with the Contains column only storing a single value. Your example could be changed to:

Table 1

ID | Item
1    Cat
3    Frog
9    Dog
11   Horse

Table 2

Category | Contains
Group 1    1
Group 2    3
Group 2    9
Group 3    3
Group 3    9
Group 3    11

Now when you want to find out "What items does group 2 contain?", you can write a query for that which selects all of the "Group 2" category rows from Table 2. When you want to find out, "What is the name of item 9", you can write a query that selects a row from Table 1.