-- 人员分组：分组主表 + 成员关联表（mp_user 员工）
|
|
CREATE TABLE IF NOT EXISTS mp_staff_group (
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 0,
|
create_id BIGINT NULL,
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
|
del_flag INT NOT NULL DEFAULT 0,
|
|
group_name VARCHAR(128) NOT NULL COMMENT '分组名称',
|
leader_mp_user_id BIGINT NULL COMMENT '负责人（mp_user.id，可为空）',
|
|
KEY idx_mpsg_name (group_name),
|
KEY idx_mpsg_leader (leader_mp_user_id)
|
);
|
|
CREATE TABLE IF NOT EXISTS mp_staff_group_member (
|
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
uuid VARCHAR(64) NOT NULL,
|
version BIGINT NOT NULL DEFAULT 0,
|
create_id BIGINT NULL,
|
create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
update_id BIGINT NULL,
|
update_time DATETIME NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
|
del_flag INT NOT NULL DEFAULT 0,
|
|
group_id BIGINT NOT NULL COMMENT 'mp_staff_group.id',
|
mp_user_id BIGINT NOT NULL COMMENT 'mp_user.id（员工）',
|
|
UNIQUE KEY uk_group_user (group_id, mp_user_id),
|
KEY idx_msgm_group (group_id),
|
KEY idx_msgm_user (mp_user_id)
|
);
|
