Step 3: Relationships Explained (Netflix)

1. Video Inheritance (Supertype/Subtype Pattern)

To handle both stand-alone Movies and episodic TV Series cleanly, we implement the Table-per-Subclass inheritance design:

• Supertype: The general videos table stores attributes shared by all video content (e.g. title, duration, maturity_rating).
• Subtype 1: The movies table contains details unique to standalone feature films. Its primary key movie_id is a 1-to-1 foreign key back to videos.video_id.
• Subtype 2: The episodes table contains details unique to episodic TV content. Its primary key episode_id is a 1-to-1 foreign key back to videos.video_id.

This pattern maintains strict referential integrity (e.g. watch history references video_id, and both movies and episodes resolve back to it seamlessly).

2. TV Show Catalog Hierarchy

The episodic TV series structure is modeled as a nested hierarchical chain:

• A show represents a master title (e.g., Stranger Things).
• A show can contain multiple seasons (one-to-many: shows.show_id ➡️ seasons.show_id).
• Each season contains multiple episodes (one-to-many: seasons.season_id ➡️ episodes.season_id).

Through this cascading chain, the general video properties (like duration) of a single episode link directly to its master TV series record.

3. Stream Routing & Playback State

• CDN Caching Map: Video files are distributed to edge storage appliances near users. The junction table video_caches links a specific video_id to a cdn_node_id, matching the available file qualities (SD, HD, 4K).
• Playback Tracking updates: When a user streams a video, their playback cursor is written continuously to watch_history. By tracking the active screen count across all user profiles under the same account_id, the system enforces screen caps before writing new active history records.

This content is reserved for Premium Members.