CampaignDB_Init_SQL-platform_server-v0.0.2.txt

/* DB Name : campaigndb - platform server v0.0.2 */

CREATE database `campaigndb` DEFAULT CHARACTER SET utf8;

use campaigndb;

CREATE TABLE `app_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) NOT NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `campaign_info` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(500) NOT NULL COMMENT 'ex) 6일 광고 다시 보지 않기',
  `camp_desc` varchar(500) DEFAULT NULL,
  `url` varchar(500) NOT NULL,
  `template` int(3) NOT NULL,
  `ad_expire_day` int(3) NOT NULL,
  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


CREATE TABLE `device_for_app` (
  `device_id` int(11) NOT NULL AUTO_INCREMENT,
  `app_id` int(11) NOT NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`device_id`),
  KEY `appID_to_device` (`app_id`),
  CONSTRAINT `appID_to_device` FOREIGN KEY (`app_id`) REFERENCES `app_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `location_for_app` (
  `seq` int(11) NOT NULL AUTO_INCREMENT,
  `location_id` varchar(100) NOT NULL,
  `app_id` int(11) NOT NULL,
  `loc_desc` varchar(500) DEFAULT NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`seq`),
  KEY `appID_to_location` (`app_id`),
  KEY `idx_loc_id` (`location_id`),
  UNIQUE KEY (`location_id`, `app_id`),
  CONSTRAINT `appID_to_location` FOREIGN KEY (`app_id`) REFERENCES `app_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

CREATE TABLE `campaign_for_location` (
  `location_seq` int(11) NOT NULL,
  `campaign_id` int(11) NOT NULL,
  `campaign_order` int(3) NOT NULL,
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modifiedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`location_seq`,`campaign_id`),
  KEY `campaignID_to_campaign_for_app` (`campaign_id`),
  CONSTRAINT `campaignID_to_campaign_for_app` FOREIGN KEY (`campaign_id`) REFERENCES `campaign_info` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `locationID_to_campaign_for_app` FOREIGN KEY (`location_seq`) REFERENCES `location_for_app` (`seq`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `campaign_click` (
  `device_id` int(11) NOT NULL,
  `campaign_id` int(11) NOT NULL,
  `type` enum('exposure', 'click', 'purchase') NOT NULL,
  `number` int(11) NOT NULL, 
   PRIMARY KEY (`device_id`, `campaign_id`, `type`),
  FOREIGN KEY (`device_id`) REFERENCES `device_for_app` (`device_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (`campaign_id`) REFERENCES `campaign_for_location` (`campaign_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;