Profile Picture Storage

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

Key Concepts Learned

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