（五）设计商品库存

最新推荐文章于 2025-06-14 13:58:20 发布

原创最新推荐文章于 2025-06-14 13:58:20 发布 · 531 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

mysql

本文详细介绍了数据库设计中省份、城市、仓库及零售店等模块的表结构创建，包括省份表、城市表、仓库表和零售店表及其关联的商品库存表。

省份表和城市表

--省份表
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 = '零售店商品库存表';