## 數據字典
```[sql]
-- last update:2017-9-21 21:00:26
-- 說明:功能模塊所用的表前綴:系統前綴+fm+表名
-- #########################【系統管理】#########################
-- ----------------------------
-- Table structure for `s_system_settings`
-- ----------------------------
DROP TABLE IF EXISTS `s_system_settings`;
CREATE TABLE `s_system_settings` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`inc_type` char(64) NOT NULL DEFAULT '' COMMENT '設置分組',
`name` varchar(64) NOT NULL DEFAULT '' COMMENT '設置的key鍵名',
`value` text NULL COMMENT '設置的val值',
`desc` varchar(500) NOT NULL DEFAULT '' COMMENT '描述',
PRIMARY KEY (`id`),
KEY `inc_type` (`inc_type`) USING BTREE,
UNIQUE KEY `inc_type_name` (`inc_type`, `name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='系統設置 表';
INSERT INTO `s_system_settings`
(`inc_type`, `name`, `value`, `desc`)
VALUES
('system', 'name', '網站名稱', ''),
('system', 'describe', '網站描述', ''),
('system', 'logo', '20170710/2bd5de93459bc46ac86c607ea7898004.jpg', '');
-- ----------------------------
-- Records of s_system_settings
-- ----------------------------
-- ----------------------------
-- Table structure for `s_admin`
-- ----------------------------
DROP TABLE IF EXISTS `s_admin`;
CREATE TABLE `s_admin` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '管理員用戶ID',
`nickname` char(16) NOT NULL DEFAULT '' COMMENT '昵稱',
`avatar` varchar(500) NOT NULL DEFAULT '' COMMENT '頭像',
`password` char(40) NOT NULL DEFAULT '' COMMENT '用戶密碼',
`salt` char(32) NOT NULL DEFAULT '' COMMENT '用于保護用戶密碼安全的鹽值',
-- 這個用戶郵箱和手機是唯一的,但是有的人開始并沒有綁定郵箱或手機,所以也不能為空串,不然唯一沖突,所以只能允許為空null了,并且默認為null
`email` char(32) NULL DEFAULT NULL COMMENT '用戶郵箱(可用作登錄名)',
`mobile` char(15) NULL DEFAULT NULL COMMENT '用戶手機(可用作登錄名)',
`login` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '登錄次數',
`create_ip` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '注冊IP',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '注冊時間',
`last_login_ip` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '最后登錄IP',
`last_login_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '最后登錄時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態',
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`) USING BTREE,
UNIQUE KEY `mobile` (`mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='系統管理員 表';
-- ----------------------------
-- Records of s_admin
-- ----------------------------
-- ----------------------------
-- Table structure for `s_salesman`
-- ----------------------------
DROP TABLE IF EXISTS `s_salesman`;
CREATE TABLE `s_salesman` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '管理員用戶ID',
`nickname` char(16) NOT NULL DEFAULT '' COMMENT '昵稱',
`avatar` varchar(500) NOT NULL DEFAULT '' COMMENT '頭像',
`password` char(40) NOT NULL DEFAULT '' COMMENT '用戶密碼',
`salt` char(32) NOT NULL DEFAULT '' COMMENT '用于保護用戶密碼安全的鹽值',
-- 這個用戶郵箱和手機是唯一的,但是有的人開始并沒有綁定郵箱或手機,所以也不能為空串,不然唯一沖突,所以只能允許為空null了,并且默認為null
`email` char(32) NULL DEFAULT NULL COMMENT '用戶郵箱(可用作登錄名)',
`mobile` char(15) NULL DEFAULT NULL COMMENT '用戶手機(可用作登錄名)',
`login` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '登錄次數',
`create_ip` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '注冊IP',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '注冊時間',
`last_login_ip` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '最后登錄IP',
`last_login_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '最后登錄時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態(1-正常,2-業務員離職或者轉讓其它原因,已失效)',
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`) USING BTREE,
UNIQUE KEY `mobile` (`mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='業務員 表';
-- ----------------------------
-- Records of s_salesman
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_salesman`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_salesman`;
CREATE TABLE `s_school_salesman` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學校ID',
`salesman_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '業務員ID',
PRIMARY KEY (`id`),
UNIQUE KEY `school_id` (`school_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='業務員-開通學校 關聯 表(業務員學校開通記錄)';
-- ----------------------------
-- Records of s_school_salesman
-- ----------------------------
-- ----------------------------
-- Table structure for `s_district_education_bureau`
-- ----------------------------
DROP TABLE IF EXISTS `s_district_education_bureau`;
CREATE TABLE `s_district_education_bureau` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '機構名稱,黃岡-區教育局',
`logo` varchar(250) NOT NULL DEFAULT '' COMMENT '機構logo',
`country` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '國家' ,
`province` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '省份' ,
`city` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '城市' ,
`district` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '縣區' ,
`twon` int(11) unsigned NULL DEFAULT 0 COMMENT '鄉鎮' ,
`address` varchar(500) NOT NULL DEFAULT '' COMMENT '詳細地址',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='區教育局 表';
-- ----------------------------
-- Records of s_district_education_bureau
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_apply`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_apply`;
CREATE TABLE `s_school_apply` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(500) NOT NULL DEFAULT '' COMMENT '學校名稱',
`logo` varchar(500) NOT NULL DEFAULT '' COMMENT '學校logo',
`school_id` int(10) unsigned NULL DEFAULT NULL COMMENT '完成后創建的學校ID',
`salesman_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '業務員ID',
`school_scale` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '預估學校規模(學校人數*3)',
`school_type` varchar(250) NOT NULL DEFAULT '' COMMENT '學校類型,多選(1,2):1-幼兒園,2-小學,3-中學,4-高中,5-職校,6-大學',
`education_bureau` varchar(500) NOT NULL DEFAULT '' COMMENT '所屬區教育局',
`is_salesman_admin` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '業務員是否成為管理員',
`admin_account` text NULL COMMENT '管理員賬號(json最多設置三個學校管理員)',
`docking_people` text NULL COMMENT '學校對接負責人(json最多三個人,負責學校開通對接時聯系,姓名,手機號碼)',
`corp_info` text NULL COMMENT '申請的企業號信息(json企業號名稱,注冊信息,機構證書等等)',
`is_branch` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '是否為分校園,0-不為分校,1-為分校',
`school_brand` text NULL COMMENT '學校品牌信息(json如果為分校,則需要提供學校品牌信息,或者已經創建的品牌ID)',
`school_country` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '國家',
`school_province` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '省份',
`school_city` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '城市',
`school_district` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '縣區',
`school_twon` int(11) unsigned NULL DEFAULT 0 COMMENT '鄉鎮',
`school_address` varchar(500) NOT NULL DEFAULT '' COMMENT '學校詳細地址',
`salesman_note` text NULL COMMENT '備注(僅業務員自己可見)',
`complete_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '完成時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態(0-待處理,通過,駁回,完成-表示學校已創建)',
PRIMARY KEY (`id`),
UNIQUE KEY `school_id` (`school_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校申請 表';
-- ----------------------------
-- Records of s_school_apply
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_apply_log`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_apply_log`;
CREATE TABLE `s_school_apply_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`apply_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '申請ID',
`title` varchar(250) NOT NULL DEFAULT '' COMMENT '記錄標題',
`content` text NULL COMMENT '內容',
`type` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '記錄類型(創建,變動,……)',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校申請表的日志 表';
-- ----------------------------
-- Records of s_school_apply_log
-- ----------------------------
-- ----------------------------
-- Table structure for `s_study_period`
-- ----------------------------
DROP TABLE IF EXISTS `s_study_period`;
CREATE TABLE `s_study_period` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(500) NOT NULL DEFAULT '' COMMENT '學段名稱,幼兒園,小學,中學,高中',
`year` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '年制,3,6,3,3',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='【系統】學校學段 表';
-- ----------------------------
-- Records of s_study_period
-- ----------------------------
-- ----------------------------
-- Table structure for `s_study_grade`
-- ----------------------------
DROP TABLE IF EXISTS `s_study_grade`;
CREATE TABLE `s_study_grade` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`period_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬學段ID',
`name` varchar(500) NOT NULL DEFAULT '' COMMENT '年級名稱,學前班、小班、大班,一年級 ~ 六年級,初一……',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='【系統】學校年級 表';
-- ----------------------------
-- Records of s_study_grade
-- ----------------------------
-- END ##################################################
-- ############################【系統應用】############################
-- ----------------------------
-- Table structure for `s_app_category`
-- ----------------------------
DROP TABLE IF EXISTS `s_app_category`;
CREATE TABLE `s_app_category` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`pid` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '上級ID',
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '分類名稱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='應用分類 表';
-- ----------------------------
-- Records of s_app_category
-- ----------------------------
-- ----------------------------
-- Table structure for `s_app`
-- ----------------------------
DROP TABLE IF EXISTS `s_app`;
CREATE TABLE `s_app` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(250) NOT NULL DEFAULT '' COMMENT '應用名稱',
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '應用標示',
`describe` text NULL COMMENT '應用描述',
`menu` text NULL COMMENT '應用自定義菜單',
`right_node` text NULL COMMENT '應用權限節點',
`edition` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '版次,不同于版本號,用于和s_school_agent同步,如果應用更新后需要學校也同步更新,比如菜單更新了',
`author` varchar(250) NOT NULL DEFAULT '' COMMENT '應用作者',
`author_url` varchar(250) NOT NULL DEFAULT '' COMMENT '應用作者網址',
`version` varchar(50) NOT NULL DEFAULT '' COMMENT '版本號',
`is_builtin` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '是否為內建應用:0-否,1-是',
`app_type` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '應用類型(1-官方應用,2-第三方應用)',
`dev_type` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '開發者類型,1-企業開發者,2-個人開發者)',
`category` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬分類id',
`keyword` varchar(250) NOT NULL DEFAULT '' COMMENT '關鍵字',
`is_install` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '是否安裝了:1-已安裝,0-待安裝',
`switch` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '應用開關:1-開,0-關。應用升級、安裝、維護時需要暫時關閉,使其不能訪問,否則會出現意外(即使這樣也還是不能完全避免極端時候的并發問題,不過還是將故障降到最低概率了)',
`status` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '狀態(0-未安裝,1-已安裝,2-已卸載,3-模塊缺失(模塊缺失或文件損壞),4-停用)',
`install_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '安裝時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間(安裝后還可以對應用信息進行修改,除了應用配置信息不可修改外,其它信息可以已更新,比如分類)',
`join_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '加入時間(掃描進來的時間)',
PRIMARY KEY (`id`),
UNIQUE KEY `name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='應用 表(這都是本地應用,是安裝在我們的服務器上的,數據也在我們服務器上面。其實還有一種服務商應用,由服務商提供服務的,那種應用直接由服務商提供數據服務,用戶直接跟服務商簽訂合作關系)';
-- ----------------------------
-- Records of s_app
-- ----------------------------
-- ----------------------------
-- Table structure for `s_app_log`
-- ----------------------------
DROP TABLE IF EXISTS `s_app_log`;
CREATE TABLE `s_app_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`app_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '應用ID',
`title` varchar(250) NOT NULL DEFAULT '' COMMENT '日志標題',
`content` text NULL COMMENT '內容',
`type` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '日志類型,0-其它,1-應用被掃描進來,2-應用安裝,3-應用卸載,4-應用升級,5-應用信息更新',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='應用日志 表';
-- ----------------------------
-- Records of s_app_log
-- ----------------------------
-- END #####################################################
-- ##########################【學校管理】#########################
-- 學校管理系統角色
-- ----------------------------
-- Table structure for `s_school_admin_rule_category`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_admin_rule_category`;
CREATE TABLE `s_school_admin_rule_category` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` char(20) NOT NULL DEFAULT '' COMMENT '分類名稱',
`pid` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬規則類型ID',
PRIMARY KEY (`id`),
UNIQUE KEY `title` (`title`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校管理員 規則分類(分組)表';
INSERT INTO `s_school_admin_rule_category`
(`id`, `title`, `pid`)
VALUES
(1, '學生管理', 0),
(2, '刪除學生', 1);
-- ----------------------------
-- Records of s_school_admin_rule_category
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_admin_rule`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_admin_rule`;
CREATE TABLE `s_school_admin_rule` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` char(20) NOT NULL DEFAULT '' COMMENT '中文名稱',
`rule` char(80) NOT NULL DEFAULT '' COMMENT '規則名(節點名,比如module/controller/action,這是單個的權限碼)',
`category_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬規則類型ID',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '狀態:1-正常,2-停用',
PRIMARY KEY (`id`),
UNIQUE KEY `rule` (`rule`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校管理員 規則表';
INSERT INTO `s_school_admin_rule`
(`title`, `rule`, `status`)
VALUES
('添加學生', 'admin/student/add', 1),
('刪除學生', 'admin/student/del', 1);
-- ----------------------------
-- Records of s_school_admin_rule
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_admin_role`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_admin_role`;
CREATE TABLE `s_school_admin_role` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` char(100) NOT NULL DEFAULT '' COMMENT '角色名稱',
`rules` text NULL COMMENT '規則集,使用逗號分隔,如果為all則表示擁有全部權限',
`status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '狀態',
PRIMARY KEY (`id`),
UNIQUE KEY `title` (`title`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校管理角色 表';
INSERT INTO `s_school_admin_role`
(`title`, `rules`, `status`)
VALUES
('學校管理員', 'all', 1),
('教務管理員', '', 1);
-- 老師不是屬于學校管理員之列
-- ----------------------------
-- Records of s_school_admin_role
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_admin_role_join`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_admin_role_join`;
CREATE TABLE `s_school_admin_role_join` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` mediumint(8) unsigned NOT NULL COMMENT '用戶id',
`role_id` mediumint(8) unsigned NOT NULL COMMENT '角色id',
PRIMARY KEY (`id`),
UNIQUE KEY `user_id_role_id` (`user_id`,`role_id`) USING BTREE,
KEY `user_id` (`user_id`) USING BTREE,
KEY `role_id` (`role_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='學校管理用戶-角色 關聯表';
-- ----------------------------
-- Records of s_school_admin_role_join
-- ----------------------------
-- 上面都是后臺管理的訪問權限,其實還有應用的管理權限
-- ----------------------------
-- Table structure for `s_school_admin`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_admin`;
CREATE TABLE `s_school_admin` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '管理員用戶ID',
`nickname` char(16) NOT NULL DEFAULT '' COMMENT '昵稱',
`avatar` varchar(500) NOT NULL DEFAULT '' COMMENT '頭像',
`password` char(40) NOT NULL DEFAULT '' COMMENT '用戶密碼',
`salt` char(32) NOT NULL DEFAULT '' COMMENT '用于保護用戶密碼安全的鹽值',
-- 這個用戶郵箱和手機是唯一的,但是有的人開始并沒有綁定郵箱或手機,所以也不能為空串,不然唯一沖突,所以只能允許為空null了,并且默認為null
`email` char(32) NULL DEFAULT NULL COMMENT '用戶郵箱(可用作登錄名)',
`mobile` char(15) NULL DEFAULT NULL COMMENT '用戶手機(可用作登錄名)',
`login` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '登錄次數',
`create_ip` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '注冊IP',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '注冊時間',
`last_login_ip` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '最后登錄IP',
`last_login_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '最后登錄時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態',
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`) USING BTREE,
UNIQUE KEY `mobile` (`mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校管理員(包括學校管理員和老師) 表';
-- ----------------------------
-- Records of s_school_admin
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_admin_join`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_admin_join`;
CREATE TABLE `s_school_admin_join` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學校ID',
`admin_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學校管理員ID',
PRIMARY KEY (`id`),
UNIQUE KEY `school_id_admin_id` (`school_id`,`admin_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校管理員-學校 關聯 表';
-- ----------------------------
-- Records of s_school_admin_join
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_corp`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_corp`;
CREATE TABLE `s_school_corp` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`corp_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '企業ID corpid',
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '企業名稱',
`portrait` varchar(500) NOT NULL DEFAULT '' COMMENT '企業頭像',
`secret` varchar(500) NOT NULL DEFAULT '' COMMENT '通訊錄secret',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態:1-正常,0-待審核',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時間',
PRIMARY KEY (`id`),
UNIQUE KEY `corp_id` (`corp_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='企業號 表(和學校是一一對應的關系,一個【獨立的學校個體】對應一個企業號(獨立的學校個體指的是有學校獨立注冊文件的機構,分校也是獨立的個體。但是校區就不是了的))';
-- ----------------------------
-- Records of s_school_corp
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_agent`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_agent`;
CREATE TABLE `s_school_agent` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '這個ID是我們系統的學校/企業的應用ID',
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學校ID',
`app_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應的app_id,app表的ID',
`agent_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT 'agent_id(這個ID在微信內并不是唯一的,只是在企業內唯一)',
`corp_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '企業ID corpid',
`describe` text NULL COMMENT '描述',
`menu` text NULL COMMENT '應用自定義菜單',
`right_node` text NULL COMMENT '應用權限節點',
`edition` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '版次,不同于版本號,用于和s_app同步,如果應用更新后需要學校也同步更新,比如菜單更新了',
`secret` varchar(500) NOT NULL DEFAULT '' COMMENT 'secret',
`token` varchar(500) NOT NULL DEFAULT '' COMMENT 'token,應用回調模式的Token,在應用的回調模式里面設置',
`encoding_aes_key` varchar(500) NOT NULL DEFAULT '' COMMENT 'encoding_aes_key,應用回調模式的加密串,在應用的回調模式里面設置',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態(1-正常,2-系統停用,3-未啟用,4-未同步(這邊創建了,但是企業微信未上線),……)',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '修改時間',
PRIMARY KEY (`id`),
UNIQUE KEY `corp_id_agent_id` (`corp_id`,`agent_id`) USING BTREE,
UNIQUE KEY `school_id_app_id` (`school_id`,`app_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校(企業號)應用 表 (企業號的應用需要系統為其開通,就算企業微信那邊自己設置了應用地址,這邊沒有開通也是沒用的)';
-- 做法是,這邊創建,然后給出參數,引導用戶去企業微信那邊添加應用,然后同步檢查。
-- ----------------------------
-- Records of s_school_agent
-- ----------------------------
-- ----------------------------
-- Table structure for `s_agent_access_token`
-- ----------------------------
DROP TABLE IF EXISTS `s_agent_access_token`;
CREATE TABLE `s_agent_access_token` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`agent_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '應用ID,注意:這不是企業的應用ID,而是我們系統的應用ID',
`access_token` varchar(500) NOT NULL DEFAULT '' COMMENT 'access_token',
`expires_in` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '過期時間',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`),
UNIQUE KEY `agent_id` (`agent_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='企業應用 access_token 表';
-- ----------------------------
-- Records of s_agent_access_token
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school`
-- ----------------------------
DROP TABLE IF EXISTS `s_school`;
CREATE TABLE `s_school` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '學校名稱',
`logo` varchar(500) NOT NULL DEFAULT '' COMMENT '學校logo',
`country` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '國家',
`province` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '省份',
`city` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '城市',
`district` int(11) unsigned NOT NULL DEFAULT 0 COMMENT '縣區',
`twon` int(11) unsigned NULL DEFAULT 0 COMMENT '鄉鎮',
`address` varchar(500) NOT NULL DEFAULT '' COMMENT '學校詳細地址',
`school_type` varchar(250) NOT NULL DEFAULT '' COMMENT '學校類型,多選(1,2):1-幼兒園,2-小學,3-中學,4-高中,5-職校,6-大學',
`education_bureau_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬區教育局ID',
`salesman_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '開通的業務員ID,學校的開通著',
`manager_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '業務經理:當前負責的業務員ID',
`docking_people` text NULL COMMENT '學校對接負責人(json最多三個人,負責學校開通對接時聯系,姓名,手機號碼)',
`is_branch` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '是否為分校園,0-不為分校,1-為分校',
`school_brand_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '主校、學校品牌(機構)ID(如果為分校則由此值)',
`corp_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應的企業ID(非本身企業ID,而是我們Corp表的ID)',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態:1-正常,0-停用',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
PRIMARY KEY (`id`),
UNIQUE KEY `corp_id` (`corp_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校 表(一個學校是一個獨立的個體,分校之間也是獨立的個體,分校和校區是兩個概念)';
-- ----------------------------
-- Records of s_school
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_log`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_log`;
CREATE TABLE `s_school_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學校ID',
`title` varchar(250) NOT NULL DEFAULT '' COMMENT '記錄標題',
`content` text NULL COMMENT '內容',
`type` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '記錄類型(創建,變動,……)',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校變動日志 表';
-- ----------------------------
-- Records of s_school_log
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_campus`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_campus`;
CREATE TABLE `s_school_campus` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬學校ID',
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '校區名稱(如:A區、本部)',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='校區 表';
-- ----------------------------
-- Records of s_school_campus
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_brand`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_brand`;
CREATE TABLE `s_school_brand` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '機構名稱(主學校名稱)',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校(品牌)機構 表';
-- ----------------------------
-- Records of s_school_brand
-- ----------------------------
-- END #########################################
-- #################################【教學基礎】##############################
-- ----------------------------
-- Table structure for `s_school_study_period`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_study_period`;
CREATE TABLE `s_school_study_period` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬學校ID',
`campus_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬校區ID(默認為0)',
`period_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學段ID',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校學段 表';
-- ----------------------------
-- Records of s_school_study_period
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_study_grade`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_study_grade`;
CREATE TABLE `s_school_study_grade` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬學校ID',
`campus_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬校區ID(默認為0)',
`grade_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '年級ID',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校年級 表';
-- ----------------------------
-- Records of s_school_study_grade
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_subject`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_subject`;
CREATE TABLE `s_school_subject` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬學校ID',
`campus_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬校區ID',
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '科目名稱',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校科目 表';
-- ----------------------------
-- Records of s_school_subject
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_parents_student`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_parents_student`;
CREATE TABLE `s_school_parents_student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`subject_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '科目ID',
`teacher_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '老師ID',
PRIMARY KEY (`id`),
UNIQUE KEY `subject_id_teacher_id` (`subject_id`,`teacher_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校科目 - 授課老師 關聯 表';
-- ----------------------------
-- Records of s_school_parents_student
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_class_teacher`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_class_teacher`;
CREATE TABLE `s_school_class_teacher` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`class_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '班級ID',
`teacher_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '老師ID',
`relation` varchar(250) NOT NULL DEFAULT '' COMMENT '關系(多選逗號分隔),1-帶課,2-班主任',
PRIMARY KEY (`id`),
UNIQUE KEY `class_id_teacher_id` (`class_id`,`teacher_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='班級-老師 關聯 表(建立課程表時就和班級發生關聯了,而不是加入時選班級)';
-- ----------------------------
-- Records of s_school_class_teacher
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_syllabus`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_syllabus`;
CREATE TABLE `s_school_syllabus` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬學校ID',
`campus_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬校區ID',
`class_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬班級ID',
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '課程表名稱(春季課程表)',
`start_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '課程表開始日期',
`end_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '課程表結束日期',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校課程 表(這個好像應該是要模塊來支持的啊)';
-- ----------------------------
-- Records of s_school_syllabus
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_syllabus_detail`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_syllabus_detail`;
CREATE TABLE `s_school_syllabus_detail` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`syllabus` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應課程表ID',
`subject_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '科目ID(如果為空,則表示沒有課,詳見課節名,比如可能是統一的眼保健操)',
`teacher_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '老師ID(帶課老師ID)(如果為空,則表示沒有課,詳見課節名,比如可能是統一的眼保健操)',
`time` varchar(50) NOT NULL DEFAULT '' COMMENT '節次/課節名(第一節課,午休,早讀,眼保健操)',
`week` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '星期,周幾1~7',
`lesson_name` varchar(500) NOT NULL DEFAULT '' COMMENT '時間段,09:10 ~ 10:30',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='課程表詳細 表';
-- ----------------------------
-- Records of s_school_syllabus_detail
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_class`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_class`;
CREATE TABLE `s_school_class` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬學校ID',
`campus_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬校區ID(默認為0)',
`name` varchar(500) NOT NULL DEFAULT '' COMMENT '班級名稱',
`is_graduation` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '是否畢業:0-正常,1-已畢業',
`graduation_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '畢業時間(畢業年份)',
`open_class` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '開班時間(開班年份)',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態:1-正常,2-停用',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='班級 表';
-- ----------------------------
-- Records of s_school_class
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_teacher`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_teacher`;
CREATE TABLE `s_school_teacher` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應的用戶ID,oauth_user表的ID',
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬學校ID',
`campus_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬校區ID(默認為0)',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態:1-正常,2-停用',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='老師 表(老師和成員是一對一的,老師和班級通過關聯表進行關聯)';
-- ----------------------------
-- Records of s_school_teacher
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_student`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_student`;
CREATE TABLE `s_school_student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬學校ID',
`campus_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬校區ID(默認為0)',
`class_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬班級ID',
`name` varchar(500) NOT NULL DEFAULT '' COMMENT '學生姓名',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態:1-正常,2-停用',
`is_claim` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '是否有家長認領:0-無,1-是',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`),
-- 班級內學生不能夠重名(檢測到重名時自動名稱XX02)
UNIQUE KEY `class_id_name` (`class_id`,`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學生 表';
-- ----------------------------
-- Records of s_school_student
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_parents`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_parents`;
CREATE TABLE `s_school_parents` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應的用戶ID,oauth_user表的ID',
`campus_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '所屬校區ID(默認為0)',
`identity` varchar(50) NOT NULL DEFAULT '' COMMENT '家長身份,爸爸,媽媽,其它,還可以自定義(僅第一次關聯時可以設置身份)',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態:1-正常,2-停用',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='家長 表(家長和成員是一對一的,家長和學生通過關聯表關聯)';
-- ----------------------------
-- Records of s_school_parents
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_parents_student`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_parents_student`;
CREATE TABLE `s_school_parents_student` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '家長ID',
`student_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學生ID',
PRIMARY KEY (`id`),
UNIQUE KEY `parent_id_student_id` (`parent_id`,`student_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='家長-學生 關聯 表';
-- ----------------------------
-- Records of s_school_parents_student
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_parents_class`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_parents_class`;
CREATE TABLE `s_school_parents_class` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '家長ID',
`class_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '班級ID',
PRIMARY KEY (`id`),
UNIQUE KEY `parent_id_class_id` (`parent_id`,`class_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='家長-班級 關聯 表';
-- ----------------------------
-- Records of s_school_parents_class
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_class_log`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_class_log`;
CREATE TABLE `s_school_class_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`class_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '班級ID',
`title` varchar(250) NOT NULL DEFAULT '' COMMENT '記錄標題',
`content` text NULL COMMENT '內容',
`type` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '記錄類型(升級,改名,變動,……)',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='班級變動記錄(日志) 表';
-- ----------------------------
-- Records of s_school_class_log
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_student_log`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_student_log`;
CREATE TABLE `s_school_student_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`student_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學生ID',
`title` varchar(250) NOT NULL DEFAULT '' COMMENT '記錄標題',
`content` text NULL COMMENT '內容',
`type` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '記錄類型(調班,留級,……)',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學生變動記錄(日志) 表';
-- ----------------------------
-- Records of s_school_student_log
-- ----------------------------
-- END ######################################################
-- ###################【企業微信 通訊錄 & 系統相關用戶模塊】######################
-- ----------------------------
-- Table structure for `s_oauth_user`
-- ----------------------------
DROP TABLE IF EXISTS `s_oauth_user`;
CREATE TABLE `s_oauth_user` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用戶ID',
`nickname` char(16) NOT NULL DEFAULT '' COMMENT '昵稱/姓名,比如家長的姓名,老師的姓名',
`avatar` varchar(500) NOT NULL DEFAULT '' COMMENT '頭像',
`password` char(40) NOT NULL DEFAULT '' COMMENT '用戶密碼',
`salt` char(32) NOT NULL DEFAULT '' COMMENT '用于保護用戶密碼安全的鹽值',
-- 這個用戶郵箱和手機是唯一的,但是有的人開始并沒有綁定郵箱或手機,所以也不能為空串,不然唯一沖突,所以只能允許為空null了,并且默認為null
`email` char(32) NULL DEFAULT NULL COMMENT '用戶郵箱(可用作登錄名)',
`mobile` char(15) NULL DEFAULT NULL COMMENT '用戶手機(可用作登錄名)',
`login` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '登錄次數',
`create_ip` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '注冊IP',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '注冊時間',
`last_login_ip` bigint(20) unsigned NOT NULL DEFAULT 0 COMMENT '最后登錄IP',
`last_login_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '最后登錄時間',
`update_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '更新時間',
`status` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '狀態',
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`email`) USING BTREE,
UNIQUE KEY `mobile` (`mobile`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='oauth_user 表(唯一的用戶,手機號碼和郵箱標示唯一性)';
-- ----------------------------
-- Records of s_oauth_user
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_oauth`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_oauth`;
CREATE TABLE `s_school_oauth` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學校ID',
`openid` varchar(250) NULL DEFAULT NULL COMMENT 'openid,OpenId(非企業成員的標識,對當前企業唯一)',
`member_id` varchar(250) NULL DEFAULT NULL COMMENT '成員ID,即接口的UserID字段(成員UserID,對當前企業唯一)',
`user_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '對應的用戶ID,oauth_user表的ID',
`subscribe` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '關注狀態,是否關注了微信插件',
`create_time` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '創建時間',
PRIMARY KEY (`id`),
UNIQUE KEY `school_id_openid` (`school_id`,`openid`) USING BTREE,
UNIQUE KEY `school_id_user_id` (`school_id`,`user_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='school_oauth 表(成員表)';
-- 通訊錄成員表(這些成員就是要使用微信插件的用戶:家長,老師)
-- ----------------------------
-- Records of s_school_oauth
-- ----------------------------
-- 【通訊錄-組織】(這是最好的方案,部門組織應該清晰明了的展示學校結構,便于查找成員)
-- XXX學校通訊錄
-- 班級
-- 幼兒園
-- 小班(一班)
-- 教師組
-- XXX(職位:代課老師、班主任、副班主任)
-- ……
-- 家長組
-- XXX(職位:AAA的媽媽)
-- ……
-- ……
-- ……
-- 全體教師組(除了家長和學生外,學校的其它任何成員,都稱為“老師”,包括校長,后勤)
-- XXX(老師)(職位:班主任,代課老師)
-- ……
-- 身份組(系統用來標示身份的,系統默認,不可修改,這是系統用來控制應用可見范圍權限的)
-- 家長
-- 代課老師
-- 班主任
-- 學校領導
-- ----------------------------
-- Table structure for `s_school_organization`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_organization`;
CREATE TABLE `s_school_organization` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`school_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '學校ID',
`pid` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '上級ID',
`name` varchar(250) NOT NULL DEFAULT '' COMMENT '部門名稱',
`is_default` tinyint(1) unsigned NOT NULL DEFAULT 0 COMMENT '是否為系統默認(1-系統默認的部門不能更改;0-非系統默認的,是用戶可以自定義修改創建的部門)',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='學校組織部門表 表';
-- ----------------------------
-- Records of s_school_organization
-- ----------------------------
-- ----------------------------
-- Table structure for `s_school_member_organization`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_member_organization`;
CREATE TABLE `s_school_member_organization` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`member_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '成員ID(s_school_oauth表的ID)',
`organization_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '組織ID',
PRIMARY KEY (`id`),
UNIQUE KEY `member_id_organization_id` (`member_id`,`organization_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='成員 - 部門組織 關聯 表';
-- ----------------------------
-- Records of s_school_member_organization
-- ----------------------------
-- ################################【應用權限管理】#########################
-- 應用權限(分為:可見和使用權限,控制使用權限就自動設置可見權限了)
-- ----------------------------
-- Table structure for `s_school_member_organization`
-- ----------------------------
DROP TABLE IF EXISTS `s_school_member_organization`;
CREATE TABLE `s_school_member_organization` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`agent_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '應用ID(s_school_agent表的ID)',
`obj_id` int(10) unsigned NOT NULL DEFAULT 0 COMMENT '組織ID',
`type` tinyint(4) unsigned NOT NULL DEFAULT 0 COMMENT '對象類型,1-組織,2-成員',
PRIMARY KEY (`id`),
UNIQUE KEY `agent_id_obj_id_type` (`agent_id`,`obj_id`,`type`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='應用(成員)可見權限 表(即應用的可見范圍,貌似沒有設置可見范圍的接口,服務商的應用也是安裝時選擇可見范圍的,也就是說沒有提供這個接口,需要企業微信管理員自己去企業微信后臺設置權限,但不排除以后會增加該接口)';
-- ----------------------------
-- Records of s_school_member_organization
-- ----------------------------
-- 【應用更細化的權限控制】(細化到操作)(不同角色可能有統一模塊的可見權限,但是具體的操作權限可能不同)
-- 【成員】
-- 前臺 應用可見權限(細化到部門和成員,上面已經實現)
-- 前臺 操作權限(細化到部門和成員)
-- 【學校管理員】
-- 后臺 應用可見(管理)權限(細化到角色和賬號)
-- 后臺 操作權限(細化到角色和賬號)
-- 【老師】
-- 后臺 應用可見權限(細化到角色和賬號)
-- 后臺 操作權限(細化到角色和賬號)
-- 應用權限規則(節點,注意這個分為前臺和后臺) 表
-- 部門/角色/成員/賬號 & 規則 關聯 表
-- 見權限設計的討論,已經設計確定好了
```
last update:2017-9-22 15:35:44