Brand Asset Guidelines

Objective

Create a brand asset management system for storing and distributing brand guidelines, logos, colors, typography, and visual identity elements with usage rules and approval workflows.

Step 1: Create Brands Table

Define brand identities.

CREATE TABLE brands (
    id INTEGER PRIMARY KEY,
    brand_code VARCHAR(50) NOT NULL UNIQUE,
    brand_name VARCHAR(200) NOT NULL,
    tagline VARCHAR(300),
    description TEXT,
    primary_logo IMAGE(PNG),
    brand_story TEXT,
    target_audience TEXT,
    brand_voice TEXT,
    status VARCHAR(50) DEFAULT 'active',
    owner_team VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Brand Colors Table

Define color palettes.

CREATE TABLE brand_colors (
    id INTEGER PRIMARY KEY,
    brand_id INTEGER NOT NULL,
    color_name VARCHAR(100),
    color_category VARCHAR(50),
    hex_code VARCHAR(7),
    rgb_values VARCHAR(20),
    cmyk_values VARCHAR(20),
    pantone_code VARCHAR(20),
    usage_description TEXT,
    display_order INTEGER,
    FOREIGN KEY (brand_id) REFERENCES brands(id)
);

Step 3: Create Brand Logos Table

Store logo variations.

CREATE TABLE brand_logos (
    id INTEGER PRIMARY KEY,
    brand_id INTEGER NOT NULL,
    logo_name VARCHAR(200),
    logo_type VARCHAR(50),
    color_variant VARCHAR(50),
    background_type VARCHAR(50),
    primary_file IMAGE(PNG),
    vector_file TEXT,
    eps_file TEXT,
    minimum_size VARCHAR(50),
    clear_space TEXT,
    usage_guidelines TEXT,
    dont_do_examples TEXT,
    is_primary BOOLEAN DEFAULT FALSE,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES brands(id)
);

Step 4: Create Typography Table

Define font guidelines.

CREATE TABLE brand_typography (
    id INTEGER PRIMARY KEY,
    brand_id INTEGER NOT NULL,
    font_name VARCHAR(100),
    font_category VARCHAR(50),
    font_family VARCHAR(100),
    font_weights TEXT,
    usage_type VARCHAR(50),
    sample_image IMAGE(PNG),
    web_font_url VARCHAR(500),
    license_type VARCHAR(50),
    usage_guidelines TEXT,
    display_order INTEGER,
    FOREIGN KEY (brand_id) REFERENCES brands(id)
);

Step 5: Create Brand Guidelines Documents Table

Store guideline PDFs.

CREATE TABLE brand_guidelines (
    id INTEGER PRIMARY KEY,
    brand_id INTEGER NOT NULL,
    guideline_name VARCHAR(200),
    guideline_type VARCHAR(50),
    version VARCHAR(20),
    document_file PDF,
    thumbnail IMAGE(JPEG),
    page_count INTEGER,
    file_size_mb DECIMAL(10, 2),
    summary TEXT,
    effective_date DATE,
    is_current BOOLEAN DEFAULT TRUE,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES brands(id)
);

Step 6: Create Brand Templates Table

Store design templates.

CREATE TABLE brand_templates (
    id INTEGER PRIMARY KEY,
    brand_id INTEGER NOT NULL,
    template_name VARCHAR(200),
    template_category VARCHAR(50),
    template_type VARCHAR(50),
    preview_image IMAGE(JPEG),
    template_file PDF,
    dimensions VARCHAR(50),
    file_format VARCHAR(20),
    usage_instructions TEXT,
    download_count INTEGER DEFAULT 0,
    status VARCHAR(50) DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES brands(id)
);

Step 7: Create Asset Usage Requests Table

Track asset requests.

CREATE TABLE brand_usage_requests (
    id INTEGER PRIMARY KEY,
    brand_id INTEGER,
    asset_type VARCHAR(50),
    asset_id INTEGER,
    requester_id VARCHAR(50),
    requester_name VARCHAR(200),
    requester_department VARCHAR(100),
    usage_purpose TEXT,
    usage_channel VARCHAR(100),
    usage_duration VARCHAR(100),
    request_status VARCHAR(50) DEFAULT 'pending',
    reviewer_id VARCHAR(50),
    reviewer_notes TEXT,
    reviewed_at TIMESTAMP,
    requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (brand_id) REFERENCES brands(id)
);

Step 8: Insert Sample Brands

Add brand identities.

INSERT INTO brands (id, brand_code, brand_name, tagline, description, target_audience, brand_voice, status, owner_team) VALUES
    (1, 'MAIN-BRAND', 'TechCorp', 'Innovation for Tomorrow', 'Primary corporate brand identity', 'Enterprise customers, technology professionals', 'Professional, innovative, trustworthy', 'active', 'Brand Team'),
    (2, 'PRODUCT-X', 'Product X', 'Simplify Your World', 'Consumer product brand', 'Tech-savvy consumers, millennials', 'Friendly, approachable, modern', 'active', 'Product Marketing'),
    (3, 'ENTERPRISE', 'TechCorp Enterprise', 'Enterprise Solutions', 'Enterprise division brand', 'Large enterprises, IT decision makers', 'Authoritative, reliable, comprehensive', 'active', 'Enterprise Marketing');

Step 9: Insert Brand Colors

Add color palettes.

INSERT INTO brand_colors (id, brand_id, color_name, color_category, hex_code, rgb_values, cmyk_values, pantone_code, usage_description, display_order) VALUES
    (1, 1, 'Primary Blue', 'primary', '#0066CC', '0, 102, 204', '100, 50, 0, 20', 'PMS 285 C', 'Primary brand color for logos and headers', 1),
    (2, 1, 'Secondary Navy', 'primary', '#003366', '0, 51, 102', '100, 50, 0, 60', 'PMS 289 C', 'Secondary color for text and accents', 2),
    (3, 1, 'Accent Orange', 'accent', '#FF6600', '255, 102, 0', '0, 60, 100, 0', 'PMS 151 C', 'Accent color for CTAs and highlights', 3),
    (4, 1, 'Light Gray', 'neutral', '#F5F5F5', '245, 245, 245', '0, 0, 0, 4', NULL, 'Background color for light sections', 4),
    (5, 1, 'Dark Gray', 'neutral', '#333333', '51, 51, 51', '0, 0, 0, 80', NULL, 'Primary text color', 5),
    (6, 2, 'Product Green', 'primary', '#00CC66', '0, 204, 102', '100, 0, 50, 20', 'PMS 354 C', 'Primary color for Product X', 1),
    (7, 2, 'Product Purple', 'accent', '#6600CC', '102, 0, 204', '50, 100, 0, 20', 'PMS 2685 C', 'Accent color for Product X', 2);

Step 10: Insert Logos and Typography

Add visual assets.

INSERT INTO brand_logos (id, brand_id, logo_name, logo_type, color_variant, background_type, minimum_size, clear_space, usage_guidelines, is_primary) VALUES
    (1, 1, 'Primary Logo - Full Color', 'primary', 'full_color', 'light', '120px width', '1x logo height on all sides', 'Use on light backgrounds only', TRUE),
    (2, 1, 'Primary Logo - White', 'primary', 'white', 'dark', '120px width', '1x logo height on all sides', 'Use on dark or colored backgrounds', FALSE),
    (3, 1, 'Primary Logo - Black', 'primary', 'black', 'light', '120px width', '1x logo height on all sides', 'Use when color printing is not available', FALSE),
    (4, 1, 'Icon Only', 'icon', 'full_color', 'any', '32px', '0.5x icon size', 'Use for favicons and small applications', FALSE),
    (5, 1, 'Horizontal Lockup', 'lockup', 'full_color', 'light', '200px width', '1x height', 'Use when vertical space is limited', FALSE),
    (6, 2, 'Product X Logo', 'primary', 'full_color', 'light', '100px width', '1x logo height', 'Primary Product X logo', TRUE);

INSERT INTO brand_typography (id, brand_id, font_name, font_category, font_family, font_weights, usage_type, license_type, usage_guidelines, display_order) VALUES
    (1, 1, 'Montserrat', 'primary', 'Montserrat, sans-serif', 'Regular 400, Medium 500, Bold 700', 'headings', 'Google Fonts (Open)', 'Use for all headings and display text', 1),
    (2, 1, 'Open Sans', 'secondary', 'Open Sans, sans-serif', 'Regular 400, Semi-Bold 600', 'body', 'Google Fonts (Open)', 'Use for body text and UI elements', 2),
    (3, 1, 'Source Code Pro', 'accent', 'Source Code Pro, monospace', 'Regular 400, Medium 500', 'code', 'Google Fonts (Open)', 'Use for code snippets and technical content', 3),
    (4, 2, 'Poppins', 'primary', 'Poppins, sans-serif', 'Light 300, Regular 400, Bold 700', 'all', 'Google Fonts (Open)', 'Primary font for Product X brand', 1);

Step 11: Insert Guidelines and Templates

Add documents and templates.

INSERT INTO brand_guidelines (id, brand_id, guideline_name, guideline_type, version, page_count, file_size_mb, summary, effective_date, is_current, created_by) VALUES
    (1, 1, 'TechCorp Brand Guidelines', 'comprehensive', '3.0', 85, 24.5, 'Complete brand guidelines covering logo, color, typography, and applications', '2024-01-01', TRUE, 'Brand Team'),
    (2, 1, 'Logo Usage Quick Reference', 'quick_reference', '2.0', 8, 2.1, 'Quick reference for logo usage dos and donts', '2024-01-01', TRUE, 'Brand Team'),
    (3, 1, 'Digital Brand Guidelines', 'digital', '2.0', 35, 12.8, 'Guidelines specific to digital applications', '2024-01-01', TRUE, 'Digital Team'),
    (4, 2, 'Product X Brand Book', 'comprehensive', '1.0', 45, 15.2, 'Complete brand guidelines for Product X', '2024-01-01', TRUE, 'Product Marketing'),
    (5, 1, 'Social Media Guidelines', 'channel_specific', '2.0', 20, 8.5, 'Brand application for social media channels', '2024-01-01', TRUE, 'Social Team');

INSERT INTO brand_templates (id, brand_id, template_name, template_category, template_type, dimensions, file_format, usage_instructions, download_count, status) VALUES
    (1, 1, 'PowerPoint Presentation', 'presentation', 'pptx', '16:9', 'PowerPoint', 'Use for all internal and external presentations', 1245, 'active'),
    (2, 1, 'Email Signature', 'communication', 'html', 'variable', 'HTML', 'Standard email signature for all employees', 2890, 'active'),
    (3, 1, 'Business Card', 'stationery', 'indd', '3.5x2 inches', 'InDesign', 'Standard business card template', 456, 'active'),
    (4, 1, 'Letterhead', 'stationery', 'docx', '8.5x11 inches', 'Word', 'Official letterhead template', 678, 'active'),
    (5, 1, 'Social Media Post', 'digital', 'psd', '1080x1080', 'Photoshop', 'Instagram and Facebook post template', 892, 'active'),
    (6, 1, 'LinkedIn Banner', 'digital', 'psd', '1584x396', 'Photoshop', 'LinkedIn company page banner', 234, 'active');

Step 12: Insert Usage Requests

Add request tracking.

INSERT INTO brand_usage_requests (id, brand_id, asset_type, asset_id, requester_id, requester_name, requester_department, usage_purpose, usage_channel, request_status, requested_at) VALUES
    (1, 1, 'logo', 1, 'USR-001', 'John Marketing', 'Marketing', 'Trade show booth graphics', 'Print', 'approved', '2024-01-10 09:00:00'),
    (2, 1, 'template', 1, 'USR-002', 'Jane Sales', 'Sales', 'Client proposal presentation', 'Digital', 'approved', '2024-01-11 14:00:00'),
    (3, 2, 'logo', 6, 'USR-003', 'Mike Product', 'Product', 'Product packaging design', 'Print', 'pending', '2024-01-15 10:00:00'),
    (4, 1, 'guidelines', 1, 'USR-004', 'Sarah Agency', 'External Agency', 'Marketing campaign development', 'Mixed', 'approved', '2024-01-12 11:00:00');

Step 13: Brand Asset Overview

View all brand assets.

SELECT
    b.brand_name,
    COUNT(DISTINCT bl.id) as logo_count,
    COUNT(DISTINCT bc.id) as color_count,
    COUNT(DISTINCT bt.id) as font_count,
    COUNT(DISTINCT bg.id) as guideline_docs,
    COUNT(DISTINCT btemp.id) as templates
FROM brands b
LEFT JOIN brand_logos bl ON b.id = bl.brand_id AND bl.status = 'active'
LEFT JOIN brand_colors bc ON b.id = bc.brand_id
LEFT JOIN brand_typography bt ON b.id = bt.brand_id
LEFT JOIN brand_guidelines bg ON b.id = bg.brand_id AND bg.is_current = TRUE
LEFT JOIN brand_templates btemp ON b.id = btemp.brand_id AND btemp.status = 'active'
WHERE b.status = 'active'
GROUP BY b.id, b.brand_name
ORDER BY b.brand_name;

Step 14: Color Palette Reference

View brand colors.

SELECT
    b.brand_name,
    bc.color_name,
    bc.color_category,
    bc.hex_code,
    bc.rgb_values,
    bc.pantone_code,
    bc.usage_description
FROM brand_colors bc
INNER JOIN brands b ON bc.brand_id = b.id
WHERE b.id = 1
ORDER BY bc.display_order;

Step 15: Asset Usage Analytics

Track asset downloads.

SELECT
    bt.template_name,
    bt.template_category,
    bt.download_count,
    COUNT(bur.id) as usage_requests,
    COUNT(CASE WHEN bur.request_status = 'approved' THEN 1 END) as approved_requests
FROM brand_templates bt
LEFT JOIN brand_usage_requests bur ON bt.brand_id = bur.brand_id
    AND bur.asset_type = 'template'
    AND bur.asset_id = bt.id
WHERE bt.status = 'active'
GROUP BY bt.id, bt.template_name, bt.template_category, bt.download_count
ORDER BY bt.download_count DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS brand_usage_requests;
DROP TABLE IF EXISTS brand_templates;
DROP TABLE IF EXISTS brand_guidelines;
DROP TABLE IF EXISTS brand_typography;
DROP TABLE IF EXISTS brand_logos;
DROP TABLE IF EXISTS brand_colors;
DROP TABLE IF EXISTS brands;

Expected Outcomes

• Brand assets organized
• Colors and logos cataloged
• Guidelines documented
• Templates available
• Usage tracked

Key Concepts Learned

• Brand asset management
• Color palette definition
• Logo versioning
• Typography guidelines
• Template distribution