Efficient way to store order in mySQL for list of items

Question

I want to code cleaner and more efficiently and I wanted to know any other suggestions for the following problem:

I have a mySQL database that holds data about a set of photograph names. Oh, say 100 photograph names

Table 1: (photos) has the following fields: photo_id, photo_name

Ex data:

1 | sunshine.jpg
2 | cloudy.jpg
3 | rainy.jpg
4 | hazy.jpg
...

Table 2: (categories) has the following fields: category_id, category_name, category_order

Ex data:

1 | Summer Shots | 1,2,4
2 | Winter Shots | 2,3
3 | All Seasons  | 1,2,3,4
...

Is it efficient to store the order of the photos in this manner per entry via comma delimited values? It's one approach I have seen used before but I wanted to know if something else is faster in run time.

Using this way I don't think it is possible to do a direct INNER JOIN on the category table and photo table to get a single matched list of all the photographs per category.

Ex: Summer shots -> sunshine.jpg, cloudy.jpg, hazy.jpg because it was matched against 1,2,4

The iteration through all the categories and then the photos will have a O(n^2) and there has to be a better/faster way. Please educate me :)

Answer 1

it seems to me you could use another table for the relation between photos and categories with fields category_id, order_id and photo_id

I think it would be simpler to use, and you'll be able to do your join

I would also suggest you set photo_id as an index in that new table if you decide to use that solution.

-- EDIT --

table photo 'photo_id','photo_name'

• photo_id is a primary key, unique, with auto increment and possibly an index

table category 'category_id', 'category_name'

• category_id is a primary key, unique(unless you want it to be possible to have different names for a same category), with auto increment and possibly an index

table category_content 'category_id','photo_id','order'

• category_id is a foreign key pointing toward table category's column category_id
• photo_id is a foreign key pointing toward table photo's column photo_id
• primary key is a combined primary key between category_id and order
• photo_id and category_id are both index

Using 'index' somehow tells MySQL that you want to optimize research on the indexed columns, when you notice that many queries have WHERE and/or JOIN on specific columns, usually it is a good idea to set the column to be indexed. Also, unless it simplifies something, it's almost never a good idea to combine multiple data into a single one like you did, an exception could be combining phone number prefix with actual number and a few things like this, but they are exeptional cases. The way you hardcoded the order within the table would have been good if you used files, but in a database, you're better off using another table than using comma separators. Any questions?