1,电视台表结构,包括推送的三张表
- create database tv;
- use tv;
- create table t_user(
- id int(10) auto_increment primary key,
- username varchar(50),
- password varchar(50),
- nickname varchar(20),
- create_id int(10) unsigned,
- create_time bigint(10) unsigned,
- update_time bigint(10) unsigned,
- status int(2) unsigned default 1,
- email varchar(50),
- potrait varchar(50),
- level int(2) unsigned default 0,
- reserved varchar(250)
- );
- create table t_admin(
- id int(10) unsigned auto_increment primary key,
- username varchar(50),
- password varchar(50),
- nickname varchar(20),
- create_id int(10) unsigned,
- create_time bigint(10) unsigned,
- status int(2) unsigned,
- email varchar(50),
- potrait varchar(50),
- lastlogintime bigint(10) unsigned,
- loginip varchar(30),
- type smallint(1) unsigned,
- reserved varchar(250)
- );
- create table t_admin_operation_log(
- id int(10) unsigned auto_increment primary key,
- username varchar(50),
- scriptname varchar(50),
- url varchar(200),
- optime int(10) unsigned
- );
- create table t_user_log(
- id int(10) unsigned auto_increment primary key,
- userId int(10) unsigned,
- login_time int(10) unsigned,
- login_result int(10) unsigned,
- cause varchar(250),
- user_agent varchar(250),
- ip varchar(250),
- reserved varchar(250)
- );
- create table t_news(
- id int(10) unsigned auto_increment primary key,
- type int(5) unsigned,
- title varchar(250),
- pic varchar(250),
- content longtext,
- keyword varchar(250),
- releaseId int(10) unsigned,
- userId int(10) unsigned,
- release_time bigint(10) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- sticktop int(2) unsigned,
- status int(2) unsigned,
- reserved varchar(250)
- );
- create table t_comment(
- id int(10) unsigned auto_increment primary key,
- from_userId int(10) unsigned,
- from_username varchar(50),
- release_time bigint(10) unsigned,
- comments longtext,
- target_type int(2) unsigned,
- targetId int(10) unsigned,
- status int(2) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- reserved varchar(250)
- );
- create table t_business_information(
- id int(10) unsigned auto_increment primary key,
- type int(5) unsigned,
- title varchar(250),
- pic varchar(250),
- link varchar(250),
- from_websit varchar(250),
- status int(2) unsigned,
- releaseId int(10) unsigned,
- release_time bigint(10) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- reserved varchar(250)
- );
- create table t_video(
- id int(10) unsigned auto_increment primary key,
- column_type int(5) unsigned default 0,
- title varchar(250),
- url varchar(250),
- path varchar(250),
- videoformat varchar(250),
- type int(2) unsigned,
- status int(2) unsigned,
- releaseId int(10) unsigned,
- release_time bigint(10) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- reserved varchar(250)
- );
- create table t_notice(
- id int(10) unsigned auto_increment primary key,
- type int(5) unsigned,
- title varchar(250),
- content longtext,
- status int(2) unsigned,
- releaseId int(10) unsigned,
- release_time bigint(10) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- reserved varchar(250)
- );
- create table t_bbs(
- id int(10) unsigned auto_increment primary key,
- type int(5) unsigned,
- title varchar(250),
- cardcontent longtext,
- click int(10) unsigned,
- status int(2) unsigned,
- releaseId int(10) unsigned,
- release_name varchar(50),
- release_time bigint(10) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- reserved varchar(250)
- );
- create table t_user_sendcard(
- id int(10) unsigned auto_increment primary key,
- title varchar(250),
- followcardcontent text ,
- cardid int(10) unsigned,
- card_type int(2) unsigned,
- click int(10) unsigned,
- status int(2) unsigned,
- releaseId int(10) unsigned,
- from_username varchar(50),
- release_time bigint(10) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- reserved varchar(250)
- );
- create table t_sendauditing(
- id int(10) unsigned auto_increment primary key,
- sendcardid int(10) unsigned,
- auditingstatus int(2) unsigned,
- compositor int(2) unsigned
- );
- create table t_tip_off(
- id int(10) unsigned auto_increment primary key,
- title varchar(250),
- pic varchar(250),
- content varchar(250),
- status int(2) unsigned,
- releaseId int(10) unsigned,
- release_name varchar(50),
- release_time bigint(10) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- reserved varchar(250)
- );
- create table t_dictionary(
- id int(10) unsigned auto_increment primary key,
- groupId varchar(250),
- key2 varchar(250),
- value varchar(250),
- description varchar(250)
- );
- create table t_investigation(
- id int(10) unsigned auto_increment primary key,
- exam_name varchar(250),
- create_time bigint(10) unsigned,
- exam_time int(10) unsigned,
- score int(10) unsigned,
- userId int(10) unsigned,
- releaseId int(10) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- reserved varchar(250)
- );
- create table t_questioncontent(
- id int(10) unsigned auto_increment primary key,
- tittle varchar(255),
- pic varchar(255),
- option_length int(5) unsigned,
- option_a varchar(255),
- option_b varchar(255),
- option_c varchar(255),
- option_d varchar(255),
- option_e varchar(255),
- right_answer varchar(255),
- right_multiAnser varchar(255),
- reserved varchar(250)
- );
- create table t_question(
- id int(10) unsigned auto_increment primary key,
- description varchar(255),
- question_type int(5) unsigned,
- single_score int(5) unsigned,
- questionContentId int(10) unsigned
- );
- create table t_questionresult(
- id int(10) unsigned auto_increment primary key,
- result_answer varchar(255),
- resultMultiAnser varchar(255),
- resultState int(1) unsigned,
- questionId int(10) unsigned,
- investigationId int(10) unsigned
- );
- create table t_user_feedback(
- id int(10) unsigned auto_increment primary key,
- type int(2) unsigned,
- content varchar(250),
- userId int(10) unsigned,
- release_time bigint(10) unsigned,
- status int(2) unsigned,
- deleteId int(10) unsigned,
- delete_time bigint(10) unsigned,
- reserved varchar(250)
- );
- /* 设备注册表*/
- create table t_push_device(
- `id` int(10) unsigned NOT NULL auto_increment COMMENT 'ID',
- userId int(10) unsigned unique null COMMENT '用户数据库ID',
- username varchar(50),
- device_id varchar(50) unique not null COMMENT '设备唯一标识',
- device_register_time varchar(50) COMMENT '设备注册时间',
- os_type varchar(10) COMMENT '设备操作系统类型android or ios',
- os_version varchar(10) COMMENT '设备操作系统版本,例如4.3.1,8.3',
- reserved varchar(250) COMMENT '预留信息,暂时没有用',
- device_info varchar(255) comment '移动设备的其他信息,比如uuid,mac地址,手机型号等',
- PRIMARY KEY (`id`)
- );
- /* 推送消息表 */
- create table t_push_message(
- `id` int(10) unsigned NOT NULL auto_increment COMMENT 'ID',
- to_userId int(10) unsigned,
- to_username varchar(50) null COMMENT '用户邮箱,满足唯一性',
- push_title varchar(250) COMMENT '推送消息标题',
- push_content varchar(250) COMMENT '推送消息内容',
- push_time varchar(50) COMMENT '推送日期,格式:yyyy-MM-dd HH:mm:ss',
- bulk_or_point varchar(10) COMMENT '是否是定点推送,取值bulk,point',
- reserved varchar(250) COMMENT '预留信息,暂时没有用',
- fromuid int(10) unsigned,
- PRIMARY KEY (`id`)
- );
- -- 用户访问日志
- create table t_access_log(
- id int(10) unsigned auto_increment primary key,
- os_type varchar(10) comment '设备类型android or ios',
- device_id varchar(50) comment '设备标示(device token or clientid)',
- access_day varchar(50),
- requestURI varchar(50),
- userId int(10) unsigned comment '登录用户的数据库ID',
- username varchar(50) comment '登录用户',
- time bigint(10) unsigned,
- ip varchar(50) comment '内网ip',
- extranet_ip varchar(50) comment '外网ip',
- description varchar(50),
- access_type smallint(1) unsigned,
- reserved varchar(250)
- );
- --压缩图片失败的日志
- create table t_compress_failed_pic (
- id integer not null auto_increment, cause varchar(255),
- failed_time datetime comment '失败的时间',
- original_size bigint comment '图片原始大小',
- pic_path varchar(255),
- primary key (id)
- )
- -- 简单的消息,没有发布者
- create table t_simple_info (
- id integer not null auto_increment,
- create_time bigint, info longtext,
- status integer not null comment '1:可用;2:不可用',
- primary key (id)
- )
- alter table t_push_device add index FK_4e4qn4vp4du6e5okbn0dq3wdf (userId), add constraint FK_4e4qn4vp4du6e5okbn0dq3wdf foreign key (userId) references t_user (id)
- --接收到的推送消息
- create table t_received_push_message (
- id integer not null auto_increment,
- fromuid integer,
- push_content varchar(255),
- push_title varchar(255),
- received_time varchar(255),
- reserved varchar(255),
- to_userId integer,
- primary key (id)
- )
- alter table t_push_message add index FK_ek2r577b6r0i1r49qsh724wnl (to_userId), add constraint FK_ek2r577b6r0i1r49qsh724wnl foreign key (to_userId) references t_user (id)
- alter table t_received_push_message add index FK_1id8pc8ubfv8dg4k1sq8o4fxr (to_userId), add constraint FK_1id8pc8ubfv8dg4k1sq8o4fxr foreign key (to_userId) references t_user (id)