How to flag a photo as the users "profile pic"?

Question

I'm building an app where each user can have multiple profiles. Users can upload multiple photos for each profile, etc.

The folder structure will be something like

public_html/
upload/
    user-123/
        profile-199/
        profile-321/
            images/
                123423.png

I'm going to have a one to many relationship between each profile and each photo, but I'm trying to see how I should flag a photo as special and the users "profile photo".

My proposed table structure is something like this

id
profile_id
filename  (or maybe extension only, since filename will most likely just be the primary id for that photo)
profile (boolean field of whether this a profile pic or not)
date_added

How should I treat special images, like profile pictures?

Edit: Sorry if I wasnt clear. I want each user to be able to have multiple profiles, each profile to have many photos. Just like for Facebook, you can have many photos, but only one profile pic at any time.

Answer 1

That would work but it would have a problem that more than one photo could be marked as a profile photo unless you take special care to prevent this.

An alternative design is to have a table with columns user_id and special_photo_id where the photo_id is a foreign key into the photos table. This has a slightly different issue: now it is possible to have a special photo that doesn't belong to the user.

Answer 2

You could have a field in your users table(where its username and other info is stored) with a profile_id field init pointing to the profiles table. That way you get one possibility for the profile and away to identify it.

Thinking about it that will only work if you do have a users table and a separated profiles table.

Answer 3

How about the image table looks like this:

id
profile_id
filename    
date_added

and add the column 'profile_image_id' in the profile table? This will be your special image's id.

I imagine you are going to have a lot more non-special images than special. So, most of the time the field 'profile(boolean)' that you proposed will be 0.

Edit: I believe @Iznogood is saying the same thing.