Objective

Create a system for storing user profile pictures with multiple size variants. This pattern supports efficient display at different sizes without runtime resizing.

Step 1: Create Users Table with Profile Picture

Create a users table with profile picture columns.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    display_name VARCHAR(100),
    profile_picture IMAGE(JPEG),
    profile_picture_small IMAGE(JPEG),
    profile_picture_thumb IMAGE(JPEG),
    avatar_updated_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Dedicated Profile Pictures Table

For more flexibility, create a separate profile pictures table.

CREATE TABLE profile_pictures (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE,
    original IMAGE(JPEG),
    large IMAGE(JPEG),
    medium IMAGE(JPEG),
    small IMAGE(JPEG),
    thumbnail IMAGE(JPEG),
    original_width INTEGER,
    original_height INTEGER,
    file_size BIGINT,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Step 3: Insert Sample Users

Add sample users to the system.

INSERT INTO users (user_id, username, email, display_name) VALUES
    (1, 'alice_j', 'alice@example.com', 'Alice Johnson'),
    (2, 'bob_smith', 'bob@example.com', 'Bob Smith'),
    (3, 'carol_w', 'carol@example.com', 'Carol Williams'),
    (4, 'david_b', 'david@example.com', 'David Brown'),
    (5, 'eve_davis', 'eve@example.com', 'Eve Davis');

Step 4: Insert Profile Picture Records

Add profile picture metadata for users.

INSERT INTO profile_pictures (id, user_id, original_width, original_height, file_size, uploaded_at) VALUES
    (1, 1, 1024, 1024, 256000, CURRENT_TIMESTAMP),
    (2, 2, 800, 800, 180000, CURRENT_TIMESTAMP),
    (3, 3, 1200, 1200, 320000, CURRENT_TIMESTAMP);

Step 5: Update User Avatar Timestamps

Track when profile pictures were updated.

UPDATE users
SET avatar_updated_at = CURRENT_TIMESTAMP
WHERE user_id IN (1, 2, 3);

SELECT username, display_name, avatar_updated_at
FROM users
WHERE avatar_updated_at IS NOT NULL;

Step 6: Create Avatar Sizes Reference Table

Define standard avatar sizes.

CREATE TABLE avatar_sizes (
    size_name VARCHAR(20) PRIMARY KEY,
    width INTEGER NOT NULL,
    height INTEGER NOT NULL,
    use_case VARCHAR(100)
);

INSERT INTO avatar_sizes (size_name, width, height, use_case) VALUES
    ('original', 1024, 1024, 'Full resolution storage'),
    ('large', 512, 512, 'Profile page header'),
    ('medium', 256, 256, 'Comments and posts'),
    ('small', 128, 128, 'Navigation bar'),
    ('thumbnail', 48, 48, 'Lists and mentions');

Step 7: Query Users with Profile Pictures

Get users along with their profile picture status.

SELECT
    u.username,
    u.display_name,
    CASE
        WHEN pp.id IS NOT NULL THEN 'Has Picture'
        ELSE 'No Picture'
    END as avatar_status,
    pp.original_width,
    pp.original_height,
    pp.file_size
FROM users u
LEFT JOIN profile_pictures pp ON u.user_id = pp.user_id
ORDER BY u.username;

Step 8: Find Users Without Profile Pictures

Identify users who haven't uploaded a profile picture.

SELECT
    u.user_id,
    u.username,
    u.email,
    u.created_at
FROM users u
LEFT JOIN profile_pictures pp ON u.user_id = pp.user_id
WHERE pp.id IS NULL;

Step 9: Profile Picture Statistics

Generate statistics about profile pictures.

SELECT
    COUNT(DISTINCT u.user_id) as total_users,
    COUNT(DISTINCT pp.user_id) as users_with_pictures,
    COUNT(DISTINCT u.user_id) - COUNT(DISTINCT pp.user_id) as users_without_pictures,
    SUM(pp.file_size) as total_storage_bytes,
    AVG(pp.file_size) as avg_file_size
FROM users u
LEFT JOIN profile_pictures pp ON u.user_id = pp.user_id;

Step 10: Recent Avatar Updates

Find recently updated profile pictures.

SELECT
    u.username,
    u.display_name,
    pp.uploaded_at,
    pp.file_size
FROM users u
INNER JOIN profile_pictures pp ON u.user_id = pp.user_id
ORDER BY pp.uploaded_at DESC
LIMIT 10;

Step 11: Large Profile Pictures

Find oversized profile pictures that may need optimization.

SELECT
    u.username,
    pp.original_width,
    pp.original_height,
    pp.file_size,
    CASE
        WHEN pp.file_size > 500000 THEN 'Too Large'
        WHEN pp.file_size > 250000 THEN 'Consider Optimizing'
        ELSE 'Good Size'
    END as size_recommendation
FROM users u
INNER JOIN profile_pictures pp ON u.user_id = pp.user_id
WHERE pp.file_size > 200000
ORDER BY pp.file_size DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS profile_pictures;
DROP TABLE IF EXISTS avatar_sizes;
DROP TABLE IF EXISTS users;

Expected Outcomes

Profile pictures stored with size variants
Users linked to their avatar images
Storage statistics calculated
Missing avatars identified
Size optimization recommendations

Avatar Size Standards

Size	Dimensions	Use Case
Original	1024x1024	Storage master
Large	512x512	Profile pages
Medium	256x256	Posts/comments
Small	128x128	Navigation
Thumbnail	48x48	Lists/mentions

Key Concepts Learned

Multiple image size variants
User-profile picture relationship
Storage optimization tracking
Missing data identification
Size recommendation queries

Profile Picture Storage