CODEWITHSUNDEEP
phpmysqldatabasedatabase-designsearch
Silvertiger
Silvertiger

Reputation: 1680

MySQL search in field (or other solutions)

I have a table with products that fall under specific categories, but the products within each category can contain multiple meta data tracking field

Table: products

 id    name          category     metadata
 1   something          1      blue,red,purple
 2   something else     2      left,right,middle

I have been trying to contemplate the best method to have a single product table but can't seem to squeeze the metadata in conveniently. for now I have created a table with all the metadata and fields for tracking the related category (the sequence is so i can order them withing a dropdown etc..)

Updated table: products

 id    name          category   metadata
 1   something          1         1,2,3
 2   something else     2         4,5,6

Table: metadata

 id   category   sequence   option
 1        1         1        blue
 2        1         2        red
 3        1         3        purple
 4        2         1        left
 5        2         2        right
 6        2         3        middle

If this format makes sense .. I am trying to generate a query that will search for values in my product table and grab each and all of the related meta values. The issue I am having is trying to find a unique value in the products field. if I do a MySQL search for LIKE(%1%) I will get matches for 1, 11, 21, 31 etc ... I thought of adding a leading and trailing comma to the field by default and then search for ",1," which would be unique .. but there has to be a better way ...

Any recommendations (regarding format or query)?

Upvotes: 0

Views: 91

Answers (2)

McGarnagle
McGarnagle

Reputation: 102783

It's not an ideal design to have comma-separated values within a single database field. Aside from the problem you mentioned (difficult to search), your queries will be less efficient, as the DB won't be able to use indices for the lookup.

I'd recommend making a separate table products_metadata with a many-to-one relationship to the products table. Have the *metadata_id*, and the *product_id*, which is a foreign key linking back to the products table. That will make your job much easier.

Upvotes: 4

eggyal
eggyal

Reputation: 125925

You want to add another table, which links products to their metadata. It will have two columns: productid and metadataid which refer to the relevant entries in the products and metadata tables respectively. Then you no longer keep metadata in the products table, but JOIN them together as required.

Upvotes: 1

Related Questions