(2) 电商数据库表设计
一. 用户实体
1. 用户登录表（customer_login）
CREATE TABLE `customer_login` (
`customer_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '用户ID',
`login_name` varchar(20) NOT NULL COMMENT '用户登陆名',
`password` char(32) NOT NULL COMMENT 'md5加密的密码',
`user_stats` tinyint NOT NULL DEFAULT '1' COMMENT '用户状态',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB COMMENT='用户登陆表';
2. 用户信息表（customer_inf）
CREATE TABLE `customer_inf` (
`customer_inf_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID',
`customer_id` int unsigned NOT NULL COMMENT 'customer_login表的自增ID',
`customer_name` varchar(20) NOT NULL COMMENT '用户真实姓名',
`identity_card_type` tinyint NOT NULL DEFAULT '1' COMMENT '证件类型：1 身份证,2军官证,3护照',
`identity_card_no` varchar(20) DEFAULT NULL COMMENT '证件号码',
`mobile_phone` int unsigned DEFAULT NULL COMMENT '手机号',
`customer_email` varchar(50) DEFAULT NULL COMMENT '邮箱',
`gender` char(1) DEFAULT NULL COMMENT '性别',
`user_point` int NOT NULL DEFAULT '0' COMMENT '用户积分',
`register_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '注册时间',
`birthday` datetime DEFAULT NULL COMMENT '会员生日',
`customer_level` tinyint NOT NULL DEFAULT '1' COMMENT '会员级别:1普通会员,2青铜会员,3白银会员,4黄金会员,5钻石会员',
`user_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '用户余额',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`customer_inf_id`)
) ENGINE=InnoDB COMMENT='用户信息表';
3. 用户级别表（customer_level_inf）
CREATE TABLE `customer_level_inf` (
`customer_level` tinyint NOT NULL AUTO_INCREMENT COMMENT '会员级别ID',
`level_name` varchar(10) NOT NULL COMMENT '会员级别名称',
`min_point` int unsigned NOT NULL DEFAULT 0 COMMENT '该级别最低积分',
`max_point` int unsigned NOT NULL DEFAULT 0 COMMENT '该级别最高积分',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`customer_level`)
) ENGINE=InnoDB COMMENT='用户信息表';
4. 用户地址表（order_customer_addr）
CREATE TABLE `order_customer_addr` (
`customer_addr_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID',
`customer_id` int unsigned NOT NULL COMMENT 'customer_login表的自增ID',
`zip` smallint NOT NULL COMMENT '邮编',
`province` smallint NOT NULL COMMENT '地区表中省份的id',
`city` smallint NOT NULL COMMENT '地区表中城市的id',
`district` smallint NOT NULL COMMENT '地区表中的区id',
`address` varchar(200) NOT NULL COMMENT '具体的地址门牌号',
`is_default` tinyint NOT NULL COMMENT '是否默认',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`customer_addr_id`)
) ENGINE=InnoDB COMMENT='用户地址表';
5. 用户积分日志表（customer_point_log）
CREATE TABLE `customer_point_log` (
`point_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '积分日志ID',
`customer_id` int unsigned NOT NULL COMMENT '用户ID',
`source` tinyint unsigned NOT NULL COMMENT '积分来源:0订单,1登录,2活动',
`refer_number` int unsigned NOT NULL DEFAULT '0' COMMENT '积分来源相关编号',
`change_point` smallint NOT NULL DEFAULT '0' COMMENT '变更积分数',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '积分日志生成时间',
PRIMARY KEY (`point_id`)
) ENGINE=InnoDB COMMENT='用户积分日志表';
6. 用户余额变动表（customer_balance_log）
CREATE TABLE `customer_balance_log` (
`balance_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '余额日志id',
`customer_id` int unsigned NOT NULL COMMENT '用户ID',
`source` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '记录来源:1订单,2退货单',
`source_sn` int unsigned NOT NULL COMMENT '相关单据ID',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录生成时间',
`amount` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '变动金额',
PRIMARY KEY (`balance_id`)
) ENGINE=InnoDB COMMENT='用户余额变动表';
7. 用户登录日志表（customer_login_log）
CREATE TABLE `customer_login_log` (
`login_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '登录日志ID',
`customer_id` int unsigned NOT NULL COMMENT '登录用户ID',
`login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '用户登录时间',
`login_ip` int unsigned NOT NULL COMMENT '登录IP',
`login_type` tinyint NOT NULL COMMENT '登录类型:0未成功 1成功',
PRIMARY KEY (`login_id`)
) ENGINE=InnoDB COMMENT='用户登录日志表';
二. 商品实体
1. 品牌信息表（product_brand_info）
CREATE TABLE `product_brand_info` (
`brand_id` smallint unsigned NOT NULL AUTO_INCREMENT COMMENT '品牌ID',
`brand_name` varchar(50) NOT NULL COMMENT '品牌名称',
`telephone` varchar(50) NOT NULL COMMENT '联系电话',
`brand_web` varchar(100) DEFAULT NULL COMMENT '品牌网站',
`brand_logo` varchar(100) DEFAULT NULL COMMENT '品牌logo URL',
`brand_desc` varchar(150) DEFAULT NULL COMMENT '品牌描述',
`brand_status` tinyint NOT NULL DEFAULT '0' COMMENT '品牌状态,0禁用,1启用',
`brand_order` tinyint NOT NULL DEFAULT '0' COMMENT '排序',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`brand_id`)
) ENGINE=InnoDB COMMENT='品牌信息表';
2. 商品分类表（product_category）
CREATE TABLE `product_category` (
`category_id` smallint unsigned NOT NULL AUTO_INCREMENT COMMENT '分类ID',
`category_name` varchar(10) NOT NULL COMMENT '分类名称',
`category_code` varchar(10) NOT NULL COMMENT '分类编码',
`parent_id` smallint unsigned NOT NULL DEFAULT '0' COMMENT '父分类ID',
`category_level` tinyint NOT NULL DEFAULT '1' COMMENT '分类层级',
`category_status` tinyint NOT NULL DEFAULT '1' COMMENT '分类状态',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB COMMENT='商品分类表';
3. 供应商信息表（product_supplier_info）
CREATE TABLE `product_supplier_info` (
`supplier_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '供应商ID',
`supplier_code` char(8) NOT NULL COMMENT '供应商编码',
`supplier_name` char(50) NOT NULL COMMENT '供应商名称',
`supplier_type` tinyint NOT NULL COMMENT '供应商类型:1.自营,2.平台',
`link_man` varchar(10) NOT NULL COMMENT '供应商联系人',
`phone_number` varchar(50) NOT NULL COMMENT '联系电话',
`bank_name` varchar(50) NOT NULL COMMENT '供应商开户银行名称',
`bank_account` varchar(50) NOT NULL COMMENT '银行账号',
`address` varchar(200) NOT NULL COMMENT '供应商地址',
`supplier_status` tinyint NOT NULL DEFAULT '0' COMMENT '状态:0禁用,1启用',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`supplier_id`)
) ENGINE=InnoDB COMMENT='供应商信息表';
4. 商品信息表（product_info）
CREATE TABLE `product_info` (
`product_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '商品ID',
`product_code` char(16) NOT NULL COMMENT '商品编码',
`product_name` varchar(50) NOT NULL COMMENT '商品名称',
`bar_code` varchar(50) NOT NULL COMMENT '国条码',
`brand_id` int unsigned NOT NULL COMMENT '品牌表的ID',
`one_category_id` smallint unsigned NOT NULL COMMENT '一级分类ID',
`two_category_id` smallint unsigned NOT NULL COMMENT '二级分类ID',
`three_category_id` smallint unsigned NOT NULL COMMENT '三级分类ID',
`supplier_id` int unsigned NOT NULL COMMENT '商品的供应商id',
`price` decimal(8,2) NOT NULL COMMENT '商品销售价格',
`average_cost` decimal(18,2) NOT NULL COMMENT '商品加权平均成本',
`publish_status` tinyint NOT NULL DEFAULT '0' COMMENT '上下架状态:0下架1上架',
`audit_status` tinyint NOT NULL DEFAULT '0' COMMENT '审核状态:0未审核,1已审核',
`weight` float DEFAULT NULL COMMENT '商品重量',
`length` float DEFAULT NULL COMMENT '商品长度',
`heigh` float DEFAULT NULL COMMENT '商品高度',
`width` float DEFAULT NULL COMMENT '商品宽度',
`color_type` enum('红','黄','蓝','黒') DEFAULT NULL,
`production_date` datetime NOT NULL COMMENT '生产日期',
`shelf_life` int NOT NULL COMMENT '商品有效期',
`descript` text NOT NULL COMMENT '商品描述',
`indate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '商品录入时间',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB COMMENT='商品信息表';
5. 商品图片信息表（product_pic_info）
CREATE TABLE `product_pic_info` (
`product_pic_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '商品图片ID',
`product_id` int unsigned NOT NULL COMMENT '商品ID',
`pic_desc` varchar(50) DEFAULT NULL COMMENT '图片描述',
`pic_url` varchar(200) NOT NULL COMMENT '图片URL',
`is_master` tinyint NOT NULL DEFAULT '0' COMMENT '是否主图:0.非主图1.主图',
`pic_order` tinyint NOT NULL DEFAULT '0' COMMENT '图片排序',
`pic_status` tinyint NOT NULL DEFAULT '1' COMMENT '图片是否有效:0无效 1有效',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`product_pic_id`)
) ENGINE=InnoDB COMMENT='商品图片信息表';
6. 商品评论表（product_comment）
CREATE TABLE `product_comment` (
`comment_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '评论ID',
`product_id` int unsigned NOT NULL COMMENT '商品ID',
`order_id` bigint unsigned NOT NULL COMMENT '订单ID',
`customer_id` int unsigned NOT NULL COMMENT '用户ID',
`title` varchar(50) NOT NULL COMMENT '评论标题',
`content` varchar(300) NOT NULL COMMENT '评论内容',
`audit_status` tinyint NOT NULL COMMENT '审核状态:0未审核1已审核',
`audit_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '评论时间',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`comment_id`)
) ENGINE=InnoDB COMMENT='商品评论表';
三. 订单实体
1. 订单主表（order_master）
CREATE TABLE `order_master` (
`order_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`order_sn` bigint(20) unsigned NOT NULL COMMENT '订单编号 yyyymmddnnnnnnnn',
`customer_id` int unsigned NOT NULL COMMENT '下单人ID',
`shipping_user` varchar(10) NOT NULL COMMENT '收货人姓名',
`province` smallint NOT NULL COMMENT '收货人所在省',
`city` smallint NOT NULL COMMENT '收货人所在市',
`district` smallint NOT NULL COMMENT '收货人所在区',
`address` varchar(100) NOT NULL COMMENT '收货人详细地址',
`payment_method` tinyint NOT NULL COMMENT '支付方式:1现金,2余额,3网银,4支付宝,5微信',
`order_money` decimal(8,2) NOT NULL COMMENT '订单金额',
`district_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠金额',
`shipping_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '运费金额',
`payment_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '支付金额',
`shipping_comp_name` varchar(10) DEFAULT NULL COMMENT '快递公司名称',
`shipping_sn` varchar(50) DEFAULT NULL COMMENT '快递单号',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
`shipping_time` datetime DEFAULT NULL COMMENT '发货时间',
`pay_time` datetime DEFAULT NULL COMMENT '支付时间',
`receive_time` datetime DEFAULT NULL COMMENT '收货时间',
`order_status` tinyint NOT NULL DEFAULT '0' COMMENT '订单状态',
`order_point` int unsigned NOT NULL DEFAULT '0' COMMENT '订单积分',
`invoice_title` varchar(100) DEFAULT NULL COMMENT '发票抬头',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`order_id`),
UNIQUE KEY `ux_ordersn` (`order_sn`)
) ENGINE=InnoDB COMMENT='订单主表';
2. 订单详情表（order_detail）
CREATE TABLE `order_detail` (
`order_detail_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '自增主键ID,订单详情表ID',
`order_id` int unsigned NOT NULL COMMENT '订单表ID',
`product_id` int unsigned NOT NULL COMMENT '订单商品ID',
`product_name` varchar(50) NOT NULL COMMENT '商品名称',
`product_cnt` int NOT NULL DEFAULT '1' COMMENT '购买商品数量',
`product_price` decimal(8,2) NOT NULL COMMENT '购买商品单价',
`average_cost` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '平均成本价格',
`weight` float DEFAULT NULL COMMENT '商品重量',
`fee_money` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '优惠分摊金额',
`w_id` int unsigned NOT NULL COMMENT '仓库ID',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`order_detail_id`)
) ENGINE=InnoDB COMMENT='订单详情表';
3. 购物车表（order_cart）
CREATE TABLE `order_cart` (
`cart_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '购物车ID',
`customer_id` int unsigned NOT NULL COMMENT '用户ID',
`product_id` int unsigned NOT NULL COMMENT '商品ID',
`product_amount` int NOT NULL COMMENT '加入购物车商品数量',
`price` decimal(8,2) NOT NULL COMMENT '商品价格',
`add_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '加入购物车时间',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`cart_id`)
) ENGINE=InnoDB COMMENT='购物车表';
4. 仓库信息表（warehouse_info）
CREATE TABLE `warehouse_info` (
`w_id` smallint unsigned NOT NULL AUTO_INCREMENT COMMENT '仓库ID',
`warehouse_sn` char(5) NOT NULL COMMENT '仓库编码',
`warehouse_name` varchar(10) NOT NULL COMMENT '仓库名称',
`warehouse_phone` varchar(20) NOT NULL COMMENT '仓库电话',
`contact` varchar(10) NOT NULL COMMENT '仓库联系人',
`province` smallint NOT NULL COMMENT '省',
`city` smallint NOT NULL COMMENT '市',
`district` smallint NOT NULL COMMENT '区',
`address` varchar(100) NOT NULL COMMENT '仓库地址',
`warehouse_status` tinyint NOT NULL DEFAULT '1' COMMENT '仓库状态:0禁用,1启用',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`w_id`)
) ENGINE=InnoDB COMMENT='仓库信息表';
5. 商品库存表（warehouse_proudct）
CREATE TABLE `warehouse_proudct` (
`wp_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '商品库存ID',
`product_id` int unsigned NOT NULL COMMENT '商品id',
`w_id` smallint unsigned NOT NULL COMMENT '仓库ID',
`currnet_cnt` int unsigned NOT NULL DEFAULT '0' COMMENT '当前商品数量',
`lock_cnt` int unsigned NOT NULL DEFAULT '0' COMMENT '当前占用数据',
`in_transit_cnt` int unsigned NOT NULL DEFAULT '0' COMMENT '在途数据',
`average_cost` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '移动加权成本',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`wp_id`)
) ENGINE=InnoDB COMMENT='商品库存表';
6. 物流公司信息表（shipping_info）
CREATE TABLE `shipping_info` (
`ship_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键id',
`ship_name` varchar(20) NOT NULL COMMENT '物流公司名称',
`ship_contact` varchar(20) NOT NULL COMMENT '物流公司联系人',
`telphone` varchar(20) NOT NULL COMMENT '物流公司联系电话',
`price` decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT '配送价格',
`modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
PRIMARY KEY (`ship_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='物流公司信息表';
四. DB规划
- 为以后数据库迁移提供方便
- 避免跨库操作，把经常一起关联查询的表放到一个DB中
- 为了方便识别表所在DB,在表名前增加库名前缀
用户数据库（mc_userdb）：用户信息表（customer_inf）、用户登录表（customer_login）、 用户级别表（customer_level_inf）、用户积分日志表（customer_point_log）、用户余额变动表（customer_balance_log）、 用户登录日志表（customer_login_log）
商品数据库（mc_productdb）：品牌信息表（product_brand_info）、商品分类表（product_category）、供应商信息表（product_supplier_info）、商品信息表（product_info）、商品图片信息表（product_pic_info）、商品评论表（product_comment）
订单数据库（mc_orderdb）：订单主表（order_master）、订单详情表（order_detail）、用户地址表（customer_addr）、仓库信息表（warehouse_info）、物流公司信息表（shipping_info）、 购物车表（order_cart）
