Model the Data Schema for an Advertising Demand Platform

Data Modeling · Software Engineering · Frequently Asked

Create the database design for an advertising demand platform. The system represents the complete advertiser structure: Advertiser → Campaign → Ad Group → Ad, while Creative is a reusable resource associated with ads. Treat this strictly as a data-modeling problem: concentrate on entities, relationships, column selections, and schema compromises, rather than infrastructure or distributed architecture.

This exercise evaluates how well you turn product needs into a sound relational design. Interviewers will look for appropriate normalization, sensible data types, lifecycle-state handling, and an extensible approach to targeting. Before proposing implementation details, clarify the responsibility of each entity.

Phase 1: Requirements

Functional Requirements

1. Advertisers must be able to create accounts that include billing information and contact data.
2. Campaigns are owned by advertisers and specify an objective, budget, and active date interval.
3. Ad Groups sit under campaigns and contain targeting definitions, bid approaches, and frequency limits.
4. Ads are contained in ad groups, reference creative assets, and have a rotation weight.
5. Creatives hold metadata for assets such as images and videos, and they pass through a review-and-approval process.
6. Targeting needs to remain extensible: it must cover dimensions such as genre, device, geography, and demographics, along with operations including inclusion, exclusion, and range evaluation.
7. Serving objects (campaigns, ad groups, and ads) require lifecycle statuses such as draft, active, paused, and archived, with allowable transitions defined. Advertisers need an account status, and creatives need a review status.
8. Campaign and ad group updates must be auditable, including the actor, the modification, and the time of change.

Non-Functional Requirements

Scale Estimation

This is an OLTP system. Dashboard activity produces a moderate write rate, but read access is especially important: the ad server retrieves active campaign configurations at high volume, and advertisers filter dashboard views by status and dates.

Phase 2: Data Model — Core Entities

Entity Definitions

Advertiser
├── id: UUID (PK)
├── name: VARCHAR(255)
├── billing_email: VARCHAR(255)
├── billing_address: TEXT
├── payment_method_token: VARCHAR(255)
├── status: VARCHAR(20) -- active, suspended, closed
├── created_at: TIMESTAMP
└── updated_at: TIMESTAMP

Campaign
├── id: UUID (PK)
├── advertiser_id: UUID (FK → Advertiser)
├── name: VARCHAR(255)
├── objective: VARCHAR(50) -- awareness, consideration, conversion
├── total_budget_cents: BIGINT -- money stored as cents, never float
├── daily_budget_cents: BIGINT
├── pacing: VARCHAR(20) -- even, accelerated
├── start_date: DATE
├── end_date: DATE
├── status: VARCHAR(20) -- draft, active, paused, completed, archived
├── created_at: TIMESTAMP
└── updated_at: TIMESTAMP

AdGroup
├── id: UUID (PK)
├── campaign_id: UUID (FK → Campaign)
├── advertiser_id: UUID -- denormalized for query performance
├── name: VARCHAR(255)
├── bid_strategy: VARCHAR(30) -- cpm, cpc, cpv, target_cpa
├── bid_amount_cents: BIGINT
├── frequency_cap_count: INTEGER -- NULL means no cap
├── frequency_cap_window_hours: INTEGER -- NULL means no cap
├── status: VARCHAR(20) -- draft, active, paused, archived
├── created_at: TIMESTAMP
└── updated_at: TIMESTAMP

Creative
├── id: UUID (PK)
├── advertiser_id: UUID (FK → Advertiser)
├── name: VARCHAR(255)
├── asset_type: VARCHAR(20) -- image, video, html5
├── asset_url: TEXT
├── width: INTEGER
├── height: INTEGER
├── duration_seconds: INTEGER -- NULL for images
├── review_status: VARCHAR(20) -- pending, approved, rejected
├── rejection_reason: TEXT
├── created_at: TIMESTAMP
└── updated_at: TIMESTAMP

Ad
├── id: UUID (PK)
├── ad_group_id: UUID (FK → AdGroup)
├── creative_id: UUID (FK → Creative)
├── name: VARCHAR(255)
├── rotation_weight: INTEGER -- relative weight for rotation (default 1)
├── status: VARCHAR(20) -- draft, active, paused, archived
├── created_at: TIMESTAMP
└── updated_at: TIMESTAMP

TargetingRule
├── id: UUID (PK)
├── ad_group_id: UUID (FK → AdGroup)
├── dimension: VARCHAR(50) -- genre, device, geo, age_range, etc.
├── operator: VARCHAR(20) -- include, exclude, in, not_in, gte, lte, between
├── values: JSONB -- operator-specific (e.g., ["US", "CA"], [18, 24] for between, or 18 for gte)
├── created_at: TIMESTAMP
└── updated_at: TIMESTAMP

AuditLog
├── id: UUID (PK)
├── entity_type: VARCHAR(50) -- campaign, ad_group, creative
├── entity_id: UUID
├── user_id: UUID
├── action: VARCHAR(20) -- create, update, delete, status_change
├── changes: JSONB -- {"field": {"old": X, "new": Y}}
├── created_at: TIMESTAMP -- append-only, no updated_at

Relationships Summary

Example: Targeting Configuration

An ad group aimed at "thriller or drama genres, Canada only, excluding tablets, ages 25 through 34" would contain these four TargetingRule records:

Within one dimension, the rules are evaluated with OR semantics (thriller OR drama). Across separate dimensions, they are combined with AND semantics (a genre match AND a geo match AND no tablet match).

Phase 3: Deep Dives

Deep Dive 1: State Machines

Campaigns and creatives each use their own lifecycle states and allowed transitions. Ad groups and ads generally follow the campaign pattern (draft → active → paused → archived).

Campaign State Flow:

• draft → active
• active → paused, completed, or archived
• paused → active or archived
• completed → archived

Creative Review State Flow:

• pending → approved or rejected
• rejected → pending for another review
• approved → pending when the creative requires another review

Cascading behavior:

• Pausing a campaign must stop every child ad group from serving, although those ad groups may continue to have an active status because the ad server evaluates campaign status throughout the hierarchy.
• An ad is eligible to serve only when the campaign is active, the ad group is active, the ad is active, and the creative is approved.
• Decision: apply hierarchy validation in the application layer rather than using database triggers. Triggers introduce implicit dependencies and are more difficult to test.

Deep Dive 2: Enum vs VARCHAR vs Lookup Tables

Suggested choices for this schema:

In real-world ad platforms, VARCHAR plus application-side validation is commonly used for every enum-like attribute. It eliminates migration friction when product requirements shift. In exchange, validation must be implemented carefully in application logic and enforced by API handlers.

Deep Dive 3: Indexing Strategy

Below are the main access patterns and the indexes intended to support them:

1. Advertiser dashboard — "List all of my campaigns ordered by status"

CREATE INDEX idx_campaign_advertiser_status
  ON campaign (advertiser_id, status);

2. Ad server configuration retrieval — "Return every active ad group and its targeting data for serving"

CREATE INDEX idx_adgroup_active_campaign
  ON ad_group (campaign_id)
  WHERE status = 'active';

CREATE INDEX idx_targeting_adgroup
  ON targeting_rule (ad_group_id);

3. Creative review queue — "List every creative awaiting review"

CREATE INDEX idx_creative_review_status
  ON creative (review_status)
  WHERE review_status = 'pending';

4. Audit-history lookup — "Retrieve the modification history for this campaign"

CREATE INDEX idx_audit_entity
  ON audit_log (entity_type, entity_id, created_at DESC);

5. Ad group search using denormalized advertiser_id

CREATE INDEX idx_adgroup_advertiser
  ON ad_group (advertiser_id);

Partial indexes, which use WHERE conditions, matter here because they include only rows satisfying the filter. This keeps them compact, which is essential when most campaigns are archived or completed while reads predominantly target active records.

Deep Dive 4: Normalization Decisions

Why keep advertiser_id denormalized on AdGroup?

Otherwise, finding every ad group for a particular advertiser requires joining through Campaign. With 1M ad groups, that JOIN is costly for a commonly used dashboard request. The trade-off is preserving consistency by changing advertiser_id if a campaign moves to another advertiser, though such reassignment does not occur in practice.

Why separate Creative from Ad?

A single creative may be reused by many ads and ad groups. Embedding creative data in Ad would repeat both asset metadata and the review state. In addition, the review process is independent: a creative is reviewed once, then reused.

To avoid data exposure across advertisers, require ad_group.advertiser_id to equal creative.advertiser_id. This can be checked in application code, or advertiser_id can be added to Ad and enforced with foreign keys.

Why use a separate TargetingRule table rather than JSONB on AdGroup?

Putting the entire targeting definition into one JSONB document on AdGroup may seem appealing, but:

• You cannot search across ad groups by targeting dimension, such as finding every ad group aimed at Canada.
• GIN indexes over deeply nested JSONB are less efficient than B-tree indexes over normalized columns.
• Updating one rule means rewriting the full JSONB document.

The dedicated table using dimension, operator, and values provides both benefits: JSONB keeps value lists flexible, while the dimension and ad_group_id columns retain a queryable, indexable structure.

Why represent money as BIGINT cents?

Floating-point calculations introduce rounding problems ($0.1 + 0.2 \ne 0.3$). Integer cents eliminate those errors. For example, $12.75 is saved as 1275. Calculations remain integer-based, and conversion to dollars occurs only in the presentation layer.

Interview Checklist

Requirements

• Confirmed the serving tree (Advertiser → Campaign → Ad Group → Ad), with Creative treated as a reusable asset.
• Covered functional needs including CRUD, targeting, budgets, and lifecycle status.
• Identified non-functional needs: integrity, read performance, audit history, and extensibility.
• Estimated the expected scale at roughly 10K advertisers and 3M ads.

Data Model

• Specified all seven entities, including their fields and types.
• Described the relationships among the entities.
• Described flexible JSONB targeting through the dimension/operator/values model.
• Added AuditLog as an append-only record of changes.

Deep Dives

• Presented Campaign and Creative state machines with permitted transitions.
• Evaluated enum versus VARCHAR and recommended VARCHAR with application validation.
• Chose indexes for core access patterns: dashboard, ad server, review queue, and audit retrieval.
• Explained the denormalization choices, including advertiser_id on AdGroup, a standalone Creative entity, and integer-cent money storage.

Key Points to Emphasize

1. Hierarchy integrity: Foreign keys must enforce the serving hierarchy (Advertiser → Campaign → AdGroup → Ad), and cascade behavior must reflect business meaning: archive rather than delete. Creatives are reusable resources connected to ads.
2. Flexible targeting: Combining dimension, operator, and JSONB values permits new targeting dimensions without schema revisions, which is vital for a rapidly evolving ads product.
3. State machines in the schema: Status values and their permitted transitions are fundamental elements of the model, not a later addition. Explain whether enforcement belongs in the application or database.
4. Money as integer cents: Floating-point types must not be used for currency. BIGINT cents plus display-layer formatting is the standard industry approach.
5. Denormalize intentionally: Including advertiser_id on AdGroup is a purposeful performance optimization with a stated reason, rather than accidental duplication.
6. Indexes follow queries: Derive indexes from access patterns instead of merely from table design. Partial status indexes keep high-traffic paths efficient.

Starter Code

[object Object]