hust-epi/whepi-doc/login.sql

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
-- drop table if exists sys_user;
-- create table sys_user (
--   user_id             BIGINT          not null             comment '用户编号',
--   weixin_id           VARCHAR(50)     not null default ''  comment '微信ID',
--   token               VARCHAR(50)     not null default ''  comment 'token',
--   version             CHAR(19)        not null             comment 'token版本',
--   create_at TIMESTAMP NOT NULL COMMENT '建立时间',
--   update_at TIMESTAMP NOT NULL COMMENT '更新日期',
--   primary key ( user_id )
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = '微信用户关联';

drop table if exists sys_user_open;
create table sys_user_open
(
  user_id             BIGINT          not null             comment '用户编号',
  open_id             VARCHAR(100)    not null             comment 'wx编号',
  nick_name           VARCHAR(100)    null                 comment '用户昵称',
  sex_desc            VARCHAR(10)     null                 comment '用户性别',
  sex                 int             null                 comment '用户性别',
  city                VARCHAR(50)     null                 comment '用户城市',
  province            VARCHAR(50)     null                 comment '用户省份',
  country             VARCHAR(50)     null                 comment '用户国家',
  head_img_url        VARCHAR(200)    null                 comment '用户头像地址',

  be_active           CHAR(1)         not null default 'Y' comment '是否活动 Y=活动/N=禁用/D=删除',
  create_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '建立时间',
  update_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新日期',
  primary key ( user_id, open_id )
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT = '用户open';

-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS sys_user_role;
CREATE TABLE sys_user_role  (
  ur_id 		bigint(20) NOT NULL COMMENT '用户角色关系ID',
  user_id 		bigint(20) NOT NULL COMMENT '用户ID',
  role_id 		int(11) NOT NULL COMMENT '角色ID:1居民,2业委会,3商家',
  property_id   bigint(20) NOT NULL DEFAULT 0 COMMENT '属性ID,居民门牌ID,业委会ID',
  user_create 	bigint(20) NOT NULL DEFAULT 0 COMMENT '新增人',
  time_create 	datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  user_update 	bigint(20) NOT NULL DEFAULT 0 COMMENT '修改人',
  time_update 	datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (ur_id) USING BTREE
) ENGINE = InnoDB default charset=utf8 comment = '用户角色关系表';

-- ----------------------------
-- Table structure for sys_owner
-- ----------------------------
DROP TABLE IF EXISTS sys_owner;
CREATE TABLE sys_owner  (
  owner_id          bigint(20) NOT NULL DEFAULT 0 COMMENT '业委会ID',
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID,下拉选ID',
  linkman           varchar(10)  NOT NULL DEFAULT '' COMMENT '联系人名称',
  phone             varchar(20)  NOT NULL DEFAULT '' COMMENT '联系人电话',
  user_create       bigint(20) NOT NULL DEFAULT 0 COMMENT '新增人',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  user_update       bigint(20) NOT NULL DEFAULT 0 COMMENT '修改人',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (owner_id) USING BTREE
) ENGINE = InnoDB default charset=utf8 comment = '业委会表';

-- ----------------------------
-- Table structure for sys_owner_linkman
-- ----------------------------
DROP TABLE IF EXISTS sys_owner_linkman;
CREATE TABLE sys_owner_linkman  (
  linkman_id        bigint(20) NOT NULL DEFAULT 0 COMMENT '业委会联系人ID',
  owner_id          bigint(20) NOT NULL DEFAULT 0 COMMENT '业委会ID',
  linkman           varchar(10)  NOT NULL DEFAULT '' COMMENT '联系人名称',
  phone             varchar(20)  NOT NULL DEFAULT '' COMMENT '联系人电话',
  user_create       bigint(20) NOT NULL DEFAULT 0 COMMENT '新增人',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  user_update       bigint(20) NOT NULL DEFAULT 0 COMMENT '修改人',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (owner_id) USING BTREE
) ENGINE = InnoDB default charset=utf8 comment = '业委会联系人表';

-- ----------------------------
-- Table structure for sys_uptown_home
-- ----------------------------
DROP TABLE IF EXISTS sys_uptown_home;
CREATE TABLE sys_uptown_home  (
  house_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '门牌ID',
  linkman           varchar(10)  NOT NULL DEFAULT '' COMMENT '联系人名称',
  phone             varchar(20)  NOT NULL DEFAULT '' COMMENT '联系人电话',
  young             int(11) NOT NULL DEFAULT 0 COMMENT '家庭年前人数',
  middle            int(11) NOT NULL DEFAULT 0 COMMENT '家庭中年人数',
  old               int(11) NOT NULL DEFAULT 0 COMMENT '家庭老年人数',
  remark            varchar(100)  NOT NULL DEFAULT '' COMMENT '备注',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (house_id) USING BTREE
) ENGINE = InnoDB default charset=utf8 comment = '小区家庭表';

-- ----------------------------
-- Table structure for sys_area
-- ----------------------------
DROP TABLE IF EXISTS sys_area;
CREATE TABLE sys_area  (
  area_id           bigint(20) NOT NULL DEFAULT 0 COMMENT '地区ID',
  province          varchar(50)  NOT NULL DEFAULT '' COMMENT '省',
  city              varchar(50)  NOT NULL DEFAULT '' COMMENT '市',
  area              varchar(50)  NOT NULL DEFAULT '' COMMENT '区',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (area_id) USING BTREE
) ENGINE = InnoDB default charset=utf8 comment = '省市区表';

-- ----------------------------
-- Table structure for sys_uptown
-- ----------------------------
DROP TABLE IF EXISTS sys_uptown;
CREATE TABLE sys_uptown  (
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID',
  uptown_name       varchar(20)  NOT NULL DEFAULT '' COMMENT '小区名称',
  uptown_addr       varchar(100)  NOT NULL DEFAULT '' COMMENT '小区地址',
  longitude         varchar(20)  NOT NULL DEFAULT '' COMMENT '经度',
  latitude          varchar(20)  NOT NULL DEFAULT '' COMMENT '纬度',
  area              int(11)  NOT NULL DEFAULT '0' COMMENT '区',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (uptown_id) USING BTREE
) ENGINE = InnoDB default charset=utf8 comment = '小区表';

DROP TABLE IF EXISTS sys_uptown_house;
CREATE TABLE sys_uptown_house  (
  house_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '门牌ID',
  uptown_id         bigint(20) NOT NULL DEFAULT 0 COMMENT '小区ID',
  ridgepole         varchar(20) NOT NULL DEFAULT '' COMMENT '栋',
  unit              varchar(20) NOT NULL DEFAULT '' COMMENT '单元',
  doorplate         varchar(20)  NOT NULL DEFAULT '' COMMENT '门牌',
  status            int(11) NOT NULL DEFAULT 0 COMMENT '状态:1正常,0草稿,-1删除',
  time_create       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '新增时间',
  time_update       datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  PRIMARY KEY (house_id) USING BTREE
) ENGINE = InnoDB default charset=utf8 comment = '门牌信息表';