Skip to content

DATABASE_DOCUMENTATION.md

Latest commit

 

History

History
102 lines (87 loc) · 3.63 KB

DATABASE_DOCUMENTATION.md

File metadata and controls

102 lines (87 loc) · 3.63 KB
Raw

StreamFlix Database Documentation

Overview

The database is a relational PostgreSQL system designed to support the StreamFlix streaming platform. It adheres to 3rd Normal Form (3NF) to ensure data integrity and minimize redundancy.

  • Database Name: streamflix
  • Schema: Public
  • User: admin (Default)

Entity Relationship Diagram (ERD)

Note: This description maps to the provided SQL schema.

Core Entities

  1. Accounts: Stores user login credentials and account status.
  2. Profile: Multiple profiles per account (like Netflix).
  3. Media: Base entity for content (Inheritance strategy).
    • Movie: Extends Media.
    • Series: Extends Media.
  4. Subscription: Manages user billing tiers.

Table Descriptions

User Management

  • accounts:
    • id (PK), email (Unique), password_hash, is_verified, is_blocked.
  • profile:
    • id (PK), account_id (FK), name, age_rating_id (FK).
  • role:
    • Lookup table for employee roles (Junior, Mid-level, Senior).
  • employee:
    • Internal staff linked to accounts and role.

Content Management

  • media:
    • id (PK), title, release_date, age_rating_id (FK), dtype (Discriminator).
  • movie:
    • media_id (PK/FK), duration_seconds, video_url.
  • series:
    • media_id (PK/FK), description.
  • season:
    • id (PK), series_id (FK), season_number.
  • episode:
    • id (PK), season_id (FK), title, video_url.

User Activity

  • viewing_progress:
    • Tracks where a user left off. Links profile to movie OR episode.
  • watch_list:
    • Content saved for later. Links profile to media.
  • preference:
    • User preferences (genres, tags).

Billing & Referrals

  • subscription_tier:
    • Defines plans (SD, HD, UHD) and prices.
  • subscription:
    • Active user subscriptions.
  • referral:
    • Tracks user invitations and discounts.

Views

Views are used to enforce row-level security and role-based data access for employees.

  1. view_junior_accounts:
    • Restricted view for Junior support staff. Shows only basic account info (no PII/financials).
  2. view_midlevel_profiles:
    • For Mid-level staff. Adds profile visibility.
  3. view_senior_full_access:
    • Full access view for Senior staff, including subscription and financial data.

Stored Procedures & Functions

  1. get_profile_viewing_stats(profile_id):
    • Returns aggregated stats: total hours watched, movies count, episodes count.
  2. get_popular_content(limit):
    • Returns top content based on view count and completion rate.
  3. clean_expired_tokens():
    • Maintenance procedure to delete old verification tokens.

Triggers

  1. trg_auto_remove_from_watchlist:
    • Automatically removes a movie or series from the user's watchlist when they finish watching it.
  2. trg_update_subscription_end_date:
    • Sets the end date to "today" when a subscription is cancelled.
  3. trg_prevent_duplicate_watchlist:
    • Prevents adding the same content to a watchlist twice.

Backup & Recovery

  • Backup: Standard pg_dump utility should be used. 
    docker exec -t streamflix-db pg_dump -U admin streamflix > backup.sql
  • Recovery: Restore using psql. 
    cat backup.sql | docker exec -i streamflix-db psql -U admin -d streamflix

Test Data

The database initializes with:

  • 7 Age Ratings (AL to 18+)
  • 3 Subscription Tiers (SD, HD, UHD)
  • 6 Movies (e.g., Inception, The Matrix)
  • 1 Series (Breaking Bad) with 2 Seasons and 3 Episodes.