自研广告系统核心表

本人负责的项目为了实现视频贴片广告功能，需要设计一套广告资源配置及广告投放管理的功能，根据对广告业务的理解抽象出以下几张业务表：
1、广告位管理表
2、广告物料表
3、广告资源包管理表
4、广告投放规则表
5、广告投放策略表
6、广告规则和投放策略关联表
表关联关系如下：

CREATE TABLE `mkt_adv_manage` (
  `adv_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '广告位名称',
  `describe` varchar(1000) NOT NULL COMMENT '描述',
  `adv_rule_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '投放规则',
  `adv_bundle_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '投放广告资源',
  `adv_type` int(2) DEFAULT '0' COMMENT '广告类型 (0 片头广告 1 插播广告)',
  `status` int(2) DEFAULT '0' COMMENT '生效状态 (0 无效 1 有效)',
  `online_time` datetime(6) NOT NULL COMMENT '上架时间',
  `down_time` datetime(6) NOT NULL COMMENT '下架时间',
  `adv_owner` varchar(100) DEFAULT NULL COMMENT '广告主',
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `modify_time` datetime(6) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`adv_id`),
  UNIQUE KEY `rule_type_key` (`adv_rule_id`,`adv_type`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='广告位管理';


CREATE TABLE `mkt_item_manage` (
  `item_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `item_title` varchar(100) NOT NULL COMMENT '标题',
  `describe` varchar(1000) NOT NULL COMMENT '描述',
  `item_type` int(2) DEFAULT '0' COMMENT '资源类型 (0 视频 1 图片)',
  `img_url` varchar(200) NOT NULL COMMENT '图片链接',
  `video_id` varchar(200) DEFAULT NULL COMMENT '视频ID',
  `duration` int(2) DEFAULT '10' COMMENT '时长,单位秒',
  `status` int(2) DEFAULT '0' COMMENT '生效状态 (0 无效 1 有效)',
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `modify_time` datetime(6) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='广告物料管理';


CREATE TABLE `mkt_bundle_manage` (
  `bundle_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '名称',
  `describe` varchar(1000) NOT NULL COMMENT '描述',
  `bundle_type` int(2) DEFAULT '0' COMMENT '资源类型 (0 视频 1 图片)',
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `modify_time` datetime(6) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`bundle_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='广告资源包管理';



CREATE TABLE `mkt_bundle_item` (
  `ref_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `bundle_id` bigint(20) NOT NULL COMMENT 'bundle_id',
  `item_id` bigint(20) NOT NULL COMMENT 'item_id',
  `sort` int(2) DEFAULT '0' COMMENT '排序值',
  `show_time` int(2) DEFAULT '10' COMMENT '时长,单位秒',
  `adv_title` varchar(100) NOT NULL COMMENT '广告标题',
  `adv_des` varchar(1000) NOT NULL COMMENT '广告描述',
  `adv_intro` varchar(1000) NOT NULL COMMENT '广告简介',
  `skip_type` varchar(200) DEFAULT NULL COMMENT '跳转类型',
  `detail_id` varchar(200) DEFAULT NULL COMMENT '跳转目标',
  `jump_url` varchar(200) DEFAULT NULL COMMENT '跳转地址',
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `modify_time` datetime(6) NOT NULL COMMENT '修改时间',
  `target_detail` varchar(500) DEFAULT NULL COMMENT '跳转详情',
  PRIMARY KEY (`ref_id`),
  UNIQUE KEY `bundle_item_key` (`bundle_id`,`item_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='资源包素材关联表';


CREATE TABLE `mkt_adv_strategy` (
   `strategy_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '策略名称',
   `strategy_index` varchar(100) NOT NULL COMMENT '策略标识',
   `default_value` varchar(200) DEFAULT NULL COMMENT '默认值',
   `strategy_type` int(2) DEFAULT '0' COMMENT '策略类型 (0 指定日期 1 指定时间 2 指定星期 3 指定内容分类 4 指定节目 5 指定季数 6 指定集数 7 指定平台 8 指定地域 9指定片源类型：正片、预告片、花絮)',
     `value_type` int(2) DEFAULT '0' COMMENT '值类型 0 字符串 1 数字 2 时间 3 日期',
   `decision_rule` int(2) DEFAULT '0' COMMENT '判定规则(0 等于, 1 不等于,2 大于等于, 3 小于等于)',
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `modify_time` datetime(6) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`strategy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='广告投放策略';


CREATE TABLE `mkt_adv_rule` (
   `rule_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL COMMENT '规则名称',
   `describe` varchar(1000) NOT NULL COMMENT '描述',
   `priority` int(2) DEFAULT '0' COMMENT '优先级，值越大优先级越高',
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `modify_time` datetime(6) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`rule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='广告投放规则';

CREATE TABLE `mkt_rule_strategy` (
  `ref_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rule_id` bigint(20) NOT NULL COMMENT '规则ID',
  `strategy_id` bigint(20) NOT NULL COMMENT '策略ID',
  `strategy_value` varchar(1000) NOT NULL COMMENT '值',
  `data_type` int(2) DEFAULT '0' COMMENT '数据类型 0 单个值,1 多个值(用逗号分隔)',
  `create_time` datetime(6) NOT NULL COMMENT '创建时间',
  `modify_time` datetime(6) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`ref_id`),
  UNIQUE KEY `rule_strategy_key` (`rule_id`,`strategy_id`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COMMENT='规则策略关联表';

注：以下是创建外键的sql，仅为了生成表关联关系使用，线上环境为了减少外键对业务并发能力的影响并未创建外键。

ALTER TABLE `mkt_adv_manage` ADD CONSTRAINT `fk_rule_id` FOREIGN KEY ( `adv_rule_id` )
REFERENCES `mkt_adv_rule` ( `rule_id` )
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `mkt_adv_manage` ADD CONSTRAINT `fk_bundle_id` FOREIGN KEY ( `adv_bundle_id` )
REFERENCES `mkt_bundle_manage` ( `bundle_id` )
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `mkt_bundle_item` ADD CONSTRAINT `fk_bundle_item_ref_bundle` FOREIGN KEY ( `bundle_id` )
REFERENCES `mkt_bundle_manage` ( `bundle_id` )
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `mkt_bundle_item` ADD CONSTRAINT `fk_bundle_item_ref_item` FOREIGN KEY ( `item_id` )
REFERENCES `mkt_item_manage` ( `item_id` )
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `mkt_rule_strategy` ADD CONSTRAINT `fk_rule_strategy_ref_rule` FOREIGN KEY ( `rule_id` )
REFERENCES `mkt_adv_rule` ( `rule_id` )
ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `mkt_rule_strategy` ADD CONSTRAINT `fk_rule_strategy_ref_strategy` FOREIGN KEY ( `strategy_id` )
REFERENCES `mkt_adv_strategy` ( `strategy_id` )
ON DELETE CASCADE ON UPDATE CASCADE;