lineage/sql/lineage.sql

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2020/9/19 12:42:47                           */
/*==============================================================*/


drop table if exists tb_apply;

drop table if exists tb_comment;

drop table if exists tb_family_album;

drop table if exists tb_family_events;

drop table if exists tb_family_garden;

drop table if exists tb_family_honor;

drop table if exists tb_family_photo;

drop table if exists tb_family_member;

drop table if exists tb_goods;

drop table if exists tb_goods_type;

drop table if exists tb_info;

drop table if exists tb_msg;

drop table if exists tb_orders;

drop table if exists tb_person_life;

drop table if exists tb_self_article;

drop table if exists tb_app_user;

drop table if exists tb_family;

drop table if exists tb_template;

drop table if exists tb_friend;

/*==============================================================*/
/* Table: apply  申请家族                                       */
/*==============================================================*/
create table tb_apply
(
   id             int not null,
   family_id            int(11),
   app_user_id           int,
   primary key (id)
);

/*==============================================================*/
/* Table: comment   评论                                        */
/*==============================================================*/
create table tb_comment
(
   id           int not null,
   article_id              int,
   app_user_id           int,
   comment              varchar(500),
   thumbs               int,
   primary key (id)
);

/*==============================================================*/
/* Table: family_album  家族相簿                          */
/*==============================================================*/
create table tb_family_album
(
   id             int(11) not null comment 'ID',
   family_id            int(11),
   title          varchar(500),
   url            varchar(20),
   primary key (id)
);

/*==============================================================*/
/* Table: family_events   大事记                                */
/*==============================================================*/
create table tb_family_events
(
   id            int(11) not null comment 'ID',
   family_id            int(11),
   title          varchar(500),
   content        varchar(500),
   primary key (id)
);

/*==============================================================*/
/* Table: family_garden 家族园地                                */
/*==============================================================*/
create table tb_family_garden
(
   id            int(11) not null comment 'ID',
   family_id            int(11),
   title         varchar(500),
   content       varchar(500),
   order              int,
   primary key (id)
);

/*==============================================================*/
/* Table: family_honor                                          */
/*==============================================================*/
create table tb_family_honor
(
   id             int(11) not null comment 'ID',
   family_id            int(11),
   title          varchar(500),
   content        varchar(500),
   pic_url              varchar(50),
   primary key (id)
);

/*==============================================================*/
/* Table: family_photo                                          */
/*==============================================================*/
create table tb_family_photo
(
   id             int(11) not null comment 'ID',
   family_id            int(11),
   album_id             int(11),
   title          varchar(500),
   url            varchar(20),
   primary key (id)
);

/*==============================================================*/
/* Table: family_user                                           */
/*==============================================================*/
create table tb_family_member
(
   id              int(11) not null auto_increment comment 'ID',
   family_id            int(11),
   parent_f             varchar(100),
   spouse               varchar(100),
   primary key (id)
);

/*==============================================================*/
/* Table: goods                                                 */
/*==============================================================*/
create table tb_goods
(
   id             int not null,
   typeid               int,
   title                varchar(100),
   price                float,
   goods_date           date,
   picurl               varchar(100),
   sell                 int,
   primary key (id)
);

/*==============================================================*/
/* Table: goods_type                                            */
/*==============================================================*/
create table tb_goods_type
(
   id               int not null,
   name             varchar(20),
   primary key (id)
);

/*==============================================================*/
/* Table: info                                                  */
/*==============================================================*/
create table tb_info
(
   id              int not null,
   title                varchar(100),
   content              varchar(1000),
   info_date            date,
   picurl               varchar(100),
   read               int,
   primary key (id)
);

/*==============================================================*/
/* Table: msg                                                   */
/*==============================================================*/
create table tb_msg
(
   id                int not null,
   life_id              int,
   msg_name              varchar(20),
   msg_content           varchar(1000),
   primary key (id)
);

/*==============================================================*/
/* Table: orders                                                */
/*==============================================================*/
create table tb_orders
(
   id             int not null,
   app_user_id           int,
   goods_id             int,
   money                float,
   status               int,
   order_num             varchar(10),
   primary key (id)
);

/*==============================================================*/
/* Table: person_life个人生平                                   */
/*==============================================================*/
create table tb_person_life
(
   id              int not null,
   app_user_id           int,
   stakeholder          varchar(20),
   stakeholder_date     date,
   life_title           varchar(20),
   life_cotent          varchar(500),
   primary key (id)
);

/*==============================================================*/
/* Table: self_article                                              */
/*==============================================================*/
create table tb_self_article
(
   page_id              int not null,
   app_user_ID           int,
   content              varchar(500),
   picurl1              varchar(50),
   picurl2              varchar(50),
   picurl3              varchar(50),
   picurl4              varchar(50),
   picurl5              varchar(50),
   picurl6              varchar(50),
   picurl7              varchar(50),
   picurl8              varchar(50),
   picurl9              varchar(50),
   page_date            datetime,
   messageUser          varchar(20),
   primary key (id)
);

/*==============================================================*/
/* Table: tb_app_user                                           */
/*==============================================================*/
create table tb_app_user
(
   id           int not null,
   family_id            int(11),
   member_id              int(11),
   template_id          int,
   role					varchar(20),
   nick_name                 varchar(100),
   head                 varchar(100),
   birthday             date,
   reg_date             date,
   qrcode               varchar(100),
   gender                  int,
   mobile                int,
   openid               varchar(100),
   ver_code             varchar(4),
   app_user_id                   varchar(10),
   name                 varchar(20),
   primary key (id)
);

/*==============================================================*/
/* Table: tb_family                                             */
/*==============================================================*/
create table tb_family
(
   id            int(11) not null auto_increment comment 'ID',
   name                 varchar(100) default NULL comment '姓名',
   address              varchar(100) comment '头像',
   content              varchar(11) comment '电话',
   pic_url              varchar(100),
   creator              varchar(100),
   creator_id           int,
   city                 varchar(20),
   familyID             varchar(20),
   primary key (id)
);

/*==============================================================*/
/* Table: template                                              */
/*==============================================================*/
create table tb_template
(
   id          int not null,
   title                varchar(100),
   content              text,
   primary key (id)
);

/*==============================================================*/
/* Table: user_friend                                           */
/*==============================================================*/
create table tb_friends
(
   id                int not null,
   app_user_id           int not null,
   friends_id            int,
   status               int,
   primary key (id)
);

alter table apply add constraint FK_Reference_20 foreign key (family_id)
      references tb_family (family_id) on delete restrict on update restrict;

alter table apply add constraint FK_Reference_21 foreign key (appuser_ID)
      references tb_app_user (appuser_ID) on delete restrict on update restrict;

alter table comment add constraint FK_Reference_15 foreign key (page_id)
      references selfpage (page_id) on delete restrict on update restrict;

alter table comment add constraint FK_Reference_16 foreign key (appuser_ID)
      references tb_app_user (appuser_ID) on delete restrict on update restrict;

alter table family_album add constraint FK_Reference_7 foreign key (family_id)
      references tb_family (family_id) on delete restrict on update restrict;

alter table family_events add constraint FK_Reference_4 foreign key (family_id)
      references tb_family (family_id) on delete restrict on update restrict;

alter table family_garden add constraint FK_Reference_6 foreign key (family_id)
      references tb_family (family_id) on delete restrict on update restrict;

alter table family_honor add constraint FK_Reference_5 foreign key (family_id)
      references tb_family (family_id) on delete restrict on update restrict;

alter table family_photo add constraint FK_Reference_8 foreign key (family_id)
      references tb_family (family_id) on delete restrict on update restrict;

alter table family_photo add constraint FK_Reference_9 foreign key (album_id)
      references family_album (album_id) on delete restrict on update restrict;

alter table family_user add constraint FK_Reference_11 foreign key (family_id)
      references tb_family (family_id) on delete restrict on update restrict;

alter table goods add constraint FK_Reference_24 foreign key (typeid)
      references goods_type (typeid) on delete restrict on update restrict;

alter table msg add constraint FK_Reference_26 foreign key (life_id)
      references person_life (life_id) on delete restrict on update restrict;

alter table orders add constraint FK_Reference_22 foreign key (appuser_ID)
      references tb_app_user (appuser_ID) on delete restrict on update restrict;

alter table orders add constraint FK_Reference_23 foreign key (goods_id)
      references goods (goods_id) on delete restrict on update restrict;

alter table person_life add constraint FK_Reference_18 foreign key (appuser_ID)
      references tb_app_user (appuser_ID) on delete restrict on update restrict;

alter table selfpage add constraint FK_Reference_13 foreign key (appuser_ID)
      references tb_app_user (appuser_ID) on delete restrict on update restrict;

alter table tb_app_user add constraint FK_Reference_10 foreign key (family_id)
      references tb_family (family_id) on delete restrict on update restrict;

alter table tb_app_user add constraint FK_Reference_12 foreign key (user_id)
      references family_user (user_id) on delete restrict on update restrict;

alter table tb_app_user add constraint FK_Reference_25 foreign key (template_id)
      references template (template_id) on delete restrict on update restrict;

alter table user_friend add constraint FK_Reference_14 foreign key (appuser_ID)
      references tb_app_user (appuser_ID) on delete restrict on update restrict;