系统管理 表设计

CREATE TABLE `sys_user`  (
  `id` int(32) NOT NULL COMMENT '自增id',
  `username` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
  `password` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密码',
  `nickname` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '昵称',
  `headImgUrl` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '头像url',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '手机号',
	`email` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
	`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `sex` tinyint(1) NULL DEFAULT NULL COMMENT '性别',
  `enabled` tinyint(1) NOT NULL DEFAULT 1 COMMENT '状态（1有效,0无效）',
	`delflag` tinyint(1)  NOT NULL DEFAULT 0 COMMENT '类型 0未删除 1 已删除',
  `type` tinyint(4)  NOT NULL DEFAULT 1 COMMENT '类型',
  `createTime` datetime(0) NOT NULL COMMENT '创建时间',
  `updateTime` datetime(0) NOT NULL COMMENT '修改时间',
	`sort` int(11) NOT NULL DEFAULT 0 COMMENT '排序字段',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `username`(`username`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;

CREATE TABLE `sys_permission`  (
  `id` int(32) NOT NULL COMMENT '自增id',
  `permission` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '权限标识',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '名称',
  `createTime` datetime(0) NOT NULL COMMENT '创建时间',
  `updateTime` datetime(0) NOT NULL COMMENT '修改时间',
	`type` tinyint(4)  NOT NULL DEFAULT 1 COMMENT '类型 0菜单 1 按钮',
	`delflag` tinyint(1)  NOT NULL DEFAULT 0 COMMENT '类型 0未删除 1 已删除',
	`sort` int(11) NOT NULL DEFAULT 0 COMMENT '排序字段',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `permission`(`permission`) USING BTREE
) ENGINE = InnoDB  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '权限标识表' ROW_FORMAT = Dynamic;

CREATE TABLE `sys_role`  (
  `id` int(32) NOT NULL COMMENT '自增id',
  `code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '角色code',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '角色名',
	`delflag` tinyint(1)  NOT NULL DEFAULT 0 COMMENT '类型 0未删除 1 已删除',
	`sort` int(11) NOT NULL DEFAULT 0 COMMENT '排序字段',
  `createTime` datetime(0) NOT NULL COMMENT '创建时间',
  `updateTime` datetime(0) NOT NULL COMMENT '修改时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `code`(`code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '角色表' ROW_FORMAT = Dynamic;

CREATE TABLE `sys_org`  (
  `id` int(32) NOT NULL  COMMENT '自增id',
  `code` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组织机构code',
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组织机构名',
  `createTime` datetime(0) NOT NULL COMMENT '创建时间',
  `updateTime` datetime(0) NOT NULL COMMENT '修改时间',
	`delflag` tinyint(1)  NOT NULL DEFAULT 1 COMMENT '类型 0未删除 1 已删除',
	`sort` int(11) NOT NULL DEFAULT 0 COMMENT '排序字段',
	`layer` int(11) NOT NULL DEFAULT 0 COMMENT '排序字段',
	`parentId` int(32) NOT NULL DEFAULT 0 COMMENT '父组织机构的id',
	`parentIds` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '所有父组织机构层的id',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `code`(`code`) USING BTREE
) ENGINE = InnoDB  CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '组织机构表' ROW_FORMAT = Dynamic;

CREATE TABLE `sys_role_permission`  (
	`id` int(32) NOT NULL COMMENT '角色-权限id',
  `roleId` int(32) NOT NULL COMMENT '角色id',
  `permissionId` int(32) NOT NULL COMMENT '权限id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '角色权限关系表' ROW_FORMAT = Dynamic;

CREATE TABLE `sys_user_role`  (
	`id` int(32) NOT NULL COMMENT '用户-角色id',
  `userId` int(32) NOT NULL COMMENT '用户id',
  `roleId` int(32) NOT NULL COMMENT '角色id',
	`type` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1主角色，2 兼任角色',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户角色关系表' ROW_FORMAT = Dynamic;

CREATE TABLE `sys_user_org`  (
	`id` int(32) NOT NULL COMMENT '用户-组织id',
  `userId` int(32) NOT NULL COMMENT '用户id',
  `orgId` int(32) NOT NULL COMMENT '组织id',
	`type` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1主组织，2 兼任组织',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户组织关系表' ROW_FORMAT = Dynamic;