-- 全国省市县地区表
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 = '零售店商品库存表';