如何设计商品的库存?

可能有读者会问，直接设置到商品表中不就好了，加上一个字段就OK了，真的这么简单吗？

我们分析一下，我们是新零售平台，也就是说会有多个店铺，每个店铺的库存都一样吗？

此时我们抽象出新的两张表，零售店与仓库表，两者是多对多关系。有读者说多对的关系，新建一个中间表关联零售店与仓库表。

想象一下新的场景，如果仓库没有库存我们会不会从别的地方调货，因此不能单纯的使用一个中间表定死了。

我们来看一下零售店、仓库与商品表的关系：

省份和城市表

有一个前提条件是创建省份和城市表，无论是零售店还是仓库都有城市信息，通过字符检索速度相对而言太慢了，尤其是在高并发的场景下还需要计算邮费等等，因此需要抽象出省份和城市表。

省份表结构和数据

CREATE TABLE `t_province`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `province` varchar(100)  NOT NULL COMMENT '省份',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `unq_province`(`province`) USING BTREE
) COMMENT = '省份表';

省份数据

 
 INSERT INTO t_province VALUES (1, '北京', 0);
 INSERT INTO t_province VALUES (2, '上海', 0);
 INSERT INTO t_province VALUES (3, '天津', 0);
 INSERT INTO t_province VALUES (4, '重庆', 0);
 INSERT INTO t_province VALUES (5, '辽宁', 0);
 INSERT INTO t_province VALUES (6, '吉林', 0);
 INSERT INTO t_province VALUES (7, '黑龙江', 0);
 INSERT INTO t_province VALUES (8, '山东', 0);
 INSERT INTO t_province VALUES (9, '江苏', 0);
 INSERT INTO t_province VALUES (10, '浙江', 0);
 INSERT INTO t_province VALUES (11, '安徽', 0);
 INSERT INTO t_province VALUES (12, '福建', 0);
 INSERT INTO t_province VALUES (13, '江西', 0);
 INSERT INTO t_province VALUES (14, '广东', 0);
 INSERT INTO t_province VALUES (15, '广西', 0);
 INSERT INTO t_province VALUES (16, '海南', 0);
 INSERT INTO t_province VALUES (17, '河南', 0);
 INSERT INTO t_province VALUES (18, '湖南', 0);
 INSERT INTO t_province VALUES (19, '湖北', 0);
 INSERT INTO t_province VALUES (20, '河北', 0);
 INSERT INTO t_province VALUES (21, '山西', 0);
 INSERT INTO t_province VALUES (22, '内蒙古', 0);
 INSERT INTO t_province VALUES (23, '宁夏', 0);
 INSERT INTO t_province VALUES (24, '青海', 0);
 INSERT INTO t_province VALUES (25, '陕西', 0);
 INSERT INTO t_province VALUES (26, '甘肃', 0);
 INSERT INTO t_province VALUES (27, '新疆', 0);
 INSERT INTO t_province VALUES (28, '四川', 0);
 INSERT INTO t_province VALUES (29, '贵州', 0);
 INSERT INTO t_province VALUES (30, '云南', 0);
 INSERT INTO t_province VALUES (31, '西藏', 0);
 INSERT INTO t_province VALUES (32, '香港', 0);
 INSERT INTO t_province VALUES (33, '澳门', 0);
 INSERT INTO t_province VALUES (34, '台湾', 0);

城市表结构和数据，城市表数据 (opens new window)

CREATE TABLE `t_city` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `city` varchar(200) NOT NULL COMMENT '城市',
  `province_id` int(10) unsigned NOT NULL COMMENT '省份ID',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `uni_city` (`city`(191))
) ENGINE=InnoDB AUTO_INCREMENT=343 DEFAULT CHARSET=utf8mb4 COMMENT='城市表';

仓库表

CREATE TABLE `t_warehouse`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `city_id` int(10) UNSIGNED NOT NULL COMMENT '城市ID',
  `address` varchar(200) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '地址',
  `tel` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话',
  `lng` decimal(15, 10) NULL DEFAULT NULL COMMENT '纬度',
  `lat` decimal(15, 10) NULL DEFAULT NULL COMMENT '经度',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_city_id`(`city_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '仓库表' ROW_FORMAT = Dynamic;

仓库商品关联表

CREATE TABLE `t_warehouse_sku`  (
  `warehouse_id` int(10) UNSIGNED NOT NULL COMMENT '仓库ID',
  `sku_id` int(10) UNSIGNED NOT NULL COMMENT '商品ID',
  `num` int(10) UNSIGNED NOT NULL COMMENT '库存数量',
  `unit` varchar(20)  NOT NULL COMMENT '库存单位',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`warehouse_id`, `sku_id`) USING BTREE
) COMMENT = '仓库商品库存表';

零售店表

CREATE TABLE `t_shop`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `city_id` int(10) UNSIGNED NOT NULL COMMENT '城市ID',
  `address` varchar(200) NOT NULL COMMENT '地址',
  `tel` varchar(20) NOT NULL COMMENT '电话',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_city_id`(`city_id`) USING BTREE
) COMMENT = '零售店表' ;

零售店与商品关联表

CREATE TABLE `t_shop_sku`  (
  `shop_id` int(10) UNSIGNED NOT NULL COMMENT '零售店ID',
  `sku_id` int(10) UNSIGNED NOT NULL COMMENT '商品ID',
  `num` int(10) UNSIGNED NOT NULL COMMENT '库存数量',
  `unit` varchar(20) NOT NULL COMMENT '库存单位',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除',
  PRIMARY KEY (`shop_id`, `sku_id`) USING BTREE
) COMMENT = '零售店商品库存表';