新零售-商品库存相关数据表设计

-- 全国省市县地区表
CREATE TABLE `core_region` (
  `national_code` mediumint(8) NOT NULL COMMENT '地区码',
  `region_name` varchar(64) NOT NULL COMMENT '地区名称',
  `parent_id` mediumint(8) NOT NULL DEFAULT '0' COMMENT '该地区父节点的地区码',
  PRIMARY KEY (`national_code`) USING BTREE,
  KEY `parent_id` (`parent_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='全国省市县地区表';

-- 仓库表
DROP TABLE IF EXISTS `warehouse`;
CREATE TABLE `warehouse`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '仓库主键',
  `national_code` int(10) UNSIGNED NOT NULL COMMENT '所在城市CODE，关联core_region',
  `address` varchar(200) NOT NULL COMMENT '仓库地址',
  `tel` varchar(20) 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 '是否已被删除 0-未被删除 1-已被删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_city_code`(`national_code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '仓库表';

-- 商品仓库库存表
DROP TABLE IF EXISTS `warehouse_sku_inventory`;
CREATE TABLE `warehouse_sku_inventory`  (
  `warehouse_id` int(10) UNSIGNED NOT NULL COMMENT '仓库ID，关联product_warehouse',
  `sku_sn` int(10) UNSIGNED NOT NULL COMMENT '商品唯一编号，关联product_sku',
  `inventory` int(10) UNSIGNED NOT NULL COMMENT '库存数量',
  `inventory_unit` varchar(20) NOT NULL COMMENT '库存单位',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已被删除 0-未被删除 1-已被删除',
  PRIMARY KEY (`warehouse_id`, `sku_sn`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '商品仓库库存表';

-- 零售店表
DROP TABLE IF EXISTS `retail_store`;
CREATE TABLE `retail_store`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '零售店ID',
  `national_code` int(10) UNSIGNED NOT NULL COMMENT '零售店所在城市CODE',
  `address` varchar(200) NOT NULL COMMENT '地址',
  `tel` varchar(20) NOT NULL COMMENT '电话',
  `is_deleted` tinyint(1) NOT NULL DEFAULT 0 COMMENT '是否已被删除 0-未被删除 1-已被删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_city_code`(`national_code`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '零售店表';

-- 零售店商品库存表
DROP TABLE IF EXISTS `retail_store_sku_inventory`;
CREATE TABLE `retail_store_sku_inventory`  (
  `store_id` int(10) UNSIGNED NOT NULL COMMENT '零售店ID',
  `sku_sn` int(10) UNSIGNED NOT NULL COMMENT '商品唯一编号，关联product_sku',
  `inventory` int(10) UNSIGNED NOT NULL COMMENT '库存数量',
  `inventory_unit` varchar(20) NOT NULL COMMENT '库存单位',
  PRIMARY KEY (`store_id`, `sku_sn`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COMMENT = '零售店商品库存表';
打赏赞