小型电子商城数据表设计2

1、根据上一篇的流程图设计出所需的数据表：

商铺表
分类表
地区表
商品表
商品详情表
用户表
订单表
商品评论表
购物车表
收藏表
用户收货地址
第三方平台与用户绑定表
用户信息表

2、SQL代码

CREATE DATABASE xeshop DEFAULT CHARACTER SET utf8;
 ####商铺表
CREATE TABLE IF NOT EXISTS xe_shop
(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
shopname VARCHAR(30) NOT NULL DEFAULT "" COMMENT "商铺名",
shopaddress VARCHAR(120) NOT NULL DEFAULT "" COMMENT "商铺地址",
metroaddress VARCHAR(120) NOT NULL DEFAULT "" COMMENT "商铺地铁地址",
shoptel CHAR(12) NOT NULL COMMENT "商铺电话",
shopcoord VARCHAR(60) NOT NULL DEFAULT "" COMMENT "商铺坐标"
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="商铺表";

分类表

CREATE TABLE IF NOT EXISTS xe_category
(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
cname CHAR(20) NOT NULL DEFAULT "" COMMENT "分类名",
pid SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "父分类的id",
keywords VARCHAR(120) NOT NULL DEFAULT "" COMMENT "分类关键字",
title VARCHAR(60) NOT NULL DEFAULT "" COMMENT "分类标题",
description VARCHAR(255) NOT NULL DEFAULT "" COMMENT "分类描述",
sort SMALLINT UNSIGNED NOT NULL COMMENT "排序",
display TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT "是否显示",
UNIQUE INDEX uniq_cname (cname),
UNIQUE INDEX uniq_pid (pid),
UNIQUE INDEX uniq_sort (sort),
UNIQUE INDEX uniq_display (display)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT "分类表";

地区表

CREATE TABLE IF NOT EXISTS xe_location
(
id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
lname CHAR(20) NOT NULL DEFAULT "" COMMENT "地区名",
pid SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "父地区的id",
sort SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT "排序",
display TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT "是否显示",
UNIQUE INDEX uniq_lname (lname),
UNIQUE INDEX uniq_pid (pid),
UNIQUE INDEX uniq_sort (sort),
UNIQUE INDEX uniq_display (display)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="地区表";

商品表

CREATE TABLE IF NOT EXISTS xe_goods
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "商品主键",
shop_id SMALLINT UNSIGNED NOT NULL COMMENT "外键-商铺id",
cate_id SMALLINT UNSIGNED NOT NULL COMMENT "外键-分类id",
loca_id SMALLINT UNSIGNED NOT NULL COMMENT "外键-地区id",
main_title VARCHAR(30) NOT NULL DEFAULT "" COMMENT "商品主标题",
sub_title VARCHAR(255) NOT NULL DEFAULT "" COMMENT "商品副标题",
price DECIMAL(7,1) NOT NULL DEFAULT 0 COMMENT "商品价格",
old_price DECIMAL(7,1) NOT NULL DEFAULT 0 COMMENT "原价",
buy SMALLINT NOT NULL DEFAULT 0 COMMENT "购买人数",
goods_img VARCHAR(60) NOT NULL COMMENT "商品图",
INDEX idx_shop_id (shop_id),
INDEX idx_cate_id (cate_id),
INDEX idx_loca_id (loca_id)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="商品表";

商品详情表

CREATE TABLE IF NOT EXISTS xe_goods_detail
(
goods_id INT UNSIGNED NOT NULL COMMENT "外键-商品id",
detail TEXT NOT NULL COMMENT "商品描述",
goods_server TINYINT UNSIGNED NOT NULL COMMENT "",
INDEX idx_goods_id (goods_id)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="商品详情表";

用户表

CREATE TABLE IF NOT EXISTS xe_user
(
id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
email VARCHAR(32) NOT NULL COMMENT "邮箱",
username VARCHAR(20) NOT NULL COMMENT "用户名",
password CHAR(32) NOT NULL COMMENT "密码",
phone CHAR(11) NOT NULL COMMENT "手机号",
UNIQUE uniq_email (email),
UNIQUE uniq_username (username)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="用户表";

订单表

CREATE TABLE IF NOT EXISTS xe_order
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "订单id",
user_id INT UNSIGNED NOT NULL COMMENT "外键-用户id",
goods_id INT UNSIGNED NOT NULL COMMENT "外键-商品id",
goods_num SMALLINT UNSIGNED NOT NULL COMMENT "商品数量",
total_money DECIMAL(7,1) NOT NULL COMMENT "总金额",
INDEX idx_goods_id (goods_id),
INDEX idx_user_id (user_id)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="订单表";

商品评论表

CREATE TABLE IF NOT EXISTS xe_comment
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "评论id",
user_id INT UNSIGNED NOT NULL COMMENT "外键-用户id",
goods_id INT UNSIGNED NOT NULL COMMENT "外键-商品id",
time INT NOT NULL COMMENT "评论时间",
content VARCHAR(255) NOT NULL COMMENT "评论内容",
INDEX idx_user_id (user_id),
INDEX idx_goods_id (goods_id)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="商品评论表";

购物车表

CREATE TABLE IF NOT EXISTS xe_cart
(
user_id INT UNSIGNED NOT NULL COMMENT "外键-用户id",
goods_id INT UNSIGNED NOT NULL COMMENT "外键-商品id",
goods_num SMALLINT NOT NULL COMMENT "商品数",
INDEX idx_user_id (user_id),
INDEX idx_goods_id (goods_id)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="购物车表";

收藏表

CREATE TABLE IF NOT EXISTS xe_collect
(
user_id INT UNSIGNED NOT NULL COMMENT "外键-用户id",
goods_id INT UNSIGNED NOT NULL COMMENT "外键-商品id",
INDEX idx_user_id (user_id),
INDEX idx_goods_id (goods_id)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="收藏表";

用户收货地址表

CREATE TABLE IF NOT EXISTS xe_user_address
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
user_id INT UNSIGNED NOT NULL COMMENT "外键-用户id",
consignee VARCHAR(20) NOT NULL COMMENT "收货人",
province VARCHAR(20) NOT NULL COMMENT "省",
city VARCHAR(20) NOT NULL COMMENT "市",
county VARCHAR(20) NOT NULL COMMENT "县",
street VARCHAR(120) NOT NULL COMMENT "街道地址",
tel CHAR(12) NOT NULL COMMENT "电话",
postcode CHAR(10) NOT NULL COMMENT "邮编地址",
INDEX idx_user_id (user_id)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="用户收货地址，可以有多个";

第三方平台与用户绑定表

CREATE TABLE IF NOT EXISTS xe_open_bind
(
user_id INT UNSIGNED NOT NULL COMMENT "外键-用户ID",
openid CHAR(64) NOT NULL COMMENT "开放平台的id",
open_distinguish TINYINT NOT NULL COMMENT "开放平台区别",
open_name VARCHAR(20) NOT NULL COMMENT "开放平台名称",
INDEX idx_user_id (user_id)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="第三方平台与用户绑定表";

用户信息表

CREATE TABLE IF NOT EXISTS xe_userinfo
(
user_id INT UNSIGNED NOT NULL COMMENT "外键-用户id",
balance DECIMAL(7,1) NOT NULL COMMENT "余额",
integral SMALLINT NOT NULL COMMENT "积分",
INDEX idx_user_id (user_id)
)ENGINE=innodb DEFAULT CHARSET=utf8 COMMENT="用户信息表，保存用户余额，积分等数据";