Objective
Create a digital asset management system for marketing teams to organize, version, approve, and track usage of brand assets across campaigns and channels.
Step 1: Create Asset Categories Table
Organize assets hierarchically.
CREATE TABLE asset_categories (
id INTEGER PRIMARY KEY,
category_code VARCHAR(50) NOT NULL UNIQUE,
category_name VARCHAR(200) NOT NULL,
parent_category_id INTEGER,
description TEXT,
icon IMAGE(PNG),
display_order INTEGER,
is_active BOOLEAN DEFAULT TRUE,
FOREIGN KEY (parent_category_id) REFERENCES asset_categories(id)
);
Step 2: Create Marketing Assets Table
Store asset metadata.
CREATE TABLE marketing_assets (
id INTEGER PRIMARY KEY,
asset_code VARCHAR(50) NOT NULL UNIQUE,
asset_name VARCHAR(200) NOT NULL,
description TEXT,
category_id INTEGER,
asset_type VARCHAR(50),
brand VARCHAR(100),
product_line VARCHAR(100),
target_audience TEXT,
usage_rights TEXT,
license_type VARCHAR(50),
license_expiry DATE,
thumbnail IMAGE(JPEG),
created_by VARCHAR(100),
owner_team VARCHAR(100),
status VARCHAR(50) DEFAULT 'draft',
approval_status VARCHAR(50) DEFAULT 'pending',
is_template BOOLEAN DEFAULT FALSE,
download_count INTEGER DEFAULT 0,
view_count INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES asset_categories(id)
);
Step 3: Create Asset Files Table
Store multiple file versions.
CREATE TABLE asset_files (
id INTEGER PRIMARY KEY,
asset_id INTEGER NOT NULL,
version_number INTEGER DEFAULT 1,
file_name VARCHAR(255),
file_type VARCHAR(50),
image_file IMAGE(JPEG),
video_file VIDEO(MP4),
audio_file AUDIO(MP3),
document_file PDF,
file_size_mb DECIMAL(10, 2),
dimensions VARCHAR(50),
duration_seconds INTEGER,
color_space VARCHAR(20),
resolution_dpi INTEGER,
format_type VARCHAR(50),
is_current BOOLEAN DEFAULT TRUE,
uploaded_by VARCHAR(100),
uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (asset_id) REFERENCES marketing_assets(id)
);
Step 4: Create Asset Tags Table
Enable flexible tagging.
CREATE TABLE asset_tags (
id INTEGER PRIMARY KEY,
tag_name VARCHAR(100) NOT NULL UNIQUE,
tag_type VARCHAR(50),
usage_count INTEGER DEFAULT 0
);
CREATE TABLE asset_tag_mapping (
id INTEGER PRIMARY KEY,
asset_id INTEGER NOT NULL,
tag_id INTEGER NOT NULL,
FOREIGN KEY (asset_id) REFERENCES marketing_assets(id),
FOREIGN KEY (tag_id) REFERENCES asset_tags(id)
);
Step 5: Create Campaigns Table
Organize assets by campaign.
CREATE TABLE marketing_campaigns (
id INTEGER PRIMARY KEY,
campaign_code VARCHAR(50) NOT NULL UNIQUE,
campaign_name VARCHAR(200) NOT NULL,
description TEXT,
campaign_type VARCHAR(50),
brand VARCHAR(100),
start_date DATE,
end_date DATE,
budget DECIMAL(12, 2),
owner_id VARCHAR(50),
owner_name VARCHAR(200),
status VARCHAR(50) DEFAULT 'planning',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Step 6: Create Campaign Assets Table
Link assets to campaigns.
CREATE TABLE campaign_assets (
id INTEGER PRIMARY KEY,
campaign_id INTEGER NOT NULL,
asset_id INTEGER NOT NULL,
usage_type VARCHAR(50),
channel VARCHAR(100),
placement VARCHAR(200),
start_date DATE,
end_date DATE,
added_by VARCHAR(100),
added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (campaign_id) REFERENCES marketing_campaigns(id),
FOREIGN KEY (asset_id) REFERENCES marketing_assets(id)
);
Step 7: Create Approval Workflow Table
Track asset approvals.
CREATE TABLE asset_approvals (
id INTEGER PRIMARY KEY,
asset_id INTEGER NOT NULL,
version_number INTEGER,
submitted_by VARCHAR(100),
submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
reviewer_id VARCHAR(50),
reviewer_name VARCHAR(200),
review_status VARCHAR(50) DEFAULT 'pending',
review_comments TEXT,
reviewed_at TIMESTAMP,
approval_level INTEGER DEFAULT 1,
FOREIGN KEY (asset_id) REFERENCES marketing_assets(id)
);
Step 8: Create Asset Usage Log Table
Track asset downloads and usage.
CREATE TABLE asset_usage_log (
id INTEGER PRIMARY KEY,
asset_id INTEGER NOT NULL,
file_id INTEGER,
user_id VARCHAR(50),
user_name VARCHAR(200),
user_department VARCHAR(100),
action_type VARCHAR(50),
channel VARCHAR(100),
purpose TEXT,
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (asset_id) REFERENCES marketing_assets(id),
FOREIGN KEY (file_id) REFERENCES asset_files(id)
);
Step 9: Insert Sample Categories
Add asset categories.
INSERT INTO asset_categories (id, category_code, category_name, parent_category_id, description, display_order) VALUES
(1, 'PHOTO', 'Photography', NULL, 'Product and lifestyle photography', 1),
(2, 'PHOTO-PROD', 'Product Photos', 1, 'Product photography', 1),
(3, 'PHOTO-LIFE', 'Lifestyle', 1, 'Lifestyle and contextual photos', 2),
(4, 'VIDEO', 'Video', NULL, 'Video content', 2),
(5, 'VIDEO-ADS', 'Advertisements', 4, 'TV and digital ads', 1),
(6, 'VIDEO-SOCIAL', 'Social Media', 4, 'Social media video content', 2),
(7, 'GRAPHICS', 'Graphics', NULL, 'Design assets', 3),
(8, 'GRAPH-LOGO', 'Logos', 7, 'Brand logos and marks', 1),
(9, 'GRAPH-BANNER', 'Banners', 7, 'Web and display banners', 2),
(10, 'DOCS', 'Documents', NULL, 'Marketing collateral', 4),
(11, 'DOCS-BROCH', 'Brochures', 10, 'Product brochures', 1),
(12, 'DOCS-PRES', 'Presentations', 10, 'Sales presentations', 2);
Step 10: Insert Marketing Assets
Add asset content.
INSERT INTO marketing_assets (id, asset_code, asset_name, description, category_id, asset_type, brand, product_line, usage_rights, license_type, owner_team, status, approval_status, download_count, view_count, created_at) VALUES
(1, 'PHO-PROD-001', 'Product Hero Shot - Model X', 'High-resolution product photography', 2, 'image', 'TechBrand', 'Model X', 'Internal use only', 'owned', 'Creative Team', 'published', 'approved', 245, 1250, '2023-06-01 10:00:00'),
(2, 'PHO-PROD-002', 'Product Lineup - Summer Collection', 'Full product lineup shot', 2, 'image', 'TechBrand', 'Summer 2024', 'All channels', 'owned', 'Creative Team', 'published', 'approved', 189, 980, '2023-07-15 09:00:00'),
(3, 'PHO-LIFE-001', 'Lifestyle - Urban Professional', 'Product in urban setting', 3, 'image', 'TechBrand', 'Model X', 'Digital only', 'owned', 'Creative Team', 'published', 'approved', 156, 720, '2023-08-01 11:00:00'),
(4, 'VID-AD-001', 'TV Commercial - 30 Second Spot', 'Main advertising campaign video', 5, 'video', 'TechBrand', 'Model X', 'All channels', 'owned', 'Video Team', 'published', 'approved', 45, 380, '2023-09-01 14:00:00'),
(5, 'VID-SOC-001', 'Instagram Story Template', 'Animated story template', 6, 'video', 'TechBrand', 'All', 'Social media', 'owned', 'Social Team', 'published', 'approved', 312, 1520, '2023-10-01 10:00:00'),
(6, 'GRA-LOGO-001', 'Primary Logo - Full Color', 'Main brand logo', 8, 'image', 'TechBrand', NULL, 'All channels', 'owned', 'Brand Team', 'published', 'approved', 542, 2150, '2023-01-01 00:00:00'),
(7, 'GRA-BAN-001', 'Web Banner - Product Launch', 'Homepage banner for launch', 9, 'image', 'TechBrand', 'Model X', 'Digital only', 'owned', 'Digital Team', 'published', 'approved', 128, 650, '2023-11-01 09:00:00'),
(8, 'DOC-BRO-001', 'Product Brochure - Model X', 'Detailed product information', 11, 'document', 'TechBrand', 'Model X', 'Print and digital', 'owned', 'Product Marketing', 'published', 'approved', 89, 420, '2023-11-15 10:00:00');