Meeting Recording Storage

Objective

Create a system for storing meeting recordings with attendee information and metadata. This enables searchable meeting archives, compliance, and knowledge management.

Step 1: Create Meetings Table

Create a table for meeting metadata.

CREATE TABLE meetings (
    meeting_id INTEGER PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    meeting_type VARCHAR(50),
    organizer VARCHAR(100) NOT NULL,
    meeting_room VARCHAR(100),
    scheduled_start TIMESTAMP,
    scheduled_end TIMESTAMP,
    actual_start TIMESTAMP,
    actual_end TIMESTAMP,
    is_recurring BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create Meeting Recordings Table

Create a table for storing recordings.

CREATE TABLE meeting_recordings (
    recording_id INTEGER PRIMARY KEY,
    meeting_id INTEGER NOT NULL,
    recording AUDIO(MP3),
    duration_seconds INTEGER,
    file_size BIGINT,
    recording_quality VARCHAR(20) DEFAULT 'standard',
    has_transcript BOOLEAN DEFAULT FALSE,
    transcript TEXT,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(meeting_id)
);

Step 3: Create Attendees Table

Create a table for meeting attendees.

CREATE TABLE meeting_attendees (
    id INTEGER PRIMARY KEY,
    meeting_id INTEGER NOT NULL,
    attendee_email VARCHAR(255) NOT NULL,
    attendee_name VARCHAR(100),
    role VARCHAR(50) DEFAULT 'participant',
    attended BOOLEAN DEFAULT FALSE,
    join_time TIMESTAMP,
    leave_time TIMESTAMP,
    FOREIGN KEY (meeting_id) REFERENCES meetings(meeting_id)
);

Step 4: Insert Sample Meetings

Add sample meeting records.

INSERT INTO meetings (meeting_id, title, description, meeting_type, organizer, meeting_room, scheduled_start, scheduled_end) VALUES
    (1, 'Q1 Planning Session', 'Quarterly planning and goal setting', 'planning', 'john.manager@company.com', 'Conference Room A', '2024-01-15 09:00:00', '2024-01-15 11:00:00'),
    (2, 'Product Review', 'Review of new product features', 'review', 'sarah.product@company.com', 'Virtual - Zoom', '2024-01-16 14:00:00', '2024-01-16 15:00:00'),
    (3, 'Team Standup', 'Daily team sync', 'standup', 'mike.lead@company.com', 'Virtual - Teams', '2024-01-17 09:30:00', '2024-01-17 09:45:00'),
    (4, 'Client Presentation', 'Demo for potential client', 'presentation', 'lisa.sales@company.com', 'Board Room', '2024-01-18 10:00:00', '2024-01-18 12:00:00'),
    (5, 'Training Session', 'New employee onboarding', 'training', 'hr.team@company.com', 'Training Room', '2024-01-19 13:00:00', '2024-01-19 16:00:00');

Step 5: Insert Meeting Recordings

Add recording records for meetings.

INSERT INTO meeting_recordings (recording_id, meeting_id, duration_seconds, file_size, recording_quality, has_transcript) VALUES
    (1, 1, 7200, 115200000, 'high', TRUE),
    (2, 2, 3600, 57600000, 'standard', TRUE),
    (3, 3, 900, 14400000, 'standard', FALSE),
    (4, 4, 6900, 110400000, 'high', TRUE),
    (5, 5, 10800, 172800000, 'standard', FALSE);

Step 6: Insert Attendees

Add attendee records.

INSERT INTO meeting_attendees (id, meeting_id, attendee_email, attendee_name, role, attended) VALUES
    (1, 1, 'john.manager@company.com', 'John Manager', 'organizer', TRUE),
    (2, 1, 'sarah.product@company.com', 'Sarah Product', 'participant', TRUE),
    (3, 1, 'mike.lead@company.com', 'Mike Lead', 'participant', TRUE),
    (4, 1, 'lisa.sales@company.com', 'Lisa Sales', 'participant', TRUE),
    (5, 2, 'sarah.product@company.com', 'Sarah Product', 'organizer', TRUE),
    (6, 2, 'dev.team@company.com', 'Dev Team', 'participant', TRUE),
    (7, 2, 'qa.team@company.com', 'QA Team', 'participant', TRUE),
    (8, 3, 'mike.lead@company.com', 'Mike Lead', 'organizer', TRUE),
    (9, 3, 'dev.team@company.com', 'Dev Team', 'participant', TRUE),
    (10, 4, 'lisa.sales@company.com', 'Lisa Sales', 'organizer', TRUE),
    (11, 4, 'client@external.com', 'Client Contact', 'guest', TRUE);

Step 7: Query Meetings with Recordings

Get meetings that have recordings.

SELECT
    m.title,
    m.meeting_type,
    m.organizer,
    mr.duration_seconds / 60 as duration_minutes,
    mr.recording_quality,
    mr.has_transcript
FROM meetings m
INNER JOIN meeting_recordings mr ON m.meeting_id = mr.meeting_id
ORDER BY m.scheduled_start DESC;

Step 8: Meeting Attendee List

Get all attendees for a meeting.

SELECT
    m.title,
    ma.attendee_name,
    ma.attendee_email,
    ma.role,
    ma.attended
FROM meetings m
INNER JOIN meeting_attendees ma ON m.meeting_id = ma.meeting_id
WHERE m.meeting_id = 1
ORDER BY ma.role, ma.attendee_name;

Step 9: Organizer Meeting Summary

Get meeting summary for an organizer.

SELECT
    organizer,
    COUNT(*) as meetings_organized,
    SUM(CASE WHEN mr.recording_id IS NOT NULL THEN 1 ELSE 0 END) as recorded_meetings,
    SUM(mr.duration_seconds) / 3600 as total_hours_recorded
FROM meetings m
LEFT JOIN meeting_recordings mr ON m.meeting_id = mr.meeting_id
GROUP BY organizer
ORDER BY meetings_organized DESC;

Step 10: Meetings Without Recordings

Find meetings missing recordings.

SELECT
    m.meeting_id,
    m.title,
    m.meeting_type,
    m.scheduled_start,
    m.organizer
FROM meetings m
LEFT JOIN meeting_recordings mr ON m.meeting_id = mr.meeting_id
WHERE mr.recording_id IS NULL
ORDER BY m.scheduled_start;

Step 11: Recording Storage Analysis

Analyze storage usage.

SELECT
    m.meeting_type,
    COUNT(*) as recording_count,
    SUM(mr.duration_seconds) / 3600 as total_hours,
    SUM(mr.file_size) / 1073741824 as total_gb,
    AVG(mr.file_size) / 1048576 as avg_mb
FROM meeting_recordings mr
INNER JOIN meetings m ON mr.meeting_id = m.meeting_id
GROUP BY m.meeting_type
ORDER BY total_gb DESC;

Step 12: Attendee Participation Report

Track attendee meeting participation.

SELECT
    attendee_email,
    attendee_name,
    COUNT(*) as meetings_invited,
    SUM(CASE WHEN attended THEN 1 ELSE 0 END) as meetings_attended,
    CAST(SUM(CASE WHEN attended THEN 1 ELSE 0 END) AS DECIMAL) / COUNT(*) * 100 as attendance_rate
FROM meeting_attendees
GROUP BY attendee_email, attendee_name
HAVING COUNT(*) > 1
ORDER BY attendance_rate DESC;

Cleanup (Optional)

DROP TABLE IF EXISTS meeting_attendees;
DROP TABLE IF EXISTS meeting_recordings;
DROP TABLE IF EXISTS meetings;

Expected Outcomes

• Meetings stored with metadata
• Recordings linked to meetings
• Attendees tracked per meeting
• Storage analysis available
• Attendance reporting works

Meeting Types

Key Concepts Learned

• Meeting-recording relationships
• Attendee tracking
• Storage usage analysis
• Attendance reporting
• Multi-table queries