Brand Asset Library

Objective

Create a centralized brand asset library for managing logos, brand guidelines, and marketing materials. This ensures brand consistency across all channels and teams.

Step 1: Create Asset Categories Table

Define asset categories.

CREATE TABLE asset_categories (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_id INTEGER,
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (parent_id) REFERENCES asset_categories(id)
);

Step 2: Create Brand Assets Table

Store brand assets.

CREATE TABLE brand_assets (
    id INTEGER PRIMARY KEY,
    category_id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    asset_type VARCHAR(50) NOT NULL,
    file_image IMAGE(PNG),
    file_pdf PDF,
    format VARCHAR(20),
    width INTEGER,
    height INTEGER,
    file_size BIGINT,
    color_space VARCHAR(20),
    usage_guidelines TEXT,
    tags VARCHAR(500),
    version VARCHAR(20) DEFAULT '1.0',
    is_approved BOOLEAN DEFAULT TRUE,
    is_public BOOLEAN DEFAULT FALSE,
    download_count INTEGER DEFAULT 0,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES asset_categories(id)
);

Step 3: Create Asset Usage Table

Track asset usage and downloads.

CREATE TABLE asset_usage (
    id INTEGER PRIMARY KEY,
    asset_id INTEGER NOT NULL,
    used_by VARCHAR(100) NOT NULL,
    usage_type VARCHAR(50),
    project_name VARCHAR(255),
    download_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (asset_id) REFERENCES brand_assets(id)
);

Step 4: Insert Asset Categories

Add category hierarchy.

INSERT INTO asset_categories (id, name, description, parent_id, sort_order) VALUES
    (1, 'Logos', 'Official company logos', NULL, 1),
    (2, 'Brand Guidelines', 'Brand identity guidelines', NULL, 2),
    (3, 'Marketing Materials', 'Marketing and promotional assets', NULL, 3),
    (4, 'Social Media', 'Social media assets', NULL, 4),
    -- Logo subcategories
    (5, 'Primary Logo', 'Main company logo variations', 1, 1),
    (6, 'Secondary Logos', 'Alternative logo formats', 1, 2),
    (7, 'Icon/Favicon', 'App icons and favicons', 1, 3),
    -- Marketing subcategories
    (8, 'Print Materials', 'Print-ready assets', 3, 1),
    (9, 'Digital Banners', 'Web and email banners', 3, 2),
    (10, 'Presentations', 'PowerPoint templates', 3, 3),
    -- Social media subcategories
    (11, 'Profile Images', 'Social profile pictures', 4, 1),
    (12, 'Cover Images', 'Social cover photos', 4, 2),
    (13, 'Post Templates', 'Social post templates', 4, 3);

Step 5: Insert Brand Assets

Add sample assets.

INSERT INTO brand_assets (id, category_id, name, description, asset_type, format, width, height, file_size, color_space, usage_guidelines, tags, version, is_public, created_by) VALUES
    -- Primary logos
    (1, 5, 'Primary Logo - Full Color', 'Main logo for light backgrounds', 'logo', 'PNG', 2000, 500, 125000, 'RGB', 'Use on white or light backgrounds only. Minimum size 100px wide.', 'logo,primary,color,main', '2.0', TRUE, 'brand_team'),
    (2, 5, 'Primary Logo - White', 'White logo for dark backgrounds', 'logo', 'PNG', 2000, 500, 98000, 'RGB', 'Use on dark or colored backgrounds. Maintain contrast ratio.', 'logo,primary,white,dark', '2.0', TRUE, 'brand_team'),
    (3, 5, 'Primary Logo - Black', 'Black logo for special applications', 'logo', 'PNG', 2000, 500, 95000, 'RGB', 'Use for grayscale or single-color printing.', 'logo,primary,black,mono', '2.0', TRUE, 'brand_team'),
    -- Secondary logos
    (4, 6, 'Horizontal Logo', 'Wide format logo variant', 'logo', 'PNG', 3000, 400, 145000, 'RGB', 'For horizontal spaces. Minimum width 200px.', 'logo,horizontal,wide', '1.5', TRUE, 'brand_team'),
    (5, 6, 'Stacked Logo', 'Vertical stacked format', 'logo', 'PNG', 800, 1000, 112000, 'RGB', 'For vertical spaces and square areas.', 'logo,stacked,vertical', '1.5', TRUE, 'brand_team'),
    -- Icons
    (6, 7, 'App Icon', 'Mobile app icon', 'icon', 'PNG', 1024, 1024, 85000, 'RGB', 'For iOS and Android app stores.', 'icon,app,mobile', '3.0', TRUE, 'brand_team'),
    (7, 7, 'Favicon', 'Website favicon', 'icon', 'PNG', 32, 32, 2500, 'RGB', 'For browser tabs and bookmarks.', 'favicon,browser,icon', '1.0', TRUE, 'brand_team'),
    -- Digital banners
    (8, 9, 'Email Header', 'Standard email header banner', 'banner', 'PNG', 600, 200, 75000, 'RGB', 'For email marketing campaigns.', 'email,header,banner', '1.2', FALSE, 'marketing'),
    (9, 9, 'Web Banner - Leaderboard', 'Standard 728x90 banner', 'banner', 'PNG', 728, 90, 45000, 'RGB', 'Standard web advertising format.', 'web,banner,leaderboard', '1.0', FALSE, 'marketing'),
    (10, 9, 'Web Banner - Skyscraper', 'Standard 160x600 banner', 'banner', 'PNG', 160, 600, 52000, 'RGB', 'Sidebar advertising format.', 'web,banner,skyscraper', '1.0', FALSE, 'marketing'),
    -- Social media assets
    (11, 11, 'LinkedIn Profile', 'LinkedIn company profile image', 'social', 'PNG', 300, 300, 35000, 'RGB', 'Square format for LinkedIn.', 'linkedin,profile,social', '1.0', FALSE, 'social_team'),
    (12, 12, 'Twitter Cover', 'Twitter header image', 'social', 'PNG', 1500, 500, 125000, 'RGB', 'Twitter/X header dimensions.', 'twitter,cover,header', '1.0', FALSE, 'social_team');

Step 6: Insert Usage Records

Track asset downloads.

INSERT INTO asset_usage (id, asset_id, used_by, usage_type, project_name, download_date) VALUES
    (1, 1, 'marketing_team', 'download', 'Q1 Campaign', '2024-01-15 10:00:00'),
    (2, 1, 'external_agency', 'download', 'Annual Report', '2024-01-18 14:30:00'),
    (3, 2, 'web_team', 'download', 'Website Refresh', '2024-01-20 09:15:00'),
    (4, 8, 'email_team', 'download', 'Newsletter Jan', '2024-01-05 11:00:00'),
    (5, 8, 'email_team', 'download', 'Newsletter Feb', '2024-02-01 10:30:00'),
    (6, 11, 'social_team', 'download', 'Profile Update', '2024-01-10 16:00:00'),
    (7, 6, 'mobile_team', 'download', 'App Store Update', '2024-01-22 13:45:00');

Step 7: Update Download Counts

Calculate asset downloads.

UPDATE brand_assets
SET download_count = (
    SELECT COUNT(*) FROM asset_usage
    WHERE asset_usage.asset_id = brand_assets.id
);

SELECT name, download_count FROM brand_assets WHERE download_count > 0;

Step 8: Browse Asset Library

Get assets by category.

SELECT
    ba.name,
    ba.asset_type,
    ba.format,
    ba.width || 'x' || ba.height as dimensions,
    ba.file_size / 1024 as size_kb,
    ba.version,
    ba.download_count
FROM brand_assets ba
WHERE ba.category_id = 5
  AND ba.is_approved = TRUE
ORDER BY ba.name;

Step 9: Search Assets by Tags

Find assets using tags.

SELECT
    ba.name,
    ac.name as category,
    ba.asset_type,
    ba.tags
FROM brand_assets ba
INNER JOIN asset_categories ac ON ba.category_id = ac.id
WHERE ba.tags LIKE '%logo%'
  AND ba.is_approved = TRUE
ORDER BY ba.download_count DESC;

Step 10: Category Summary

Get asset count by category.

SELECT
    ac.name as category,
    COUNT(ba.id) as asset_count,
    SUM(ba.download_count) as total_downloads
FROM asset_categories ac
LEFT JOIN brand_assets ba ON ac.id = ba.category_id
WHERE ac.parent_id IS NOT NULL
GROUP BY ac.id, ac.name
ORDER BY asset_count DESC;

Step 11: Most Downloaded Assets

Get popular assets.

SELECT
    ba.name,
    ac.name as category,
    ba.asset_type,
    ba.download_count,
    ba.created_by
FROM brand_assets ba
INNER JOIN asset_categories ac ON ba.category_id = ac.id
WHERE ba.is_approved = TRUE
ORDER BY ba.download_count DESC
LIMIT 10;

Step 12: Usage Report by Team

Analyze downloads by team.

SELECT
    au.used_by,
    COUNT(*) as downloads,
    COUNT(DISTINCT au.asset_id) as unique_assets,
    COUNT(DISTINCT au.project_name) as projects
FROM asset_usage au
GROUP BY au.used_by
ORDER BY downloads DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS asset_usage;
DROP TABLE IF EXISTS brand_assets;
DROP TABLE IF EXISTS asset_categories;

Expected Outcomes

• Brand assets organized
• Categories structured
• Downloads tracked
• Search by tags works
• Usage analytics available

Asset Types

Key Concepts Learned

• Brand asset management
• Category hierarchy
• Download tracking
• Tag-based search
• Usage analytics