Promotion Posting System Data Model

Design the relational schema for an internal tool that posts trailers/clips to many social platforms across regions and languages: campaigns, posts, creatives, localized content, multi-level budgets (cents/BIGINT), an approval workflow, and an audit log. Design the Data Model for a Promotion Posting System Design a database schema for Netflix's internal tool used to post ads. Netflix employees use this tool to post trailers, clips, and announcements on social media apps like Facebook, YouTube, TikTok, Instagram, and X (Twitter). The database must handle: Posting to many platforms (like YouTube vs. TikTok). Different regions and languages (English in the US, Japanese in Japan). Money tracking (budgets and costs). Approvals (managers must say "yes" before a post goes live). Scheduling (picking a future time to post). This allows the interviewer to see how you design tables, relationships, and handle money. Do not worry about servers or hardware. Focus on the data. Phase 1: What We Need to Build System Features Users — Netflix employees who create ads. Some are writers, some are managers who approve ads. Campaigns — A big goal (like "Promote Stranger Things"). It has a total budget and start/end dates. Posts — A single message sent to one app (like one Tweet or one Instagram photo). Media Files — The actual images or videos. Each app has different rules (like video length or file size). Translations — Text for the post (headlines, hashtags) in different languages. Money Tracking — Track how much money is planned vs. how much is spent. We need to track this by Campaign, App, or Region. Approval Process — A post starts as a "Draft." A manager makes it "Approved." Then it becomes "Scheduled" and finally "Published." History Log — A record of who changed what and when. This helps us fix mistakes. Quality Requirements Requirement Target Why we need it Valid Data No broken links Everything must connect correctly (e.g., a post must belong to a campaign). Speed Fast loading (< 200ms) Marketers open dashboards often; the system must not feel slow. History Keep all records We need to prove who spent money or changed ads. Easy to Upgrade Easy to add apps New apps (like Threads) appear often; adding them shouldn't break the database. Size Estimation Metric Value Employees ~5,000 Campaigns per year ~20,000 Posts ~200,000 Media Files ~300,000 Translations ~500,000 Regions ~30 Platforms ~5-7 Note: We don't have a huge number of writes per second. However, reading data happens fast because the "Scheduler" (a computer program) constantly checks for posts that need to go live. Phase 2: Database Tables Table Definitions Employee ├── id: UUID (PK) ├── email: VARCHAR(255) ├── name: VARCHAR(255) ├── department: VARCHAR(100) ├── role: VARCHAR(30) -- marketer, approver, admin ├── created_at: TIMESTAMP └── updated_at: TIMESTAMP Promotion ├── id: UUID (PK) ├── created_by: UUID (FK → Employee) ├── title: VARCHAR(255) ├── content_title: VARCHAR(255) -- Name of the show/movie ├── content_type: VARCHAR(30) -- series, film, documentary ├── objective: VARCHAR(50) -- marketing goal ├── total_budget_cents: BIGINT -- money in cents (no decimals) ├── currency: VARCHAR(3) -- USD, EUR, JPY ├── start_date: DATE ├── end_date: DATE ├── status: VARCHAR(20) -- draft, active, paused, archived ├── created_at: TIMESTAMP └── updated_at: TIMESTAMP Platform ├── id: UUID (PK) ├── name: VARCHAR(50) -- facebook, youtube, tiktok ├── display_name: VARCHAR(100) -- "Facebook", "YouTube" ├── max_text_length: INTEGER ├── supported_asset_types: JSONB -- ["image", "video", "carousel"] ├── aspect_ratios: JSONB -- ["16:9", "9:16", "1:1"] ├── max_video_duration_sec: INTEGER ├── created_at: TIMESTAMP └── updated_at: TIMESTAMP Region ├── id: UUID (PK) ├── code: VARCHAR(10) -- us, uk, jp, br ├── name: VARCHAR(100) -- United States, United Kingdom ├── default_locale: VARCHAR(10) -- en-US, en-GB, ja-JP ├── timezone: VARCHAR(50) -- America/New_York ├── created_at: TIMESTAMP └── updated_at: TIMESTAMP Post ├── id: UUID (PK) ├── promotion_id: UUID (FK → Promotion) ├── platform_id: UUID (FK → Platform) ├── region_id: UUID (FK → Region) ├── created_by: UUID (FK → Employee) ├── approved_by: UUID (FK → Employee, nullable) ├── status: VARCHAR(20) -- draft, approved, scheduled, published ├── scheduled_at: TIMESTAMP -- empty until scheduled ├── published_at: TIMESTAMP -- empty until published ├── external_post_id: VARCHAR(255) -- ID from the social app (e.g., Tweet ID) ├── rejection_reason: TEXT -- empty unless rejected ├── created_at: TIMESTAMP └── updated_at: TIMESTAMP Creative ├── id: UUID (PK) ├── promotion_id: UUID (FK → Promotion) ├── asset_type: VARCHAR(20) -- image, video, carousel ├── asset_url: TEXT ├── thumbnail_url: TEXT ├── width: INTEGER ├── height: INTEGER ├── duration_seconds: INTEGER -- empty for images ├── file_size_bytes: BIGINT ├── created_at: TIMESTAMP └── updated_at: TIMESTAMP PostCreative ├── id: UUID (PK) ├── post_id: UUID (FK → Post) ├── creative_id: UUID (FK → Creative) ├── display_order: INTEGER -- order of images in a post ├── created_at: TIMESTAMP LocalizedContent ├── id: UUID (PK) ├── post_id: UUID (FK → Post) ├── locale: VARCHAR(10) -- en-US, ja-JP ├── headline: VARCHAR(500) ├── body_text: TEXT ├── call_to_action: VARCHAR(100) -- "Watch Now", "Learn More" ├── hashtags: TEXT ├── created_at: TIMESTAMP └── updated_at: TIMESTAMP BudgetAllocation ├── id: UUID (PK) ├── promotion_id: UUID (FK → Promotion) ├── platform_id: UUID (FK → Platform, nullable) -- empty = general budget ├── region_id: UUID (FK → Region, nullable) -- empty = general budget ├── allocated_cents: BIGINT ├── spent_cents: BIGINT -- actual money spent ├── currency: VARCHAR(3) -- USD, EUR, JPY ├── created_at: TIMESTAMP └── updated_at: TIMESTAMP AuditLog ├── id: UUID (PK) ├── entity_type: VARCHAR(50) -- promotion, post, budget ├── entity_id: UUID ├── employee_id: UUID (FK → Employee) ├── action: VARCHAR(20) -- create, update, delete ├── changes: JSONB -- {"field": {"old": X, "new": Y}} ├── created_at: TIMESTAMP -- never changes Table Connections Parent Child Connection Type Delete Rules Employee Promotion 1 to Many No. If an employee leaves, keep their campaigns. Employee Post 1 to Many No. Posts belong to the campaign, not just the user. Promotion Post 1 to Many No. Archive the campaign instead of deleting. Promotion BudgetAllocation 1 to Many Yes. If a campaign is deleted, delete its budget data. Platform Post 1 to Many No. If a platform is removed, archive the posts. Post PostCreative 1 to Many Yes. These links only exist for the post. Post LocalizedContent 1 to Many Yes. Translations belong to the post. Example Scenario: "Stranger Things Season 5" A campaign targets 3 apps (YouTube, Instagram, TikTok) in 2 regions (US, Japan). This creates 6 posts: Post App Region Languages Media Post 1 YouTube US English Wide video trailer Post 2 YouTube Japan Japanese, English Wide video trailer (JP subtitles) Post 3 Instagram US English Square image + Vertical video Post 4 Instagram Japan Japanese Square image (JP text) Post 5 TikTok US English Vertical short video Post 6 TikTok Japan Japanese Vertical short video (JP subtitles) Phase 3: Detailed Explanations Topic 1: Approval Process Ads cannot just go live immediately. They must follow a strict path. The Post Path: Draft: Being written. Pending Approval: Waiting for a manager. Approved: Manager said yes. Scheduled: A computer is waiting for the right time to post it. Published: It is live on social media. (Optional) Rejected: Manager said no. Goes back to Draft. Important Rules: A post cannot be scheduled unless the Campaign is active AND the post is Approved. If a Campaign is paused, the Scheduler must stop all its posts. We check these rules in the Application Code, not inside the database. This makes the system easier to test. Topic 2: Handling Many Languages (Localization) We need to store text for different languages. There are three ways to do this: Separate Table (Best Choice): Create a LocalizedContent table. Each row is one language for one post. This handles unlimited languages easily. JSON Column: Store all languages in one cell on the Post table. This is hard to search and update. Extra Columns: Add columns like title_en, title_jp. This is bad because we have to change the database structure every time we add a new language. Conclusion: Use the Separate Table. We add a rule (Constraint) to make sure we don't accidentally add two English entries for the same post: ALTER TABLE localized_content ADD CONSTRAINT uq_post_locale UNIQUE (post_id, locale); Topic 3: Money and Budget We need to track budget at different levels (Global, per App, or per Region). Instead of making 4 different tables, we use one table with empty (NULL) columns. Global Campaign Budget: platform_id is NULL, region_id is NULL. Budget for YouTube: platform_id is set, region_id is NULL. Budget for Japan: platform_id is NULL, region_id is set. Budget for YouTube in Japan: Both are set. How we store money: We use BIGINT (large integers). We store cents, not dollars. Example: $150.00 is stored as 15000. This prevents math errors that happen with decimals (floating point math). We keep allocated_cents (planned) separate from spent_cents (actual). Topic 4: Speeding Up Queries (Indexing) We add "indexes" to make common searches fast. Dashboard: Marketers often look for their own active campaigns. Index on: (created_by, status) The Scheduler: The computer constantly looks for posts ready to publish. Index on: (scheduled_at) but ONLY include posts where status = 'scheduled'. This keeps the index tiny and very fast. App Filtering: Finding all Facebook posts. Index on: (platform_id, status) Audit History: Seeing the history of a specific item. Index on: (entity_type, entity_id, created_at) Review Checklist Requirements Understood the goal: Employees post ads for Netflix shows. Listed what we need: Users, Campaigns, Posts, Files, Languages, Money, Approvals. Listed quality needs: Data is valid, system is fast, history is kept. Estimated size: ~5K users, ~200K posts, ~30 regions. Data Model Defined all 10 tables clearly. Explained how tables connect (Relationships). Explained the PostCreative table (allows one post to have many files). Used a separate table for Languages. Used one Budget table with NULLs for flexibility. Included an Audit Log to track changes. Detailed Topics Explained the Approval steps (Draft -> Approved -> Published). Explained why we chose a separate table for languages. Explained storing money as cents (Integers). Listed the specific indexes needed for speed. Important Takeaways Flexible Platforms: We store platform rules (like "max video length") in a JSONB column. If we add a new app like Threads, we just add a row. We don't change the table structure. Smart Translations: Using a separate LocalizedContent table lets us add new languages easily without breaking the database. Simple Budgeting: Using one table with NULL foreign keys is cleaner than creating four different tables for different budget types. Money Math: Always use BIGINT and store minor units (cents). Never use floats for money. Strict Approvals: A post cannot be scheduled until it is "Approved." This prevents mistakes. Fast Scheduling: The index for the scheduler is "partial" (WHERE status = 'scheduled'). This makes it extremely fast because it ignores old or draft posts.