Sound Effect Library

Objective

Create a cataloging system for sound effects with categories and tags. This enables quick discovery of audio assets for game development, video production, and multimedia projects.

Step 1: Create Sound Effects Table

Create a table for sound effect storage.

CREATE TABLE sound_effects (
    id INTEGER PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    audio_file AUDIO(WAV),
    category VARCHAR(50) NOT NULL,
    subcategory VARCHAR(50),
    description TEXT,
    duration_ms INTEGER,
    file_size BIGINT,
    sample_rate INTEGER DEFAULT 44100,
    channels INTEGER DEFAULT 2,
    bit_depth INTEGER DEFAULT 16,
    is_loopable BOOLEAN DEFAULT FALSE,
    license_type VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Tags Table

Create a table for tagging sound effects.

CREATE TABLE sfx_tags (
    tag_id INTEGER PRIMARY KEY,
    tag_name VARCHAR(50) NOT NULL UNIQUE,
    usage_count INTEGER DEFAULT 0
);

CREATE TABLE sound_effect_tags (
    sound_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    PRIMARY KEY (sound_id, tag_id),
    FOREIGN KEY (sound_id) REFERENCES sound_effects(id),
    FOREIGN KEY (tag_id) REFERENCES sfx_tags(tag_id)
);

Step 3: Insert Sample Sound Effects

Add sample sound effects to the library.

INSERT INTO sound_effects (id, name, category, subcategory, description, duration_ms, file_size, is_loopable, license_type) VALUES
    (1, 'Footstep Grass', 'Foley', 'Footsteps', 'Single footstep on grass surface', 450, 39690, FALSE, 'royalty-free'),
    (2, 'Door Creak', 'Foley', 'Doors', 'Old wooden door creaking open slowly', 2100, 185220, FALSE, 'royalty-free'),
    (3, 'Explosion Large', 'Impact', 'Explosions', 'Large cinematic explosion with debris', 3500, 308700, FALSE, 'royalty-free'),
    (4, 'UI Click', 'Interface', 'Buttons', 'Clean button click for menus', 120, 10584, FALSE, 'royalty-free'),
    (5, 'Ambient Forest', 'Ambience', 'Nature', 'Forest ambience with birds and wind', 30000, 2646000, TRUE, 'royalty-free'),
    (6, 'Sword Swing', 'Combat', 'Melee', 'Fast sword swing through air', 380, 33516, FALSE, 'royalty-free'),
    (7, 'Magic Spell Cast', 'Fantasy', 'Magic', 'Mystical spell casting sound', 1800, 158760, FALSE, 'royalty-free'),
    (8, 'Car Engine Idle', 'Vehicles', 'Cars', 'Sedan engine idling', 5000, 441000, TRUE, 'royalty-free'),
    (9, 'Rain Heavy', 'Weather', 'Rain', 'Heavy rainfall on roof', 60000, 5292000, TRUE, 'royalty-free'),
    (10, 'Notification Chime', 'Interface', 'Notifications', 'Pleasant notification alert', 800, 70560, FALSE, 'royalty-free'),
    (11, 'Gunshot Pistol', 'Combat', 'Firearms', 'Single pistol shot', 650, 57330, FALSE, 'royalty-free'),
    (12, 'Thunder Rumble', 'Weather', 'Thunder', 'Distant thunder rolling', 4500, 396900, FALSE, 'royalty-free');

Step 4: Insert Tags

Add tags for categorizing sound effects.

INSERT INTO sfx_tags (tag_id, tag_name) VALUES
    (1, 'nature'),
    (2, 'indoor'),
    (3, 'outdoor'),
    (4, 'action'),
    (5, 'calm'),
    (6, 'intense'),
    (7, 'loop'),
    (8, 'one-shot'),
    (9, 'UI'),
    (10, 'game'),
    (11, 'film'),
    (12, 'horror');

Step 5: Tag Sound Effects

Associate tags with sound effects.

INSERT INTO sound_effect_tags (sound_id, tag_id) VALUES
    (1, 1), (1, 3), (1, 8), (1, 10),    -- Footstep Grass
    (2, 2), (2, 8), (2, 12),            -- Door Creak
    (3, 4), (3, 6), (3, 8), (3, 11),    -- Explosion
    (4, 9), (4, 8), (4, 10),            -- UI Click
    (5, 1), (5, 3), (5, 5), (5, 7),     -- Ambient Forest
    (6, 4), (6, 8), (6, 10),            -- Sword Swing
    (7, 4), (7, 8), (7, 10),            -- Magic Spell
    (8, 3), (8, 7),                      -- Car Engine
    (9, 1), (9, 3), (9, 5), (9, 7),     -- Rain Heavy
    (10, 9), (10, 8),                    -- Notification
    (11, 4), (11, 6), (11, 8),          -- Gunshot
    (12, 1), (12, 3), (12, 6);          -- Thunder

Step 6: Update Tag Usage Counts

Calculate how often each tag is used.

UPDATE sfx_tags
SET usage_count = (
    SELECT COUNT(*) FROM sound_effect_tags WHERE sound_effect_tags.tag_id = sfx_tags.tag_id
);

SELECT tag_name, usage_count
FROM sfx_tags
ORDER BY usage_count DESC;

Step 7: Search by Category

Find sound effects by category.

SELECT
    name,
    subcategory,
    duration_ms,
    is_loopable
FROM sound_effects
WHERE category = 'Combat'
ORDER BY name;

Step 8: Search by Tags

Find sound effects with specific tags.

SELECT
    sf.name,
    sf.category,
    sf.duration_ms
FROM sound_effects sf
INNER JOIN sound_effect_tags sft ON sf.id = sft.sound_id
INNER JOIN sfx_tags t ON sft.tag_id = t.tag_id
WHERE t.tag_name = 'nature'
ORDER BY sf.name;

Step 9: Find Loopable Sounds

Get all sounds suitable for looping.

SELECT
    name,
    category,
    duration_ms / 1000 as duration_seconds,
    file_size / 1024 as size_kb
FROM sound_effects
WHERE is_loopable = TRUE
ORDER BY duration_ms DESC;

Step 10: Category Statistics

Analyze library by category.

SELECT
    category,
    COUNT(*) as sound_count,
    SUM(duration_ms) / 1000 as total_seconds,
    SUM(file_size) / 1048576 as total_mb,
    SUM(CASE WHEN is_loopable THEN 1 ELSE 0 END) as loopable_count
FROM sound_effects
GROUP BY category
ORDER BY sound_count DESC;

Step 11: Duration Range Query

Find sounds by duration.

SELECT
    name,
    category,
    duration_ms,
    CASE
        WHEN duration_ms < 500 THEN 'Very Short'
        WHEN duration_ms < 2000 THEN 'Short'
        WHEN duration_ms < 10000 THEN 'Medium'
        ELSE 'Long'
    END as length_category
FROM sound_effects
WHERE duration_ms BETWEEN 100 AND 3000
ORDER BY duration_ms;

Step 12: Tag Cloud Data

Generate data for tag cloud display.

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

Cleanup (Optional)

DROP TABLE IF EXISTS sound_effect_tags;
DROP TABLE IF EXISTS sfx_tags;
DROP TABLE IF EXISTS sound_effects;

Expected Outcomes

• Sound effects cataloged by category
• Tags enable flexible search
• Loopable sounds identified
• Duration filtering works
• Library statistics available

Sound Effect Categories

Key Concepts Learned

• Audio cataloging patterns
• Tagging system with junction table
• Category and subcategory organization
• Duration-based filtering
• Tag cloud generation