Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 996
kmdent Avatar asked Apr 23 '12 02:04

kmdent


People also ask

Can you store an array in SQL?

Store Arrays Within a Different SQL Data Type During the MySQL 5.7 update and upwards, the language now supports JSON as a data type. JSON provides SQL a convenient method to store complex data types like arrays, sets, maps, dictionaries, and many more. The fastest way you can mock an array within SQL is to store it as a string.

What is an array in SQL?

An array is a data structure that stores elements of the same built-in data-type in a contiguous memory location. Arrays can be one dimensional or multidimensional. They are very useful because they allow for easy access to data elements. This is a guide to Array in SQL.

Is it possible to store an array in a relational database?

This is an effective way to store an array within SQL. Still, it’s considered a bad design to force an array into a relational database when you can easily store it in a new table and configure the relations and constraints. DelftStack is a collective effort contributed by software geeks like you.

How do I mock an array in SQL?

JSON provides SQL a convenient method to store complex data types like arrays, sets, maps, dictionaries, and many more. The fastest way you can mock an array within SQL is to store it as a string.


2 Answers

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')
like image 192
McGarnagle Avatar answered Oct 14 '22 15:10

McGarnagle


@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).

like image 43
Branko Dimitrijevic Avatar answered Oct 14 '22 13:10

Branko Dimitrijevic