1.创建员工相关表
CREATE TABLE t_dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
dname VARCHAR(20) NOT NULL COMMENT "部门名称",
UNIQUE unq_dname(dname)
) COMMENT = "部门表"
CREATE TABLE t_dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
job VARCHAR(20) NOT NULL COMMENT "职位名称",
UNIQUE unq_job(job)
) COMMENT = "职位表"
CREATE TABLE t_emp(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
wid VARCHAR(20) NOT NULL COMMENT "工号",
ename VARCHAR(20) NOT NULL COMMENT "姓名",
sex CHAR(1) NOT NULL COMMENT "性别",
married BOOLAEAN NOT NULL COMMENT "婚否",
education TINYINT NOT NULL COMMENT "学历：1大专，2本科，3研究生，4博士，5其他",
tel CHAR(11) NOT NULL COMMENT "电话",
email VARCHAR(200) NOT NULL COMMENT "邮箱",
adress VARCHAR(200) NOT NULL COMMENT "住址",
job_id INT UNSIGNED NOT NULL COMMENT "职务ID",
dept_id INT UNSIGNED NOT NULL COMMENT "部门ID",
mgr_id INT UNSIGNED NOT NULL COMMENT "上司ID",
hiredate DATE NOT NULL COMMENT "入职日期",
termdate DATE NOT NULL COMMENT "离职日期",
`status` TINYINT UNSIGNED NOT NULL COMMENT "状态：1在职，2休假，3离职，4死亡",
INDEX idx_job_id(job_id),
INDEX idx_dept_id(dept_id),
INDEX idx_status(`status`),
INDEX idx_mgr_id(mgr_id),
INDEX idx_wid(wid),
UNIQUE unq_wid(wid)
) COMMENT = "员工表"
CREATE TABLE t_role(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
role VARCHAR(20) NOT NULL COMMENT "角色名称",
UNIQUE unq_role(role)
) COMMENT = "角色表"
2.创建用户相关表
CREATE TABLE t_user(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
username VARCHAR(200) NOT NULL COMMENT "用户名",
`password` VARCHAR(2000) NOT NULL COMMENT "密码",
married BOOLAEAN NOT NULL COMMENT "婚否",
education TINYINT NOT NULL COMMENT "学历：1大专，2本科，3研究生，4博士，5其他",
emp_id INT UNSIGNED NOT NULL COMMENT "员工ID",
role_id INT UNSIGNED NOT NULL COMMENT "角色ID",
`status` TINYINT UNSIGNED NOT NULL COMMENT "状态：1可用，2禁用,
INDEX idx_emp_id(emp_id),
INDEX idx_role_id(role_id),
INDEX idx_username(username),
UNIQUE unq_username(username)
) COMMENT = "用户表"
3.创建快递相关表
CREATE TABLE t_delivery(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
order_id INT UNSIGNED NOT NULL COMMENT "订单ID",
sku JSON NOT NULL COMMENT "商品 [sku_id,sku_id]",
qa_id INT UNSIGNED NOT NULL COMMENT "质检员ID",
de_id INT UNSIGNED NOT NULL COMMENT "发货员ID",
postid INT UNSIGNED NOT NULL COMMENT "快递单号",
price DECIMAL(10,2) UNSIGNED NOT NULL COMMENT "快递费",
ecp TINYINT UNSIGNED NOT NULL COMMENT "快递公司单号 1EMS，2顺丰",
adress_id INT UNSIGNED NOT NULL COMMENT "收货地址ID",
warehouse_id INT UNSIGNED NOT NULL COMMENT "仓库ID",
create_time TIMESTAMP INT UNSIGNED NOT NULL DEFAULT NOW() COMMENT "添加时间",
INDEX idx_order_id(order_id),
INDEX idx_qa_id(qa_id),
INDEX idx_de_id(de_id),
INDEX idx_postid(postid),
INDEX idx_warehouse_id(warehouse_id),
INDEX idx_adress_id(adress_id),
INDEX idx_ecp(ecp)
) COMMENT = "快递表"
CREATE TABLE t_backstock(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
order_id INT UNSIGNED NOT NULL COMMENT "订单ID",
sku JSON NOT NULL COMMENT "商品 [sku_id,sku_id]",
reason VARCHAR(200) NOT NULL COMMENT "退货原因",
qa_id INT UNSIGNED NOT NULL COMMENT "质检员ID",
payment DECIMAL(10,2) UNSIGNED NOT NULL COMMENT "退款金额",
payment_type TINYINT UNSIGNED NOT NULL COMMENT "退款方式：1借记卡，2信用卡，3微信，4支付宝，5现金",
`status` TINYINT UNSIGNED NOT NULL COMMENT "状态：1退货成功，2无法退货,
INDEX idx_order_id(order_id),
INDEX idx_qa_id(qa_id),
INDEX idx_username(username),
UNIQUE unq_status(`status`)
) COMMENT = "退货表"
4.创建评价表
CREATE TABLE t_rating(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
order_id INT UNSIGNED NOT NULL COMMENT "订单ID",
sku_id INT UNSIGNED NOT NULL COMMENT "商品ID",
img JSON COMMENT "图片集合",
rating TINYINT NOT NULL COMMENT "评分",
`comment` VARCHAR(200) COMMENT "评论",
role_id INT UNSIGNED NOT NULL COMMENT "角色ID",
create_time TIMESTAMP INT UNSIGNED NOT NULL DEFAULT NOW() COMMENT "添加时间",
INDEX idx_order_id(order_id),
INDEX idx_sku_id(sku_id),
INDEX idx_create_time(create_time)
) COMMENT = "评价表"
5.创建供货商相关表
CREATE TABLE t_supplier(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
`code` VARCHAR(200) NOT NULL COMMENT "供货商编号",
`name` VARCHAR(200) NOT NULL COMMENT "供货商名称",
type TINYINT NOT NULL COMMENT "类型：1厂家，2代理商，3个人",
link_man VARCHAR(20) NOT NULL COMMENT "联系人",
tel VARCHAR(20) NOT NULL COMMENT "联系电话",
adress VARCHAR(200) NOT NULL COMMENT "联系地址",
bank_name VARCHAR(200) COMMENT "开户银行名称",
bank_account VARCHAR(200) COMMENT "开户银行账号",
`status` TINYINT UNSIGNED NOT NULL COMMENT "状态：1可用，2不可用,
INDEX idx_code(`code`),
INDEX idx_type(type),
INDEX idx_status(`status`),
UNIQUE unq_code(`code`)
) COMMENT = "供货商表"
CREATE TABLE t_supplier_sku(
supplier_id INT UNSIGNED NOT NULL COMMENT "供货商ID",
sku_id INT UNSIGNED NOT NULL COMMENT "商品ID",
PRIMARY KEY(supplier_id, sku_id)
) COMMENT = "供货商关联商品表"
6.创建采购入库相关表
CREATE TABLE t_purchase(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
sku_id INT UNSIGNED NOT NULL COMMENT "商品ID",
num INT UNSIGNED NOT NULL COMMENT "数量",
warehouse_id INT UNSIGNED NOT NULL COMMENT "仓库ID",
in_price DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT "采购价格",
out_price DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT "建议零售价",
buyer_id INT UNSIGNED NOT NULL COMMENT "采购员ID",
`status` TINYINT UNSIGNED NOT NULL COMMENT "状态：1未完成，2已完成",
create_time TIMESTAMP INT UNSIGNED NOT NULL DEFAULT NOW() COMMENT "添加时间",
INDEX idx_sku_id(sku_id),
INDEX idx_warehouse_id(warehouse_id),
INDEX idx_buyer_id(buyer_id),
INDEX idx_status(`status`),
INDEX idx_create_time(create_time),
) COMMENT = "采购表"
CREATE TABLE t_production(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
storekeeper_id INT UNSIGNED NOT NULL COMMENT "保管员ID",
amount DECIMAL(15, 2) UNSIGNED NOT NULL COMMENT "总金额",
supplier_id INT UNSIGNED NOT NULL COMMENT "供应商ID",
payment DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT "实付金额",
payment_type TINYINT UNSIGNED NOT NULL COMMENT "支付方式",
invoice BOOLEAN NOT NULL COMMENT "是否开票",
remark VARCHAR(200) COMMENT "备注",
create_time TIMESTAMP INT UNSIGNED NOT NULL DEFAULT NOW() COMMENT "添加时间",
INDEX idx_storekeeper_id(storekeeper_id),
INDEX idx_supplier_id(supplier_id),
INDEX idx_payment_type(payment_type),
INDEX idx_status(`status`),
INDEX idx_create_time(create_time),
) COMMENT = "入库信息表"
CREATE TABLE t_production_purchase(
production_id INT UNSIGNED NOT NULL COMMENT "入库ID",
purchase_id INT UNSIGNED NOT NULL COMMENT "采购ID",
PRIMARY KEY(production_id, purchase_id)
) COMMENT = "入库商品表"
总结
