Model advertiser intake database schema
Overview
This question evaluates database and system design skills, focusing on data modeling for multi-tenant advertiser and campaign hierarchies, ERD-level schema design with composite keys and parent-child locality, plus operational concerns such as billing, approvals, auditability, versioning, and normalization trade-offs.
Advertiser Intake and Campaign Data Model (F1-style)
Context
You are designing a multi-tenant advertiser intake and campaign configuration system. The system must cleanly model advertisers and their campaigns, enable hierarchical configuration (organization → account → campaign → ad group → creative), and support operational needs such as approvals, auditability, and billing. The schema should be amenable to an F1/Spanner-style hierarchical layout (composite keys, parent-child locality), but also work in a standard RDBMS.
Requirements
-
Model core entities and relationships:
- Organization, account, user/seat (and account membership/roles)
- Campaign, ad group, creative (with assets)
- Budget and flighting (by campaign/ad group as applicable)
- Targeting (geo, device, audience, exclusions, etc.)
- Frequency-cap policy (campaign/ad group scope)
- Billing (billing profile, invoices, invoice lines, spend ledger)
- Approvals (for onboarding and configuration changes)
- Provide ERD-level tables with primary/foreign keys and key fields.
-
Discuss:
- Normalization vs. denormalization (read/write trade-offs)
- Multi-tenancy and row-level security
- Configuration versioning and auditability
- Soft deletes
- Idempotent imports
- How the schema supports validation and partial onboarding (drafts, checklists)
Deliverables
- ERD-level table definitions (names, primary keys, foreign keys, key columns) in a hierarchical, F1-friendly fashion (composite keys) or an equivalent approach for a traditional RDBMS.
- A brief rationale for key design decisions and operational considerations.
Solution
Login required