From 067fe738f379c2f9bf44a92416ede67e2a3ee771 Mon Sep 17 00:00:00 2001
From: wuxw <928255095@qq.com>
Date: 星期日, 06 十月 2019 17:58:43 +0800
Subject: [PATCH] 优化sql脚本
---
java110-db/db/UserService/create_organization.sql | 188 +++++++---------------------------------------
1 files changed, 30 insertions(+), 158 deletions(-)
diff --git a/java110-db/db/UserService/create_organization.sql b/java110-db/db/UserService/create_organization.sql
index 568f96b..bb4e2ff 100644
--- a/java110-db/db/UserService/create_organization.sql
+++ b/java110-db/db/UserService/create_organization.sql
@@ -1,174 +1,46 @@
--- business_user
-
-create table business_user(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
+-- 缁勭粐閮ㄩ棬
+create table business_org(
+ org_id VARCHAR(30) NOT NULL COMMENT '缁勭粐ID',
b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟Id',
- user_id VARCHAR(30) NOT NULL COMMENT '鐢ㄦ埛ID',
- name VARCHAR(50) NOT NULL COMMENT '鍚嶇О',
- email VARCHAR(30) COMMENT '閭鍦板潃',
- address VARCHAR(200) COMMENT '鐜板眳浣忓湴鍧�',
- password varchar(128) comment '鐢ㄦ埛瀵嗙爜锛屽姞瀵嗚繃鍚�',
- location_cd varchar(8) comment '鐢ㄦ埛鍦板尯锛岀紪鐮佽瑙� u_location',
- age int comment '鐢ㄦ埛骞撮緞',
- sex varchar(1) comment '鎬у埆锛�0琛ㄧず鐢峰 1琛ㄧず濂冲',
- tel varchar(11) comment '鐢ㄦ埛鎵嬫満',
- level_cd varchar(2) not null default '0' comment '鐢ㄦ埛绾у埆,鍏宠仈user_level',
+ store_id varchar(30) not null comment '鍟嗘埛ID',
+ org_name varchar(200) not null comment '缁勭粐鍚嶇О',
+ org_level varchar(10) not null comment '缁勭粐绾у埆1 鍏徃绾� 2 鍒嗗叕鍙哥骇锛�3 閮ㄩ棬绾� 鏌ョ湅t_dict琛�',
+ parent_org_id varchar(30) not null comment '涓婄骇缁勭粐ID锛屼竴绾ф椂濉啓org_id',
+ description varchar(200) not null comment '缁勭粐鎻忚堪',
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
operate VARCHAR(3) NOT NULL COMMENT '鏁版嵁鐘舵�侊紝娣诲姞ADD锛屼慨鏀筂OD 鍒犻櫎DEL'
);
--- user_level
-
-CREATE TABLE user_level(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
- level_cd VARCHAR(4) NOT NULL UNIQUE COMMENT '鐢ㄦ埛绾у埆',
- `name` VARCHAR(50) NOT NULL COMMENT '鍚嶇О',
- description VARCHAR(200) COMMENT '鎻忚堪',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿'
-);
-
-insert into user_level(level_cd,name,description) values('0','鏅�氱敤鎴�','鏅�氱敤鎴�');
-
--- u_location
-CREATE TABLE u_location(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
- location_cd VARCHAR(4) NOT NULL UNIQUE COMMENT '鍖哄煙缂栫爜',
- level VARCHAR(4) not null comment '鍖哄煙绾у埆锛�1 琛ㄧず涓�绾у湴鍖�',
- `name` VARCHAR(50) NOT NULL COMMENT '鍖哄煙鍚嶇О',
- description VARCHAR(200) COMMENT '鎻忚堪',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿'
-);
-
--- business_user_attr
-CREATE TABLE business_user_attr(
- b_id VARCHAR(30) NOT NULL COMMENT '璁㈠崟ID',
- attr_id VARCHAR(30) NOT NULL COMMENT '灞炴�d',
- user_id VARCHAR(30) NOT NULL COMMENT '鐢ㄦ埛ID',
- spec_cd VARCHAR(12) NOT NULL COMMENT '瑙勬牸id,鍙傝�僺pec琛�',
- value VARCHAR(50) NOT NULL COMMENT '灞炴�у��',
+-- 缁勭粐鍛樺伐鍏崇郴
+create table business_org_staff_rel(
+ rel_id varchar(30) not null comment '缁勭粐鍛樺伐ID',
+ b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟Id',
+ org_id varchar(30) not null comment '缁勭粐ID',
+ staff_id varchar(30) not null comment '鍛樺伐ID',
+ rel_cd varchar(30) not null comment '鍏崇郴瑙掕壊锛�10000 鏅�氬憳宸ワ紝 20000閮ㄩ棬缁忕悊 鏌ョ湅t_dict琛�',
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
operate VARCHAR(3) NOT NULL COMMENT '鏁版嵁鐘舵�侊紝娣诲姞ADD锛屼慨鏀筂OD 鍒犻櫎DEL'
);
--- u_user
-create table u_user(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
- user_id VARCHAR(30) NOT NULL UNIQUE COMMENT '鐢ㄦ埛ID',
- name VARCHAR(50) NOT NULL COMMENT '鍚嶇О',
- email VARCHAR(30) COMMENT '閭鍦板潃',
- address VARCHAR(200) COMMENT '鐜板眳浣忓湴鍧�',
- password varchar(128) comment '鐢ㄦ埛瀵嗙爜锛屽姞瀵嗚繃鍚�',
- location_cd varchar(8) comment '鐢ㄦ埛鍦板尯锛岀紪鐮佽瑙� u_location',
- age int comment '鐢ㄦ埛骞撮緞',
- sex varchar(1) comment '鎬у埆锛�0琛ㄧず鐢峰 1琛ㄧず濂冲',
- tel varchar(11) comment '鐢ㄦ埛鎵嬫満',
- level_cd varchar(2) not null default '0' comment '鐢ㄦ埛绾у埆,鍏宠仈user_level',
+create table u_org(
+ org_id VARCHAR(30) NOT NULL COMMENT '缁勭粐ID',
b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟Id',
+ store_id varchar(30) not null comment '鍟嗘埛ID',
+ org_name varchar(200) not null comment '缁勭粐鍚嶇О',
+ org_level varchar(10) not null comment '缁勭粐绾у埆1 鍏徃绾� 2 鍒嗗叕鍙哥骇锛�3 閮ㄩ棬绾� 鏌ョ湅t_dict琛�',
+ parent_org_id varchar(30) not null comment '涓婄骇缁勭粐ID锛屼竴绾ф椂濉啓org_id',
+ description varchar(200) not null comment '缁勭粐鎻忚堪',
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
- status_cd VARCHAR(2) NOT NULL default '0' COMMENT '鏁版嵁鐘舵�侊紝璇︾粏鍙傝�僣_status琛紝S 淇濆瓨锛�0, 鍦ㄧ敤 1澶辨晥'
+ status_cd VARCHAR(2) NOT NULL default '0' COMMENT '鏁版嵁鐘舵�侊紝璇︾粏鍙傝�僣_status琛紝0鍦ㄧ敤锛�1澶辨晥'
);
-
--- u_user_attr
-
-CREATE TABLE u_user_attr(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
- attr_id VARCHAR(30) NOT NULL COMMENT '灞炴�d',
- user_id VARCHAR(30) NOT NULL COMMENT '鐢ㄦ埛ID',
- spec_cd VARCHAR(12) NOT NULL COMMENT '瑙勬牸id,鍙傝�僺pec琛�',
- value VARCHAR(50) NOT NULL COMMENT '灞炴�у��',
- b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟ID',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
- status_cd VARCHAR(2) NOT NULL default '0' COMMENT '鏁版嵁鐘舵�侊紝璇︾粏鍙傝�僣_status琛紝S 淇濆瓨锛�0鍦ㄧ敤锛�1澶辨晥'
-);
--- 鐢ㄦ埛 鐗╂祦鍦板潃
-create table business_user_address(
- address_id VARCHAR(30) NOT NULL COMMENT '鍦板潃ID',
+-- 缁勭粐鍛樺伐鍏崇郴
+create table u_org_staff_rel(
+ rel_id varchar(30) not null comment '缁勭粐鍛樺伐ID',
b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟Id',
- user_id VARCHAR(30) NOT NULL COMMENT '鐢ㄦ埛ID',
- tel varchar(11) not null comment '鐢佃瘽',
- postal_code varchar(10) not null comment '閭斂缂栫爜',
- address varchar(200) not null comment '鍦板潃',
- is_default varchar(1) not null comment '鏄惁涓洪粯璁ゅ湴鍧� 1锛岃〃绀洪粯璁ゅ湴鍧� 0 鎴栫┖涓嶆槸榛樿鍦板潃',
+ org_id varchar(30) not null comment '缁勭粐ID',
+ staff_id varchar(30) not null comment '鍛樺伐ID',
+ rel_cd varchar(30) not null comment '鍏崇郴瑙掕壊锛�10000 鏅�氬憳宸ワ紝 20000閮ㄩ棬缁忕悊 鏌ョ湅t_dict琛�',
create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
- operate VARCHAR(3) NOT NULL COMMENT '鏁版嵁鐘舵�侊紝娣诲姞ADD锛屼慨鏀筂OD 鍒犻櫎DEL'
+ status_cd VARCHAR(2) NOT NULL default '0' COMMENT '鏁版嵁鐘舵�侊紝璇︾粏鍙傝�僣_status琛紝0鍦ㄧ敤锛�1澶辨晥'
);
-
--- u_user_address
-create table u_user_address(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
- address_id VARCHAR(30) NOT NULL COMMENT '鍦板潃ID',
- b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟Id',
- user_id VARCHAR(30) NOT NULL COMMENT '鐢ㄦ埛ID',
- tel varchar(11) not null comment '鐢佃瘽',
- postal_code varchar(10) not null comment '閭斂缂栫爜',
- address varchar(200) not null comment '鍦板潃',
- is_default varchar(1) not null comment '鏄惁涓洪粯璁ゅ湴鍧� 1锛岃〃绀洪粯璁ゅ湴鍧� 0 鎴栫┖涓嶆槸榛樿鍦板潃',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
- status_cd VARCHAR(2) NOT NULL default '0' COMMENT '鏁版嵁鐘舵�侊紝璇︾粏鍙傝�僣_status琛紝S 淇濆瓨锛�0鍦ㄧ敤锛�1澶辨晥'
-);
--- 鐢ㄦ埛鏍囩
-create table business_user_tag(
- tag_id VARCHAR(30) NOT NULL COMMENT '鎵撴爣ID',
- b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟Id',
- user_id VARCHAR(30) NOT NULL COMMENT '鐢ㄦ埛ID',
- tag_cd VARCHAR(12) NOT NULL COMMENT '鏍囩缂栫爜,鍙傝�僼ag琛�',
- remark VARCHAR(200) COMMENT '澶囨敞',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
- operate VARCHAR(3) NOT NULL COMMENT '鏁版嵁鐘舵�侊紝娣诲姞ADD锛屼慨鏀筂OD 鍒犻櫎DEL'
-
-);
-
--- 鐢ㄦ埛鏍囩
-create table u_user_tag(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
- tag_id VARCHAR(30) NOT NULL COMMENT '鎵撴爣ID',
- user_id VARCHAR(30) NOT NULL COMMENT '鐢ㄦ埛ID',
- tag_cd VARCHAR(12) NOT NULL COMMENT '鏍囩缂栫爜,鍙傝�僼ag琛�',
- b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟Id',
- remark VARCHAR(200) COMMENT '澶囨敞',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
- status_cd VARCHAR(2) NOT NULL default '0' COMMENT '鏁版嵁鐘舵�侊紝璇︾粏鍙傝�僣_status琛紝S 淇濆瓨锛�0鍦ㄧ敤锛�1澶辨晥'
-);
-
--- 鏍囩
-create table tag(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
- domain varchar(20) not null comment '鏍囩鍩�',
- tag_cd VARCHAR(12) NOT NULL COMMENT '鏍囩缂栫爜',
- `name` VARCHAR(50) NOT NULL COMMENT '鏍囩鍚嶇О',
- description VARCHAR(200) COMMENT '鎻忚堪',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿'
-);
--- 鐢ㄦ埛璇佷欢 琛紙杩囩▼琛級
-create table business_user_credentials(
- credentials_id VARCHAR(30) NOT NULL COMMENT '璇佷欢ID',
- b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟Id',
- user_id VARCHAR(30) NOT NULL COMMENT '鐢ㄦ埛ID',
- credentials_cd varchar(12) not null comment '璇佷欢绫诲瀷',
- value varchar(50) not null comment '璇佷欢鍙风爜',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
- operate VARCHAR(3) NOT NULL COMMENT '鏁版嵁鐘舵�侊紝娣诲姞ADD锛屼慨鏀筂OD 鍒犻櫎DEL'
-);
-
-
--- 鐢ㄦ埛璇佷欢琛�
-create table u_user_credentials(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
- credentials_id VARCHAR(30) NOT NULL COMMENT '璇佷欢ID',
- b_id VARCHAR(30) NOT NULL COMMENT '涓氬姟Id',
- user_id VARCHAR(30) NOT NULL COMMENT '鐢ㄦ埛ID',
- credentials_cd varchar(12) not null comment '璇佷欢绫诲瀷',
- value varchar(50) not null comment '璇佷欢鍙风爜',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿',
- status_cd VARCHAR(2) NOT NULL default '0' COMMENT '鏁版嵁鐘舵�侊紝璇︾粏鍙傝�僣_status琛紝S 淇濆瓨锛�0鍦ㄧ敤锛�1澶辨晥'
-);
-
--- 鏍囩
-create table credentials(
- id INT NOT NULL AUTO_INCREMENT KEY COMMENT 'id',
- credentials_cd VARCHAR(12) NOT NULL COMMENT '璇佷欢缂栫爜',
- `name` VARCHAR(50) NOT NULL COMMENT '璇佷欢鍚嶇О',
- description VARCHAR(200) COMMENT '鎻忚堪',
- create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '鍒涘缓鏃堕棿'
-);
\ No newline at end of file
--
Gitblit v1.8.0