新零售数据库设计 笔记（三）

1.如何设计商品库存

• 库存能定义到商品表吗？ 系统没有分店场景的话，可以把库存定义到商品表里

• 零售店与仓库，还有商品的关系

零售店与商品是多对多关系，商品又与仓库是多对多关系，而零售店与仓库虽然是多对多关系，但不能创建关联表，创建了关联表零售店与仓库就形成约束关系，这样零售店就不能自由切换仓库，反之仓库也是。

• 创建省份和城市表

CREATE TABLE t_province(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  province VARCHAR(200) NOT NULL COMMENT "省份",
  UNIQUE unq_province(province)
)COMMENT="省份表"

CREATE TABLE t_province(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  city VARCHAR(200) NOT NULL COMMENT "城市",
  province_id INT UNSIGNED NOT NULL COMMENT "省份ID",
)COMMENT="城市表"

• 创建仓库和仓库商品关联表

CREATE TABLE t_warehouse(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  city_id INT UNSIGNED NOT NULL COMMENT "城市ID",
  address VARCHAR(200) NOT NULL COMMENT "地址",
  tel VARCHAR(20) NOT NULL COMMENT "电话",
  INDEX idx_city_id(city_id)
)COMMENT="仓库表"

CREATE TABLE t_warehouse_sku(
  warehouse_id INT UNSIGNED COMMENT "仓库ID",
  sku_id INT UNSIGNED COMMENT "商品ID",
  num INT UNSIGNED COMMENT "库存数量",
  unit VARCHAR(20) NOT NULL COMMENT "电话",
  PRIMARY KEY (warehouse_id, sku_id)
)COMMENT="仓库商品表"

• 创建零售店和零售店商品关联表

CREATE TABLE t_shop(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  city_id INT UNSIGNED NOT NULL COMMENT "城市ID",
  address VARCHAR(200) NOT NULL COMMENT "地址",
  tel VARCHAR(20) NOT NULL COMMENT "库存单位",
  INDEX idx_city_id(city_id)
)COMMENT="零售店表"

CREATE TABLE t_shop_sku(
  shop_id INT UNSIGNED COMMENT "零售店ID",
  sku_id INT UNSIGNED COMMENT "商品ID",
  num INT UNSIGNED COMMENT "库存数量",
  unit VARCHAR(20) NOT NULL COMMENT "库存单位",
  PRIMARY KEY (warehouse_id, sku_id)
)COMMENT="零售店商品表"

2.如何设计客户相关表

• 创建会员登记表，客户表和收获地址表

CREATE TABLE t_level(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  `level` VARCHAR(20) NOT NULL COMMENT "会员等级",
  discount DECIMAL(10, 2) UNSIGNED NOT NULL COMMENT "折扣",
)COMMENT="会员登记表"

CREATE TABLE t_customer(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  username VARCHAR(200) NOT NULL COMMENT "用户名",
  `password` VARCHAR(2000) NOT NULL COMMENT "密码",
  wechat VARCHAR(200) COMMENT "微信号",
  tel CHAR(11) COMMENT "手机号",
  level_id INT UNSIGNED COMMENT "会员等级",
  create_time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT "添加时间",
  last_update_time TIMESTAMP NOT NULL DEFAULT NOW() COMMENT "最后修改时间",
  INDEX idx_username(username),
  UNIQUE unq_username(username)
)COMMENT="零售店商品表"

CREATE TABLE t_customer_address(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  customer_id INT UNSIGNED NOT NULL COMMENT "客户ID",
  `name` VARCHAR(200) NOT NULL COMMENT "收货人姓名",
  tel CHAR(11) COMMENT "手机号",
  address VARCHAR(200) NOT NULL COMMENT "收货地址",
  prime BOOLEAN NOT NULL COMMENT "是否为缺省收货地址",
  INDEX idx_username(customer_id)
)COMMENT="收获地址表"

3.如何设计购物券相关表

CREATE TABLE t_voucher(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  deno DECIMAL(10,2) UNSIGNED NOT NULL COMMENT "面值",
  `condition` DECIMAL(10,2) UNSIGNED NOT NULL COMMENT "订单满多少钱可以使用",
  start_date DATE COMMENT "起始日期",
  end_date DATE COMMENT "截止日期",
  max_num INT COMMENT "代金券发放最大数量",
)COMMENT="购物券表"

CREATE TABLE t_voucher_customer(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  voucher_id INT UNSIGNED NOT NULL COMMENT "购物券ID",
  customer_id INT UNSIGNED NOT NULL COMMENT "客户ID"
)COMMENT="客户关联购物券表"

4.如何设计订单相关表

• 一张订单可以包含多个商品记录，但不建议用JSON存储这些商品信息，因为不能检索数据，而JSON字段只适合存储数据

CREATE TABLE t_order(
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT "主键",
  `code` VARCHAR(200) NOT NULL COMMENT "流水号",
  type TINYINT UNSIGNED NOT NULL COMMENT "订单类型：1.实体销售 2.网络销售",
  shop_id INT UNSIGNED NOT NULL COMMENT "零售店ID",
  customer_id INT UNSIGNED NOT NULL COMMENT "客户ID",
  amount 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.已付款 3.已发货 4.已签收"
  postage DECIMAL(10,2) UNSIGNED NOT NULL COMMENT "邮费",
  weight INT UNSIGNED COMMENT "重量（克）",
  voucher_id INT UNSIGNED COMMENT "购物券ID",
  create_time TIMESTAMP NOT NULL COMMENT "创建时间",
  INDEX idx_code(`code`),
  INDEX idx_customer_id(customer_id),
  INDEX idx_status(`status`),
  INDEX idx_create_time(create_time),
  INDEX idx_type(type),
  INDEX idx_shop_id(shop_id),
  UNIQUE unq_code(`code`)
)COMMENT="订单表"

CREATE TABLE t_order_detail(
  order_id INT UNSIGNED NOT NULL COMMENT "订单ID",
  sku_id INT UNSIGNED NOT NULL COMMENT "商品ID",
  price DECIMAL(10,2) UNSIGNED NOT NULL COMMENT "原价",
  actual_price DECIMAL(10,2) UNSIGNED NOT NULL COMMENT "实际购买价",
  num INT UNSIGNED NOT NULL COMMENT "购买数量",
  PRIMARY KEY(order_id, sku_id)
)COMMENT="订单详情表"