ER Diagram
全部模板

库存管理数据库表结构

一个支持多仓库的库存模型。商品在多个仓库中存储，通过采购订单从供应商进货，库存水平按"商品-仓库"对进行跟踪。它可从单一库房扩展到多地点运营。

在编辑器中打开 → 6 张表 · MySQL DDL

此结构中的表

suppliersproductswarehousesstock_levelspurchase_orderspurchase_order_items

表结构（SQL）

MySQL 方言的 DDL。在编辑器中打开可获得可编辑的可视化 ER 图，或直接将 SQL 复制到你的项目中。

CREATE TABLE suppliers (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  contact_email VARCHAR(254),
  phone VARCHAR(32)
);

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(64) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  unit_cost DECIMAL(10,2) NOT NULL,
  supplier_id INT,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE TABLE warehouses (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(120) NOT NULL,
  location VARCHAR(200)
);

CREATE TABLE stock_levels (
  product_id INT NOT NULL,
  warehouse_id INT NOT NULL,
  quantity INT NOT NULL DEFAULT 0,
  reorder_point INT NOT NULL DEFAULT 0,
  PRIMARY KEY (product_id, warehouse_id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
);

CREATE TABLE purchase_orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  supplier_id INT NOT NULL,
  warehouse_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  ordered_at DATETIME NOT NULL,
  FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
  FOREIGN KEY (warehouse_id) REFERENCES warehouses(id)
);

CREATE TABLE purchase_order_items (
  id INT PRIMARY KEY AUTO_INCREMENT,
  purchase_order_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  unit_cost DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

更多模板