Image Tagging System

Objective

Implement a flexible tagging system for images using a many-to-many relationship. This allows each image to have multiple tags and each tag to be applied to multiple images.

Step 1: Create Images Table

Create the main images table.

CREATE TABLE images (
    image_id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    filename VARCHAR(255) NOT NULL,
    image IMAGE(JPEG),
    description TEXT,
    uploaded_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Tags Table

Create a table for storing unique tags.

CREATE TABLE tags (
    tag_id INTEGER PRIMARY KEY,
    tag_name VARCHAR(50) NOT NULL UNIQUE,
    tag_category VARCHAR(50),
    usage_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 3: Create Junction Table

Create the many-to-many relationship table.

CREATE TABLE image_tags (
    image_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    tagged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    tagged_by VARCHAR(100),
    PRIMARY KEY (image_id, tag_id),
    FOREIGN KEY (image_id) REFERENCES images(image_id),
    FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);

Step 4: Insert Sample Images

Add sample images to the system.

INSERT INTO images (image_id, title, filename, description, uploaded_by) VALUES
    (1, 'Mountain Sunrise', 'mountain_sunrise.jpg', 'Beautiful mountain at dawn', 'photographer1'),
    (2, 'City Night', 'city_night.jpg', 'Urban skyline after dark', 'photographer2'),
    (3, 'Beach Waves', 'beach_waves.jpg', 'Ocean waves crashing', 'photographer1'),
    (4, 'Forest Trail', 'forest_trail.jpg', 'Hiking path through woods', 'photographer3'),
    (5, 'Portrait Studio', 'portrait_studio.jpg', 'Professional headshot', 'photographer2');

Step 5: Insert Tags

Create a set of reusable tags.

INSERT INTO tags (tag_id, tag_name, tag_category) VALUES
    (1, 'landscape', 'subject'),
    (2, 'nature', 'subject'),
    (3, 'urban', 'subject'),
    (4, 'portrait', 'subject'),
    (5, 'sunrise', 'time'),
    (6, 'sunset', 'time'),
    (7, 'night', 'time'),
    (8, 'outdoor', 'location'),
    (9, 'indoor', 'location'),
    (10, 'water', 'element'),
    (11, 'mountains', 'element'),
    (12, 'trees', 'element');

Step 6: Tag the Images

Associate tags with images through the junction table.

INSERT INTO image_tags (image_id, tag_id, tagged_by) VALUES
    -- Mountain Sunrise tags
    (1, 1, 'photographer1'),   -- landscape
    (1, 2, 'photographer1'),   -- nature
    (1, 5, 'photographer1'),   -- sunrise
    (1, 8, 'photographer1'),   -- outdoor
    (1, 11, 'photographer1'),  -- mountains

    -- City Night tags
    (2, 3, 'photographer2'),   -- urban
    (2, 7, 'photographer2'),   -- night
    (2, 8, 'photographer2'),   -- outdoor

    -- Beach Waves tags
    (3, 1, 'photographer1'),   -- landscape
    (3, 2, 'photographer1'),   -- nature
    (3, 8, 'photographer1'),   -- outdoor
    (3, 10, 'photographer1'),  -- water

    -- Forest Trail tags
    (4, 1, 'photographer3'),   -- landscape
    (4, 2, 'photographer3'),   -- nature
    (4, 8, 'photographer3'),   -- outdoor
    (4, 12, 'photographer3'),  -- trees

    -- Portrait Studio tags
    (5, 4, 'photographer2'),   -- portrait
    (5, 9, 'photographer2');   -- indoor

Step 7: Update Tag Usage Counts

Keep track of how often each tag is used.

UPDATE tags
SET usage_count = (
    SELECT COUNT(*) FROM image_tags WHERE image_tags.tag_id = tags.tag_id
);

SELECT tag_name, tag_category, usage_count
FROM tags
ORDER BY usage_count DESC;

Step 8: Find Images by Tag

Query images with a specific tag.

SELECT
    i.title,
    i.filename,
    i.uploaded_by
FROM images i
INNER JOIN image_tags it ON i.image_id = it.image_id
INNER JOIN tags t ON it.tag_id = t.tag_id
WHERE t.tag_name = 'landscape';

Step 9: Get All Tags for an Image

List all tags associated with a specific image.

SELECT
    i.title,
    t.tag_name,
    t.tag_category
FROM images i
INNER JOIN image_tags it ON i.image_id = it.image_id
INNER JOIN tags t ON it.tag_id = t.tag_id
WHERE i.image_id = 1
ORDER BY t.tag_category, t.tag_name;

Step 10: Find Images with Multiple Tags

Search for images matching multiple tags (AND logic).

SELECT
    i.title,
    COUNT(DISTINCT t.tag_id) as matching_tags
FROM images i
INNER JOIN image_tags it ON i.image_id = it.image_id
INNER JOIN tags t ON it.tag_id = t.tag_id
WHERE t.tag_name IN ('landscape', 'nature', 'outdoor')
GROUP BY i.image_id, i.title
HAVING COUNT(DISTINCT t.tag_id) = 3;

Step 11: Tag Cloud Query

Generate data for a tag cloud visualization.

SELECT
    tag_name,
    tag_category,
    usage_count,
    CASE
        WHEN usage_count >= 4 THEN 'large'
        WHEN usage_count >= 2 THEN 'medium'
        ELSE 'small'
    END as cloud_size
FROM tags
WHERE usage_count > 0
ORDER BY usage_count DESC;

Step 12: Images by Tag Category

Group images by tag category.

SELECT
    t.tag_category,
    COUNT(DISTINCT i.image_id) as image_count,
    COUNT(it.image_id) as tag_applications
FROM tags t
INNER JOIN image_tags it ON t.tag_id = it.tag_id
INNER JOIN images i ON it.image_id = i.image_id
GROUP BY t.tag_category
ORDER BY image_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS image_tags;
DROP TABLE IF EXISTS tags;
DROP TABLE IF EXISTS images;

Expected Outcomes

• Many-to-many relationship established
• Images can have multiple tags
• Tags reusable across images
• Tag usage statistics tracked
• Complex tag queries supported

Key Concepts Learned

• Junction table pattern
• Many-to-many relationships
• Composite primary keys
• Multi-table JOINs
• Tag counting and statistics