39 lines
2.1 KiB
MySQL
39 lines
2.1 KiB
MySQL
|
-- =============================================
|
|||
|
|
-- 租户-项目两级架构 - 建表脚本
|
|||
|
|
-- =============================================
|
|||
|
|
|
|||
|
|
-- 1. 项目表
|
|||
|
|
CREATE TABLE IF NOT EXISTS system_project (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '项目编号',
|
|||
|
|
tenant_id BIGINT NOT NULL COMMENT '所属租户编号',
|
|||
|
|
name VARCHAR(100) NOT NULL COMMENT '项目名称',
|
|||
|
|
code VARCHAR(50) NOT NULL COMMENT '项目编码（如楼盘编号）',
|
|||
|
|
status TINYINT DEFAULT 0 COMMENT '状态（0=正常, 1=禁用）',
|
|||
|
|
contact_name VARCHAR(30) COMMENT '项目联系人',
|
|||
|
|
contact_mobile VARCHAR(30) COMMENT '联系手机',
|
|||
|
|
address VARCHAR(500) COMMENT '项目地址',
|
|||
|
|
remark VARCHAR(500) COMMENT '备注',
|
|||
|
|
creator VARCHAR(64) DEFAULT '' COMMENT '创建者',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
updater VARCHAR(64) DEFAULT '' COMMENT '更新者',
|
|||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
deleted BIT DEFAULT 0 COMMENT '是否删除',
|
|||
|
|
UNIQUE KEY uk_tenant_code (tenant_id, code, deleted),
|
|||
|
|
INDEX idx_tenant (tenant_id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='项目表';
|
|||
|
|
|
|||
|
|
-- 2. 用户-项目关联表
|
|||
|
|
CREATE TABLE IF NOT EXISTS system_user_project (
|
|||
|
|
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '编号',
|
|||
|
|
user_id BIGINT NOT NULL COMMENT '用户编号',
|
|||
|
|
project_id BIGINT NOT NULL COMMENT '项目编号',
|
|||
|
|
tenant_id BIGINT NOT NULL COMMENT '租户编号',
|
|||
|
|
creator VARCHAR(64) DEFAULT '' COMMENT '创建者',
|
|||
|
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
|||
|
|
updater VARCHAR(64) DEFAULT '' COMMENT '更新者',
|
|||
|
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
|||
|
|
deleted BIT DEFAULT 0 COMMENT '是否删除',
|
|||
|
|
UNIQUE KEY uk_user_project (user_id, project_id, deleted),
|
|||
|
|
INDEX idx_project (project_id)
|
|||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户-项目关联表';
|