[eluser]boltsabre[/eluser]
Hi, I've got myself into a situation with my databasing that I've never had to tackle before, just looking for some general insight to the issue.
I've an image gallery (with various 'categories'), and images can belong to a maximum of 3 categories (and must belong to at least 1 category).
When a user goes to a certain category, I need to build a sql select statement like this:
Code:
SELECT id FROM img_gal_images WHERE (and this is the problem...)
To get all the images for that category (using pagination to display 30 images per page)
Thoughts on how to do this include:
1. In the 'img_gal_images' table have a column called 'cat_ids' (varchar 12) which stores a string containing the category ids separated by (whatever, pipe, space, comma).
Then my sql "WHERE" statement could do a text %wildcard search on that column searching for the category id.
However, due to pagination, I need to do a 'count' on the entire table of images what belong to a certain category (Initial gallery will have 200-300 images, but could grow to 10,000+ over the next 2-4 years).
2. Have a new table "img_id_by_cat_id" which has "cat_id" and "img_id" columns, and each image gets a row in this table for each category it is in (min. of 1, max of 3).
With indexes on both columns, I suspect this will be much faster than idea 1 using wildcards for getting the image count for pagination. The sql used to find images in this table that belong to a certain category would be something like:
Code:
SELECT COUNT(img_id) AS c FROM img_id_by_cat_id WHERE img_id = $img_id AND cat_id = $cat_id
But when actually getting the 30 pictures for that page I would then have to have my select statement reference both tables, something like this:
Code:
SELECT id, height, width, alt_text, etc
FROM img_gal_images, img_id_by_cat_id
WHERE img_id = $img_id
If anyone has any experience in similar situations, I'd be very grateful for you input on what you think is the best method to implement. Or is there some better method I have not even considered, or any pitfalls to either of the above methods that I haven't considered.
Many thanks in advance guys and girls!
