Corporate Communications Archive

Objective

Create a comprehensive archive for corporate communications including executive messages, company announcements, town halls, and internal memos with full-text search and multimedia support.

Step 1: Create Communication Types Table

Define communication categories.

CREATE TABLE communication_types (
    id INTEGER PRIMARY KEY,
    type_code VARCHAR(50) NOT NULL UNIQUE,
    type_name VARCHAR(100),
    description TEXT,
    requires_approval BOOLEAN DEFAULT TRUE,
    retention_years INTEGER DEFAULT 7,
    is_active BOOLEAN DEFAULT TRUE
);

Step 2: Create Communications Table

Store communication records.

CREATE TABLE corporate_communications (
    id INTEGER PRIMARY KEY,
    comm_id VARCHAR(50) NOT NULL UNIQUE,
    type_id INTEGER NOT NULL,
    title VARCHAR(300) NOT NULL,
    summary TEXT,
    full_content TEXT,
    author_id VARCHAR(50),
    author_name VARCHAR(200),
    author_title VARCHAR(100),
    department VARCHAR(100),
    target_audience TEXT,
    priority VARCHAR(20) DEFAULT 'normal',
    status VARCHAR(50) DEFAULT 'draft',
    published_at TIMESTAMP,
    expires_at TIMESTAMP,
    view_count INTEGER DEFAULT 0,
    is_pinned BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    FOREIGN KEY (type_id) REFERENCES communication_types(id)
);

Step 3: Create Communication Attachments Table

Store attached media.

CREATE TABLE comm_attachments (
    id INTEGER PRIMARY KEY,
    comm_id INTEGER NOT NULL,
    attachment_type VARCHAR(50),
    file_name VARCHAR(255),
    document_file PDF,
    video_file VIDEO(MP4),
    audio_file AUDIO(MP3),
    image_file IMAGE(JPEG),
    file_size_mb DECIMAL(10, 2),
    duration_seconds INTEGER,
    page_count INTEGER,
    upload_order INTEGER,
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (comm_id) REFERENCES corporate_communications(id)
);

Step 4: Create Town Halls Table

Track company meetings.

CREATE TABLE town_hall_meetings (
    id INTEGER PRIMARY KEY,
    meeting_id VARCHAR(50) NOT NULL UNIQUE,
    title VARCHAR(300),
    description TEXT,
    meeting_date DATE,
    start_time TIME,
    duration_minutes INTEGER,
    location VARCHAR(200),
    is_virtual BOOLEAN DEFAULT TRUE,
    recording VIDEO(MP4),
    presentation_slides PDF,
    transcript TEXT,
    attendee_count INTEGER,
    host_name VARCHAR(200),
    status VARCHAR(50) DEFAULT 'scheduled',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 5: Create Q&A Sessions Table

Store Q&A from town halls.

CREATE TABLE town_hall_qa (
    id INTEGER PRIMARY KEY,
    meeting_id INTEGER NOT NULL,
    question_text TEXT,
    question_submitted_by VARCHAR(200),
    question_department VARCHAR(100),
    answer_text TEXT,
    answered_by VARCHAR(200),
    timestamp_seconds INTEGER,
    is_answered BOOLEAN DEFAULT FALSE,
    is_featured BOOLEAN DEFAULT FALSE,
    upvote_count INTEGER DEFAULT 0,
    FOREIGN KEY (meeting_id) REFERENCES town_hall_meetings(id)
);

Step 6: Create Communication Read Receipts Table

Track readership.

CREATE TABLE comm_read_receipts (
    id INTEGER PRIMARY KEY,
    comm_id INTEGER NOT NULL,
    user_id VARCHAR(50),
    user_name VARCHAR(200),
    user_department VARCHAR(100),
    read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    time_spent_seconds INTEGER,
    FOREIGN KEY (comm_id) REFERENCES corporate_communications(id)
);

Step 7: Insert Communication Types

Add categories.

INSERT INTO communication_types (id, type_code, type_name, description, requires_approval, retention_years) VALUES
    (1, 'CEO-MSG', 'CEO Message', 'Direct communications from the CEO', TRUE, 10),
    (2, 'EXEC-MEMO', 'Executive Memo', 'Communications from executive leadership', TRUE, 7),
    (3, 'HR-ANN', 'HR Announcement', 'Human resources announcements', TRUE, 5),
    (4, 'POLICY', 'Policy Update', 'Company policy changes', TRUE, 10),
    (5, 'NEWS', 'Company News', 'General company news and updates', TRUE, 5),
    (6, 'PRODUCT', 'Product Update', 'Product launch and update announcements', TRUE, 5),
    (7, 'AWARD', 'Recognition', 'Employee recognition and awards', FALSE, 3),
    (8, 'EVENT', 'Event Announcement', 'Company events and activities', FALSE, 2);

Step 8: Insert Sample Communications

Add archived communications.

INSERT INTO corporate_communications (id, comm_id, type_id, title, summary, full_content, author_id, author_name, author_title, department, target_audience, priority, status, published_at, view_count, is_pinned) VALUES
    (1, 'COMM-2024-001', 1, 'New Year Message from CEO', 'Looking back at 2023 and ahead to 2024', 'Dear Team, As we begin 2024, I want to reflect on our incredible journey...', 'CEO-001', 'John Smith', 'Chief Executive Officer', 'Executive', 'All Employees', 'high', 'published', '2024-01-02 09:00:00', 2450, TRUE),
    (2, 'COMM-2024-002', 3, 'Updated Benefits Package for 2024', 'Summary of changes to employee benefits', 'We are pleased to announce enhancements to our benefits program...', 'HR-001', 'Sarah Johnson', 'VP of Human Resources', 'Human Resources', 'All Employees', 'high', 'published', '2024-01-05 10:00:00', 2180, TRUE),
    (3, 'COMM-2024-003', 4, 'Remote Work Policy Update', 'Clarifications to our hybrid work policy', 'Following employee feedback, we are updating our remote work guidelines...', 'HR-002', 'Mike Williams', 'HR Director', 'Human Resources', 'All Employees', 'normal', 'published', '2024-01-10 14:00:00', 1890, FALSE),
    (4, 'COMM-2024-004', 6, 'Introducing Product X 2.0', 'Major product update announcement', 'We are excited to announce the launch of Product X 2.0...', 'PROD-001', 'Emily Chen', 'VP of Product', 'Product', 'All Employees', 'normal', 'published', '2024-01-15 11:00:00', 1250, FALSE),
    (5, 'COMM-2024-005', 5, 'Q4 2023 Results Summary', 'Financial performance highlights', 'Q4 2023 was our strongest quarter yet...', 'CFO-001', 'Robert Brown', 'Chief Financial Officer', 'Finance', 'All Employees', 'normal', 'published', '2024-01-20 09:00:00', 1680, FALSE),
    (6, 'COMM-2024-006', 7, 'Employee of the Month - January', 'Recognizing outstanding performance', 'Please join us in congratulating...', 'HR-001', 'Sarah Johnson', 'VP of Human Resources', 'Human Resources', 'All Employees', 'normal', 'published', '2024-01-25 10:00:00', 980, FALSE);

Step 9: Insert Attachments

Add media files.

INSERT INTO comm_attachments (id, comm_id, attachment_type, file_name, file_size_mb, upload_order) VALUES
    (1, 1, 'document', 'ceo_new_year_message.pdf', 0.8, 1),
    (2, 2, 'document', 'benefits_summary_2024.pdf', 2.5, 1),
    (3, 2, 'document', 'benefits_comparison_chart.pdf', 0.5, 2),
    (4, 3, 'document', 'remote_work_policy_v2.pdf', 1.2, 1),
    (5, 4, 'document', 'product_x_2_features.pdf', 3.8, 1),
    (6, 5, 'document', 'q4_2023_summary.pdf', 1.5, 1);

INSERT INTO comm_attachments (id, comm_id, attachment_type, file_name, file_size_mb, duration_seconds, upload_order) VALUES
    (7, 1, 'video', 'ceo_message_video.mp4', 125.4, 480, 2),
    (8, 4, 'video', 'product_x_demo.mp4', 245.8, 720, 2);

Step 10: Insert Town Halls

Add meeting records.

INSERT INTO town_hall_meetings (id, meeting_id, title, description, meeting_date, start_time, duration_minutes, location, is_virtual, attendee_count, host_name, status) VALUES
    (1, 'TH-2024-Q1', 'Q1 2024 All-Hands Meeting', 'Quarterly company update and Q&A', '2024-01-30', '10:00:00', 90, 'Main Auditorium / Zoom', TRUE, 1850, 'John Smith (CEO)', 'completed'),
    (2, 'TH-2024-FEB', 'February Town Hall', 'Monthly update and team recognition', '2024-02-15', '14:00:00', 60, 'Virtual - Zoom', TRUE, 1420, 'Sarah Johnson (VP HR)', 'completed'),
    (3, 'TH-2024-MAR', 'March Strategy Update', 'Strategic initiatives discussion', '2024-03-20', '10:00:00', 75, 'Main Auditorium / Zoom', TRUE, NULL, 'Robert Brown (CFO)', 'scheduled');

INSERT INTO town_hall_qa (id, meeting_id, question_text, question_submitted_by, question_department, answer_text, answered_by, timestamp_seconds, is_answered, is_featured, upvote_count) VALUES
    (1, 1, 'What are our hiring plans for 2024?', 'Anonymous', 'Engineering', 'We plan to add 150 new positions across all departments...', 'John Smith', 2400, TRUE, TRUE, 45),
    (2, 1, 'Will there be salary adjustments this year?', 'Anonymous', 'Sales', 'Yes, we will conduct our annual compensation review in March...', 'Sarah Johnson', 2850, TRUE, TRUE, 128),
    (3, 1, 'What is our strategy for international expansion?', 'Anonymous', 'Marketing', 'We are planning to enter 3 new markets in Europe...', 'John Smith', 3200, TRUE, FALSE, 32),
    (4, 2, 'Can we get more details on the new PTO policy?', 'Anonymous', 'HR', 'The new unlimited PTO policy applies to all full-time employees...', 'Sarah Johnson', 1800, TRUE, TRUE, 85),
    (5, 2, 'Will remote work options continue permanently?', 'Anonymous', 'Engineering', 'Yes, our hybrid model is here to stay...', 'Sarah Johnson', 2100, TRUE, FALSE, 67);

Step 11: Insert Read Receipts

Track engagement.

INSERT INTO comm_read_receipts (id, comm_id, user_id, user_name, user_department, read_at, time_spent_seconds) VALUES
    (1, 1, 'EMP-001', 'John Doe', 'Engineering', '2024-01-02 09:15:00', 180),
    (2, 1, 'EMP-002', 'Jane Smith', 'Sales', '2024-01-02 09:20:00', 240),
    (3, 1, 'EMP-003', 'Mike Johnson', 'Marketing', '2024-01-02 09:30:00', 200),
    (4, 2, 'EMP-001', 'John Doe', 'Engineering', '2024-01-05 10:30:00', 420),
    (5, 2, 'EMP-002', 'Jane Smith', 'Sales', '2024-01-05 11:00:00', 380),
    (6, 3, 'EMP-001', 'John Doe', 'Engineering', '2024-01-10 15:00:00', 300),
    (7, 4, 'EMP-004', 'Sarah Wilson', 'Product', '2024-01-15 12:00:00', 450);

Step 12: Recent Communications Feed

View latest announcements.

SELECT
    cc.comm_id,
    ct.type_name,
    cc.title,
    cc.summary,
    cc.author_name,
    cc.author_title,
    cc.priority,
    cc.published_at,
    cc.view_count,
    cc.is_pinned,
    COUNT(ca.id) as attachment_count
FROM corporate_communications cc
INNER JOIN communication_types ct ON cc.type_id = ct.id
LEFT JOIN comm_attachments ca ON cc.id = ca.comm_id
WHERE cc.status = 'published'
  AND (cc.expires_at IS NULL OR cc.expires_at > CURRENT_TIMESTAMP)
GROUP BY cc.id, cc.comm_id, ct.type_name, cc.title, cc.summary, cc.author_name, cc.author_title, cc.priority, cc.published_at, cc.view_count, cc.is_pinned
ORDER BY cc.is_pinned DESC, cc.published_at DESC;

Step 13: Town Hall Summary

View meeting recordings.

SELECT
    th.meeting_id,
    th.title,
    th.meeting_date,
    th.duration_minutes,
    th.attendee_count,
    th.host_name,
    COUNT(qa.id) as questions_count,
    COUNT(CASE WHEN qa.is_answered THEN 1 END) as answered_count,
    SUM(qa.upvote_count) as total_upvotes
FROM town_hall_meetings th
LEFT JOIN town_hall_qa qa ON th.id = qa.meeting_id
WHERE th.status = 'completed'
GROUP BY th.id, th.meeting_id, th.title, th.meeting_date, th.duration_minutes, th.attendee_count, th.host_name
ORDER BY th.meeting_date DESC;

Step 14: Communication Readership Report

Analyze engagement.

SELECT
    cc.title,
    ct.type_name,
    cc.published_at,
    cc.view_count,
    COUNT(DISTINCT crr.user_id) as unique_readers,
    AVG(crr.time_spent_seconds) as avg_time_seconds,
    COUNT(DISTINCT crr.user_department) as departments_reached
FROM corporate_communications cc
INNER JOIN communication_types ct ON cc.type_id = ct.id
LEFT JOIN comm_read_receipts crr ON cc.id = crr.comm_id
WHERE cc.status = 'published'
GROUP BY cc.id, cc.title, ct.type_name, cc.published_at, cc.view_count
ORDER BY cc.published_at DESC;

Step 15: Search Communications Archive

Find historical content.

SELECT
    cc.comm_id,
    ct.type_name,
    cc.title,
    cc.author_name,
    cc.department,
    cc.published_at,
    cc.view_count
FROM corporate_communications cc
INNER JOIN communication_types ct ON cc.type_id = ct.id
WHERE cc.status = 'published'
  AND (cc.title LIKE '%policy%' OR cc.full_content LIKE '%policy%')
ORDER BY cc.published_at DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS comm_read_receipts;
DROP TABLE IF EXISTS town_hall_qa;
DROP TABLE IF EXISTS town_hall_meetings;
DROP TABLE IF EXISTS comm_attachments;
DROP TABLE IF EXISTS corporate_communications;
DROP TABLE IF EXISTS communication_types;

Expected Outcomes

• Communications categorized by type
• Multimedia attachments supported
• Town halls archived with Q&A
• Readership tracked
• Searchable archive

Key Concepts Learned

• Communication management
• Multi-media attachments
• Town hall archiving
• Engagement tracking
• Content search