湛兮
69 lines
4.0 KiB
SQL
69 lines
4.0 KiB
SQL
-- 001_initial_schema.sql
|
|
-- 这个迁移文件负责创建项目的基础表结构。
|
|
-- 迁移脚本会按文件名排序执行，所以 001 会先于 002 执行。
|
|
|
|
-- 门店表：保存每个门店的基础信息。
|
|
-- deleted_at 用于软删除，业务查询通常只查询 deleted_at IS NULL 的数据。
|
|
CREATE TABLE IF NOT EXISTS stores (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
name VARCHAR(100) NOT NULL COMMENT '门店名称',
|
|
address VARCHAR(255) NULL COMMENT '门店地址',
|
|
phone VARCHAR(30) NULL COMMENT '门店联系电话',
|
|
status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE' COMMENT '门店状态',
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
deleted_at DATETIME(3) NULL COMMENT '软删除时间，NULL 表示未删除',
|
|
PRIMARY KEY (id),
|
|
KEY idx_stores_status (status),
|
|
KEY idx_stores_deleted_at (deleted_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='门店表';
|
|
|
|
-- 角色表：保存系统内可分配给员工的角色。
|
|
-- code 是稳定的角色编码，适合在代码里做权限判断；name 是展示给用户看的名称。
|
|
CREATE TABLE IF NOT EXISTS roles (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
code VARCHAR(50) NOT NULL COMMENT '角色编码，代码里使用这个值做权限判断',
|
|
name VARCHAR(50) NOT NULL COMMENT '角色名称',
|
|
description VARCHAR(255) NULL COMMENT '角色说明',
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_roles_code (code)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='角色表';
|
|
|
|
-- 员工表：保存员工基础资料，并通过 store_id 关联所属门店。
|
|
-- 这里不直接存角色列表，因为一个员工可以有多个角色，角色关系放在 employee_roles 表。
|
|
CREATE TABLE IF NOT EXISTS employees (
|
|
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
store_id INT UNSIGNED NOT NULL COMMENT '所属门店',
|
|
name VARCHAR(50) NOT NULL COMMENT '员工姓名',
|
|
phone VARCHAR(30) NOT NULL COMMENT '员工手机号',
|
|
status ENUM('ACTIVE', 'INACTIVE') NOT NULL DEFAULT 'ACTIVE' COMMENT '员工状态',
|
|
remark VARCHAR(500) NULL COMMENT '备注',
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
|
deleted_at DATETIME(3) NULL COMMENT '软删除时间，NULL 表示未删除',
|
|
-- MySQL 的唯一索引允许多个 NULL。
|
|
-- 软删除后 active_phone 会变成 NULL，这样同一门店可以重新录入相同手机号的新员工。
|
|
active_phone VARCHAR(30) GENERATED ALWAYS AS (
|
|
CASE WHEN deleted_at IS NULL THEN phone ELSE NULL END
|
|
) STORED COMMENT '仅用于保证同一门店未删除员工手机号唯一',
|
|
PRIMARY KEY (id),
|
|
UNIQUE KEY uk_employees_store_active_phone (store_id, active_phone),
|
|
KEY idx_employees_store_status (store_id, status),
|
|
KEY idx_employees_deleted_at (deleted_at),
|
|
CONSTRAINT fk_employees_store_id FOREIGN KEY (store_id) REFERENCES stores (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工表';
|
|
|
|
-- 员工角色关系表：连接 employees 和 roles。
|
|
-- 一个员工可以有多个角色，一个角色也可以分配给多个员工，所以这是多对多关系表。
|
|
CREATE TABLE IF NOT EXISTS employee_roles (
|
|
employee_id INT UNSIGNED NOT NULL,
|
|
role_id INT UNSIGNED NOT NULL,
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
PRIMARY KEY (employee_id, role_id),
|
|
KEY idx_employee_roles_role_id (role_id),
|
|
CONSTRAINT fk_employee_roles_employee_id FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_employee_roles_role_id FOREIGN KEY (role_id) REFERENCES roles (id)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='员工角色关系表';
|