StreamFlix schema

All tables live in the streamflix schema, so write streamflix.users in your queries.

streamflix.users

One row per registered account.

• user_id (INT) — PK
• email (VARCHAR(120))
• full_name (VARCHAR(80))
• country (VARCHAR(40))
• signup_date (DATE)
• device_type (VARCHAR(20)) — device used at signup: 'mobile', 'tv', 'web', 'tablet'
• status (VARCHAR(20)) — 'ACTIVE' (has an active subscription), 'CANCELLED' (had one, none now), 'REGISTERED' (never subscribed)

streamflix.plans

The plans on sale and their current monthly price (USD).

• plan_type (VARCHAR(20)) — PK; 'Basic', 'Standard', 'Premium'
• monthly_price (DECIMAL(6,2))
• max_screens (INT)
• video_quality (VARCHAR(10))

streamflix.subscriptions

One row per subscription period. Changing plan ends one row and starts a new one.

• subscription_id (INT) — PK
• user_id (INT) — FK → users.user_id
• plan_type (VARCHAR(20)) — FK → plans.plan_type
• start_date (DATE)
• end_date (DATE) — nullable; NULL while the subscription is running
• status (VARCHAR(20)) — 'active', 'cancelled', 'switched_plan'
• auto_renew (BOOLEAN)
• monthly_price (DECIMAL(6,2)) — price the user pays on this subscription

streamflix.payments

Monthly billing attempts (USD).

• payment_id (INT) — PK
• user_id (INT) — FK → users.user_id
• subscription_id (INT) — FK → subscriptions.subscription_id
• amount (DECIMAL(6,2))
• payment_date (DATE)
• status (VARCHAR(10)) — 'SUCCESS', 'FAILED', 'REFUNDED'

streamflix.content

The catalogue: one row per movie or series.

• content_id (INT) — PK
• title (VARCHAR(120))
• content_type (VARCHAR(10)) — 'movie' or 'series'
• genre (VARCHAR(30))
• release_year (INT)
• maturity_rating (VARCHAR(5)) — 'U', '13+', '16+', '18+'
• duration_minutes (INT) — nullable; runtime for movies; NULL for series (see episodes)
• is_original (BOOLEAN) — produced by StreamFlix
• avg_rating (DECIMAL(2,1)) — rating shown in the app, 1 to 5
• added_at (DATE) — date it became available

streamflix.episodes

Episodes of series.

• episode_id (INT) — PK
• content_id (INT) — FK → content.content_id
• season_number (INT)
• episode_number (INT)
• title (VARCHAR(140))
• duration_minutes (INT)

streamflix.watch_history

One row per viewing session.

• watch_id (INT) — PK
• user_id (INT) — FK → users.user_id
• content_id (INT) — FK → content.content_id
• episode_id (INT) — FK → episodes.episode_id; nullable; set for series; NULL for movies
• watched_at (TIMESTAMP) — session start
• watch_duration_minutes (INT)
• device_type (VARCHAR(20))
• completed (BOOLEAN) — watched at least 90% of the movie or episode

streamflix.ratings

Star ratings users give titles.

• rating_id (INT) — PK
• user_id (INT) — FK → users.user_id
• content_id (INT) — FK → content.content_id
• rating (INT) — 1 to 5
• rated_at (DATE)

streamflix.recommendations

Titles the recommendation engine showed to a user.

• recommendation_id (INT) — PK
• user_id (INT) — FK → users.user_id
• content_id (INT) — FK → content.content_id
• shown_at (TIMESTAMP)
• placement (VARCHAR(30)) — 'home_row', 'because_you_watched', 'trending'