CODEWITHSUNDEEP
mysqlsqldatabasedatabase-designforeign-keys
kmdent
kmdent

Reputation: 1587

Storing a SQL string array, and subsequent querying

I want to store a variable size list of image tags in a SQL database and then be able to search the database for images based on the tags. I am currently pulling the tag lists out of the database, and checking if the query tag is contained in the array. Is there any way I can format these as sql queries instead of doing them in php after I pull the tags out?

The list is stored as comma separated values of the form:

"sunny, beach, palms" -im1

"palms, rainy, clouds" -im2

"winter, snow, snowman, swag" -img

I would like to be able to get the union of the query tags: A query of "beach, palms" should return im1 and im2

Thanks

Upvotes: 10

Views: 11933

Answers (2)

McGarnagle
McGarnagle

Reputation: 102723

You'll want to avoid delimited lists in your database-- as you've found, that negates the advantages of using a database to store structured data. Instead you should create a new table with one tag per row, and then reference that table, using a foreign key, from your main images table.

Images Table

  • id
  • image_name

Tags Table

  • id
  • tag_name

Image_Tags Table

  • image_id (references Images table primary key)
  • tag_id (references Tags table primary key)

This way, you'll be able to run queries like this:

SELECT t.tag_name, i.image_name FROM image_tags it
    INNER JOIN images i on it.image_id = i.id
    INNER JOIN tags t on it.tag_id = t.id
WHERE t.tag_name in ('beach', 'palms')

Upvotes: 18

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

@dbaseman provided a good answer (+1), but it might be a bit of an overkill. Assuming the only thing you want to know about a tag is its name, the model with only 2 tables will probably behave better in practice (one less JOIN and better clustering of data):

image table:

  • id PK
  • image_name

image_tag table:

  • tag_name PK
  • image_id PK, FK -> image

The order of fields in image_tag's PK is important: if you keep it as {tag_name, image_id} it will nicely cluster the data (i.e. keep images with same tags together), exactly as you need it for querying:

SELECT DISTINCT image.*
FROM image JOIN image_tag ON (image.id = image_tag.image_id)
WHERE image_tag.tag_name IN ('beach', 'palms')

On the other hand, if you also need a tag description, tag author etc..., @dbaseman's model is fine, except you might consider flipping the order of fields in Image_Tags PK (for the same clustering reasons as above).

Upvotes: 7

Related Questions