Efficient way to store order in mySQL for list of items
- by ninumedia
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 :)
