Store and Retrieve Multiple Avatar Image Sizes

I am building a forum type site whereby the users will have an avatar. The main page will have a small version whereas the single page will have a large version of the avatar.

So for sake of bandwidth, I will have different sizes:

example.png
example_thumb.png

What is the preferred storage and retrieval method for the different sizes?

Here's what I have come up with so far:

User Table    UserId  |  AvatarName  |  AvatarExt
              1       |  example     |  .png

This way I can get the base and thumb version with the same query as long as I know that there is a '_thumb' version开发者_开发知识库

Or should I create a meta table like so:

UserMeta      UserId  |  Avatar             |  AvatarType
              1       |  example.png        |  original
              1       |  example_thumb.png  |  thumbnail

I would love some advice

You are on the right track.

Users:

id
username
...

Avatars

id
user_id
avatar_type(original, thumbnail)
file_name

Then you can do:

select * from avatars where user_id = $user_id and avatar_type='thumbnail'

This allows you to, say, allow users later on to have multiple avatars without modifying your tables.

Either one will work, though I would go with the meta table for ease of use, in particular if the extension is not expected to change.

I don't see any benefit in storing the filename and extension in separate columns.