o1-preview-2024-09-12· OpenAI
WildChat-4.8M (AI2)
Help me design a realistic database for a demand side platform (DSP) in the ad space.…
- User
Help me design a realistic database for a demand side platform (DSP) in the ad space. Consider security and access control. Write sql statements to create the 10-20 essential tables in the database. Add sql comments to explain the purpose of each table and a comment for each column. Specify the table CREATE statements in the correct order, so that foreign-key relationships are respected. Start with tables that have no foreign-key relationships. At the end, add a comment section to explain the schema to a technical reviewer.
Your response should consist of executable sql code and sql comments only, compatible with mysql.
- Assistant
-- Table: roles -- Purpose: Store the different user roles in the system CREATE TABLE roles ( role_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for the roles table role_name VARCHAR(50) NOT NULL UNIQUE -- Name of the role (e.g., 'advertiser', 'admin') ) ENGINE=InnoDB; -- Table: advertisers -- Purpose: Store advertiser company information CREATE TABLE advertisers ( advertiser_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for the advertisers table name VARCHAR(100) NOT NULL, -- Advertiser's company name contact_email VARCHAR(100) NOT NULL, -- Contact email for the advertiser address VARCHAR(255), -- Address of the advertiser phone_number VARCHAR(20) -- Phone number ) ENGINE=InnoDB; -- Table: users -- Purpose: Store user account information CREATE TABLE users ( user_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for the users table username VARCHAR(50) NOT NULL UNIQUE, -- Username for login password_hash VARCHAR(255) NOT NULL, -- Hashed password email VARCHAR(100) NOT NULL UNIQUE, -- User's email address is_active TINYINT(1) NOT NULL DEFAULT 1, -- Indicates if the user's account is active advertiser_id INT, -- Foreign key to advertisers table FOREIGN KEY (advertiser_id) REFERENCES advertisers(advertiser_id) ) ENGINE=InnoDB; -- Table: user_roles -- Purpose: Map users to their roles (many-to-many relationship) CREATE TABLE user_roles ( user_id INT NOT NULL, -- Foreign key to users table role_id INT NOT NULL, -- Foreign key to roles table PRIMARY KEY (user_id, role_id), -- Composite primary key FOREIGN KEY (user_id) REFERENCES users(user_id), -- Foreign key constraint FOREIGN KEY (role_id) REFERENCES roles(role_id) -- Foreign key constraint ) ENGINE=InnoDB; -- Table: ad_exchanges -- Purpose: Store information about ad exchanges CREATE TABLE ad_exchanges ( exchange_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for ad_exchanges table name VARCHAR(100) NOT NULL, -- Name of the ad exchange api_endpoint VARCHAR(255) NOT NULL -- API endpoint URL ) ENGINE=InnoDB; -- Table: campaigns -- Purpose: Store advertising campaign information CREATE TABLE campaigns ( campaign_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for campaigns table advertiser_id INT NOT NULL, -- Foreign key to advertisers table name VARCHAR(100) NOT NULL, -- Campaign name start_date DATE NOT NULL, -- Campaign start date end_date DATE NOT NULL, -- Campaign end date budget DECIMAL(15,2) NOT NULL, -- Total budget for the campaign status ENUM('active', 'paused', 'completed') NOT NULL DEFAULT 'active', -- Campaign status FOREIGN KEY (advertiser_id) REFERENCES advertisers(advertiser_id) ) ENGINE=InnoDB; -- Table: campaign_budgets -- Purpose: Store the budget allocations for campaigns CREATE TABLE campaign_budgets ( campaign_id INT PRIMARY KEY, -- Primary key and foreign key to campaigns daily_budget DECIMAL(15,2), -- Daily budget for the campaign total_budget DECIMAL(15,2) NOT NULL, -- Total budget for the campaign amount_spent DECIMAL(15,2) NOT NULL DEFAULT 0.00, -- Amount spent so far FOREIGN KEY (campaign_id) REFERENCES campaigns(campaign_id) ) ENGINE=InnoDB; -- Table: creatives -- Purpose: Store ad creatives (ad content) CREATE TABLE creatives ( creative_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for creatives table campaign_id INT NOT NULL, -- Foreign key to campaigns table type ENUM('banner', 'video', 'native') NOT NULL, -- Type of ad creative content VARCHAR(255) NOT NULL, -- URL or identifier for the ad content width INT, -- Width of the ad in pixels height INT, -- Height of the ad in pixels status ENUM('active', 'paused', 'pending') NOT NULL DEFAULT 'pending', -- Creative status FOREIGN KEY (campaign_id) REFERENCES campaigns(campaign_id) ) ENGINE=InnoDB; -- Table: advertiser_accounts -- Purpose: Store the account balance for each advertiser CREATE TABLE advertiser_accounts ( advertiser_id INT PRIMARY KEY, -- Primary key and foreign key to advertisers balance DECIMAL(15,2) NOT NULL DEFAULT 0.00, -- Current account balance FOREIGN KEY (advertiser_id) REFERENCES advertisers(advertiser_id) ) ENGINE=InnoDB; -- Table: inventories -- Purpose: Store information about ad inventories received from exchanges CREATE TABLE inventories ( inventory_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for inventories table exchange_id INT NOT NULL, -- Foreign key to ad_exchanges table auction_id VARCHAR(255) NOT NULL, -- Auction identifier from the exchange ad_format VARCHAR(50) NOT NULL, -- Ad format (e.g., 'banner', 'video') width INT NOT NULL, -- Width of the ad space height INT NOT NULL, -- Height of the ad space bid_floor DECIMAL(10,4) NOT NULL, -- Minimum bid amount in USD timestamp DATETIME NOT NULL, -- Time when the inventory was received FOREIGN KEY (exchange_id) REFERENCES ad_exchanges(exchange_id) ) ENGINE=InnoDB; -- Table: bids -- Purpose: Store bid information for ad auctions CREATE TABLE bids ( bid_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for bids table creative_id INT NOT NULL, -- Foreign key to creatives table inventory_id INT NOT NULL, -- Foreign key to inventories table bid_amount DECIMAL(10,4) NOT NULL, -- Bid amount in USD bid_time DATETIME NOT NULL, -- Time when the bid was made FOREIGN KEY (creative_id) REFERENCES creatives(creative_id), FOREIGN KEY (inventory_id) REFERENCES inventories(inventory_id) ) ENGINE=InnoDB; -- Table: impressions -- Purpose: Store information about ad impressions CREATE TABLE impressions ( impression_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for impressions table bid_id INT NOT NULL, -- Foreign key to bids table impression_time DATETIME NOT NULL, -- Time when the ad was displayed FOREIGN KEY (bid_id) REFERENCES bids(bid_id) ) ENGINE=InnoDB; -- Table: clicks -- Purpose: Store information about ad clicks CREATE TABLE clicks ( click_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for clicks table impression_id INT NOT NULL, -- Foreign key to impressions table click_time DATETIME NOT NULL, -- Time when the ad was clicked FOREIGN KEY (impression_id) REFERENCES impressions(impression_id) ) ENGINE=InnoDB; -- Table: conversions -- Purpose: Store information about conversions (desired user actions) CREATE TABLE conversions ( conversion_id INT AUTO_INCREMENT PRIMARY KEY, -- Primary key for conversions table impression_id INT, -- Foreign key to impressions table (nullable) click_id INT, -- Foreign key to clicks table (nullable) conversion_time DATETIME NOT NULL, -- Time when the conversion occurred value DECIMAL(10,2), -- Monetary value of the conversion FOREIGN KEY (impression_id) REFERENCES impressions(impression_id), FOREIGN KEY (click_id) REFERENCES clicks(click_id) ) ENGINE=InnoDB; /* Schema Explanation: The database schema is designed for a Demand Side Platform (DSP) in the ad space. It includes tables to manage users, advertisers, campaigns, creatives, bidding, and ad delivery data. - The `roles`, `users`, and `user_roles` tables manage user authentication and access control. - The `advertisers` table stores information about advertiser companies. - The `users` table links to `advertisers` via `advertiser_id` to associate users with advertisers. - The `campaigns` table stores advertising campaigns, linked to `advertisers`. - The `campaign_budgets` table manages budget allocations for campaigns. - The `creatives` table stores ad creative information, associated with campaigns. - The `ad_exchanges` table stores information about ad exchanges the DSP interacts with. - The `inventories` table stores ad inventory data received from exchanges. - The `bids` table records bids made on ad inventory, linked to `creatives` and `inventories`. - The `impressions` table records ad impressions resulting from successful bids. - The `clicks` table records user clicks on ads. - The `conversions` table records conversions (desired user actions), linked to impressions or clicks. - The `advertiser_accounts` table tracks the account balance for each advertiser. Foreign key constraints are used to maintain referential integrity between tables.