新零售数据库（6）:用户和员工以及角色表设计
部门、员工、职位表设计
DROP TABLE IF EXISTS `t_dept`; CREATE TABLE `t_dept` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `dname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '部门名称', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unq_dname`(`dname`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '部门表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_dept -- ---------------------------- INSERT INTO `t_dept` VALUES (1, '董事会', 0); INSERT INTO `t_dept` VALUES (2, '总裁办', 0); INSERT INTO `t_dept` VALUES (3, '零售部', 0); INSERT INTO `t_dept` VALUES (4, '网商部', 0); INSERT INTO `t_dept` VALUES (5, '技术部', 0); INSERT INTO `t_dept` VALUES (6, '售后部', 0); -- ---------------------------- -- Table structure for t_emp -- ---------------------------- DROP TABLE IF EXISTS `t_emp`; CREATE TABLE `t_emp` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `wid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '工号', `ename` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名', `sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '性别', `married` tinyint(1) NOT NULL COMMENT '婚否', `education` tinyint(4) NOT NULL COMMENT '学历：1大专,2本科,3研究生,4博士,5其他', `tel` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话', `email` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱', `address` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '住址', `job_id` int(10) UNSIGNED NOT NULL COMMENT '职务ID', `dept_id` int(10) UNSIGNED NOT NULL COMMENT '部门ID', `mgr_id` int(10) UNSIGNED NULL DEFAULT NULL COMMENT '上司ID', `hiredate` date NOT NULL COMMENT '入职日期', `termdate` date NULL DEFAULT NULL COMMENT '离职日期', `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态：1在职,2休假,3离职,4死亡', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unq_wid`(`wid`) USING BTREE, INDEX `idx_job_id`(`job_id`) USING BTREE, INDEX `idx_dept_id`(`dept_id`) USING BTREE, INDEX `idx_status`(`status`) USING BTREE, INDEX `idx_mgr_id`(`mgr_id`) USING BTREE, INDEX `idx_wid`(`wid`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '员工表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_emp -- ---------------------------- INSERT INTO `t_emp` VALUES (1, 'S10010E', '李娜', '女', 1, 2, '18912345678', NULL, NULL, 5, 3, NULL, '2018-12-06', NULL, 1, 0); INSERT INTO `t_emp` VALUES (2, 'S10014A', '刘畅', '女', 1, 2, '13312345678', NULL, NULL, 6, 3, 1, '2019-04-11', NULL, 1, 0); INSERT INTO `t_emp` VALUES (3, 'TE0023', '陈婷婷', '女', 0, 1, '13322334545', NULL, NULL, 9, 3, NULL, '2019-05-16', NULL, 1, 0); INSERT INTO `t_emp` VALUES (4, 'TE0024', '徐刚', '男', 0, 1, '13322334545', NULL, NULL, 9, 4, NULL, '2019-05-16', NULL, 1, 0); DROP TABLE IF EXISTS `t_job`; CREATE TABLE `t_job` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `job` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '职位名称', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unq_job`(`job`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 10 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '职位表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_job -- ---------------------------- INSERT INTO `t_job` VALUES (1, '董事长', 0); INSERT INTO `t_job` VALUES (2, '总经理', 0); INSERT INTO `t_job` VALUES (3, '部门经理', 0); INSERT INTO `t_job` VALUES (4, '主管', 0); INSERT INTO `t_job` VALUES (5, '店长', 0); INSERT INTO `t_job` VALUES (6, '售货员', 0); INSERT INTO `t_job` VALUES (7, '保安', 0); INSERT INTO `t_job` VALUES (8, '保管员', 0); INSERT INTO `t_job` VALUES (9, '实习生', 0);
用户和角色表创建
DROP TABLE IF EXISTS `t_role`; CREATE TABLE `t_role` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `role` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '角色名称', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unq_role`(`role`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '角色表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_role -- ---------------------------- INSERT INTO `t_role` VALUES (6, '保管员'); INSERT INTO `t_role` VALUES (3, '售货员'); INSERT INTO `t_role` VALUES (5, '客服'); INSERT INTO `t_role` VALUES (2, '管理员'); INSERT INTO `t_role` VALUES (7, '质检员'); INSERT INTO `t_role` VALUES (1, '超级管理员'); INSERT INTO `t_role` VALUES (4, '零售店长'); DROP TABLE IF EXISTS `t_user`; CREATE TABLE `t_user` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `username` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '用户名', `password` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '密码（AES加密）', `emp_id` int(10) UNSIGNED NOT NULL COMMENT '员工ID', `role_id` int(10) UNSIGNED NOT NULL COMMENT '角色ID', `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态：1可用,2禁用', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `last_update_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '最后修改时间', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unq_username`(`username`) USING BTREE, INDEX `idx_username`(`username`) USING BTREE, INDEX `idx_emp_id`(`emp_id`) USING BTREE, INDEX `idx_role_id`(`role_id`) USING BTREE, INDEX `idx_status`(`status`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_user -- ---------------------------- INSERT INTO `t_user` VALUES (1, 'demo123', 'C55E8E99B7745306F1B30AE2C8C8D54B', 1, 4, 1, '2019-04-11 15:26:33', '2019-04-11 15:26:33', 0);
快递表设计
DROP TABLE IF EXISTS `t_delivery`; CREATE TABLE `t_delivery` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `order_id` int(10) UNSIGNED NOT NULL COMMENT '订单ID', `sku` json NOT NULL COMMENT '商品', `qa_id` int(10) UNSIGNED NOT NULL COMMENT '质检员ID', `de_id` int(10) UNSIGNED NOT NULL COMMENT '发货员ID', `postid` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '快递单号', `price` decimal(10, 0) UNSIGNED NOT NULL COMMENT '快递费', `ecp` tinyint(3) UNSIGNED NOT NULL COMMENT '快递公司编号', `address_id` int(10) UNSIGNED NOT NULL COMMENT '收货地址ID', `warehouse_id` int(10) UNSIGNED NOT NULL COMMENT '仓库ID', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_order_id`(`order_id`) USING BTREE, INDEX `idx_qa_id`(`qa_id`) USING BTREE, INDEX `idx_de_id`(`de_id`) USING BTREE, INDEX `idx_postid`(`postid`) USING BTREE, INDEX `idx_warehouse_id`(`warehouse_id`) USING BTREE, INDEX `idx_address_id`(`address_id`) USING BTREE, INDEX `idx_ecp`(`ecp`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '快递表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_delivery -- ---------------------------- INSERT INTO `t_delivery` VALUES (1, 2, '[3, 3]', 15, 17, '125488611212545', 60, 1, 1, 1, '2019-04-11 21:14:38', 0);
退货表
DROP TABLE IF EXISTS `t_backstock`; CREATE TABLE `t_backstock` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `order_id` int(10) UNSIGNED NOT NULL COMMENT '订单ID', `sku` json NOT NULL COMMENT '退货商品', `reason` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '退货原因', `qa_id` int(10) UNSIGNED NOT NULL COMMENT '质检员ID', `payment` decimal(10, 2) UNSIGNED NOT NULL COMMENT '退款金额', `payment_type` tinyint(3) UNSIGNED NOT NULL COMMENT '退款方式：1借记卡，2信用卡，3微信，4支付宝，5现金', `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态：1退货成功,2无法退货', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_order_id`(`order_id`) USING BTREE, INDEX `idx_qa_id`(`qa_id`) USING BTREE, INDEX `idx_status`(`status`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '退货表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_backstock -- ---------------------------- INSERT INTO `t_backstock` VALUES (1, 2, '[3]', '质量问题', 15, 2999.00, 5, 1, '2019-04-11 21:18:06', 0);
评价表设计
DROP TABLE IF EXISTS `t_rating`; CREATE TABLE `t_rating` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '评价ID', `order_id` int(10) UNSIGNED NOT NULL COMMENT '订单ID', `sku_id` int(10) UNSIGNED NOT NULL COMMENT '商品ID', `img` json NULL COMMENT '买家晒图', `rating` tinyint(3) UNSIGNED NOT NULL COMMENT '评分', `comment` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '评论', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_order_id`(`order_id`) USING BTREE, INDEX `idx_sku_id`(`sku_id`) USING BTREE, INDEX `idx_create_time`(`create_time`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '评价表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_rating -- ---------------------------- INSERT INTO `t_rating` VALUES (1, 2, 3, '[\"http://192.168.99.184/1.jpg\"]', 5, '很好用，很漂亮', '2019-04-12 00:17:40', 0);
供货商表设计
DROP TABLE IF EXISTS `t_supplier`; CREATE TABLE `t_supplier` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `code` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '供货商编号', `name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '供货商名称', `type` tinyint(3) UNSIGNED NOT NULL COMMENT '类型：1厂家,2代理商,3个人', `link_man` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '联系人', `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '联系电话', `address` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '联系地址', `bank_name` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '开户银行名称', `bank_account` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '银行账号', `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态:1可用,2不可用', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, UNIQUE INDEX `unq_code`(`code`) USING BTREE, INDEX `idx_code`(`code`) USING BTREE, INDEX `idx_type`(`type`) USING BTREE, INDEX `idx_status`(`status`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '供货商表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_supplier -- ---------------------------- INSERT INTO `t_supplier` VALUES (1, '2394125', 'A供货商', 1, '李强', '13312345678', '辽宁省大连市高新园区121号', NULL, NULL, 1, 0);
DROP TABLE IF EXISTS `t_supplier_sku`; CREATE TABLE `t_supplier_sku` ( `supplier_id` int(10) UNSIGNED NOT NULL COMMENT '供货商ID', `sku_id` int(10) UNSIGNED NOT NULL COMMENT '商品ID', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`supplier_id`, `sku_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '供货商关联商品表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_supplier_sku -- ---------------------------- INSERT INTO `t_supplier_sku` VALUES (1, 1, 0); INSERT INTO `t_supplier_sku` VALUES (1, 2, 0); INSERT INTO `t_supplier_sku` VALUES (1, 3, 0);
采购和入库数据库表设计
DROP TABLE IF EXISTS `t_purchase`; CREATE TABLE `t_purchase` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `sku_id` int(10) UNSIGNED NOT NULL COMMENT '商品ID', `num` int(10) UNSIGNED NOT NULL COMMENT '数量', `warehouse_id` int(10) UNSIGNED NOT NULL COMMENT '仓库ID', `in_price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '采购价格', `out_price` decimal(10, 2) UNSIGNED NULL DEFAULT NULL COMMENT '建议零售价', `buyer_id` int(10) UNSIGNED NOT NULL COMMENT '采购员ID', `status` tinyint(3) UNSIGNED NOT NULL COMMENT '状态：1未完成,2已完成', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_sku_id`(`sku_id`) USING BTREE, INDEX `idx_warehouse_id`(`warehouse_id`) USING BTREE, INDEX `idx_buyer_id`(`buyer_id`) USING BTREE, INDEX `idx_status`(`status`) USING BTREE, INDEX `idx_create_time`(`create_time`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '采购表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_purchase -- ---------------------------- INSERT INTO `t_purchase` VALUES (1, 1, 50, 1, 3000.00, 3299.00, 20, 2, '2019-04-12 19:16:03', 0); DROP TABLE IF EXISTS `t_productin`; CREATE TABLE `t_productin` ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `storekeeper_id` int(10) UNSIGNED NOT NULL COMMENT '保管员ID', `amount` decimal(15, 2) UNSIGNED NOT NULL COMMENT '总金额', `supplier_id` int(10) UNSIGNED NOT NULL COMMENT '供应商ID', `payment` decimal(15, 2) UNSIGNED NOT NULL COMMENT '实付金额', `payment_type` tinyint(3) UNSIGNED NOT NULL COMMENT '支付方式', `invoice` tinyint(1) NOT NULL COMMENT '是否开票', `remark` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '备注', `create_time` timestamp(0) NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`id`) USING BTREE, INDEX `idx_storekeeper_id`(`storekeeper_id`) USING BTREE, INDEX `idx_supplier_id`(`supplier_id`) USING BTREE, INDEX `idx_payment_type`(`payment_type`) USING BTREE, INDEX `idx_create_time`(`create_time`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '入库信息表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_productin -- ---------------------------- INSERT INTO `t_productin` VALUES (1, 42, 150000.00, 1, 150000.00, 1, 1, NULL, '2019-04-12 19:17:10', 0); DROP TABLE IF EXISTS `t_productin_purchase`; CREATE TABLE `t_productin_purchase` ( `productin_id` int(10) UNSIGNED NOT NULL COMMENT '入库ID', `purchase_id` int(10) UNSIGNED NOT NULL COMMENT '采购ID', `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除', PRIMARY KEY (`productin_id`, `purchase_id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '入库商品表' ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of t_productin_purchase -- ---------------------------- INSERT INTO `t_productin_purchase` VALUES (1, 1, 0);
浙公网安备 33010602011771号