How to find and update the next record in a linking table

Question

I have a classifieds system I'm working on.

People are able to add photos to a classified, but I only display one when displaying the list of classifieds.

To do that, I have a linking table between classifieds and photos that has a "is_main" boolean field.

When someone deletes one of their classified photos, I want to:

1) See if there is more than that photo tied to the classified.
2) If there is, update the next photo and set that "is_main" field to TRUE.

Just trying to find out the most efficient way to do this.

Answer 1

In my opinion, I find storing image names in the database a bit redundant.
I'd rather make a directory named after classified id, and store corresponding images there.

So, if user deletes a photo, he just deletes a photo.

To keep one photo as main one, I'd call it main.jpg.
if it's going to be deleted, I'd read directory content and rename whatever photo came first.
that's all

Answer 2

In my opinion you should not set a 'is_main' flag in the photo table but add the ID of that photo to the classified record. Finding the 'main' Photo for display purposes is much faster (this happens often, I assume).

When you delete a photo from the photo table you need to see if it is the one referenced in the classifieds table or not. If it is, then you can select the next photo associated with that (e.g. by using min(PhotoID) or min(Created), where created is the datetime when so photo is added to the photo table.

Conclusion: the is _main property of a photo is not a flag on the photo table but rather a FK in the classifieds table.