（五）设计商品库存

省份表和城市表

--省份表
CREATE TABLE `t_province`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `province` varchar(200) NOT NULL COMMENT '省份',
  PRIMARY KEY (`id`),
  UNIQUE INDEX `unq_province`(`province`)
) COMMENT = '省份表';


--城市表
CREATE TABLE `t_city`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `city` varchar(200) NOT NULL COMMENT '城市',
  `province_id` int UNSIGNED NOT NULL COMMENT '省份ID',
  PRIMARY KEY (`id`),
) COMMENT = '城市表';

 

仓库表 与 仓库商品库库存表

 

CREATE TABLE `t_warehouse`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `city_id` int UNSIGNED NOT NULL COMMENT '城市ID',
  `adress` varchar(200) NOT NULL COMMENT '地址',
  `tel` varchar(20) NOT NULL COMMENT '电话',
  PRIMARY KEY (`id`),
  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 NOT NULL COMMENT '库存数量',
  `unit` varchar(20) NOT NULL COMMENT '库存单位',
  PRIMARY KEY (`warehouse_id`, `sku_id`)
) COMMENT = '仓库商品库存表';

 

零售店表与零售店商品库存表

 

 

CREATE TABLE `t_shop`  (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `city_id` int UNSIGNED NOT NULL COMMENT '城市ID',
  `adress` varchar(200) NOT NULL COMMENT '地址',
  `tel` varchar(20) NOT NULL COMMENT '电话',
  PRIMARY KEY (`id`),
  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 NOT NULL COMMENT '库存数量',
  `unit` varchar(20) NOT NULL COMMENT '库存单位',
  PRIMARY KEY (`shop_id`, `sku_id`)
) COMMENT = '零售店商品库存表';