关于QuickStore这个项目的重构（2）- 数据库建表文件

Colin草率地做慢慢地改
3 阅读9分钟

好吧今天还得再发表一两篇文章。

数据库用的是Postgresql，使用的管理软件是pgAdmin。

几张表的构成如下。尽量简单点吧，反正仓库的流程本身也挺简单的。

office给出纸质order -> 仓库根据order把东西装车发给客户/客户自己开车过来，至少两人一组把东西给他搬车上 -> 出库完成

集装箱到货 -> 用叉车叉到仓库储存区 -> 对照office给出的入库单，清点数量，无误 -> 入库完成

OK，下面是建表的sql文档，以及部分测试数据。

建表文档：

📚 QuickStore Database Tables

QuickStore Database Schema Overview

This document outlines the structure and meaning of all database tables used in the QuickStore WMS.

users （系统用户）

字段名类型描述
idSERIAL (PK)用户 ID，自增主键
usernameTEXT登录名（唯一）
password_hashTEXT加密后的密码
full_nameTEXT用户姓名
roleTEXT角色：如 'admin'、'office'、'warehouse'
created_atTIMESTAMP创建时间

products （商品信息）

字段名类型描述
idSERIAL (PK)产品 ID，自增主键
nameTEXT产品名称（如 角铝、圆管 等）
specificationTEXT规格（如 20x20mm、2mm 厚）
unitTEXT单位（如 米、条、根）
priceNUMERIC(10, 2)单价（如 12.50）
created_atTIMESTAMP添加时间

product_locations （商品位置信息）

字段名类型描述
idSERIAL (PK)位置 ID
product_idINTEGER (FK → products.id)所属商品 ID
location_codeTEXT货位编码（比如A1,C1,S1等）
quantityINTEGER当前位置货物数量。当is_default为true时可以为0
is_defaultBOOLEAN是否为商品默认位置。可以有多个true
updated_atTIMESTAMP最后修改时间

customers （客户信息）

字段名类型描述
idSERIAL (PK)客户 ID
nameTEXT客户名称（公司名或个人名）
contact_numberTEXT电话号码
emailTEXT邮箱（可选）
addressTEXT地址
created_atTIMESTAMP添加时间

inbounds （入库记录）

字段名类型描述
idSERIAL (PK)入库记录 ID
shipment_codeTEXT货柜编号或批次号（可选）
arrival_dateDATE到货日期
inbound_dateDATE入库日期
created_byINTEGER (FK → users.id)哪个用户录入的
remarkTEXT备注（手动调整库存等）
statusTEXT状态（如：办公室：draft, 仓库：pending, completed等）
created_atTIMESTAMP入库登记时间

inbound_items （入库商品）

字段名类型描述
idSERIAL (PK)入库商品 ID
inbound_idINTEGER (FK → inbounds.id)入库记录 ID
product_idINTEGER (FK → products.id)产品id
quantityINTEGER入库数量
remarkTEXT备注（手动调整库存等）
created_atTIMESTAMP入库登记时间

orders （客户下的订单/出库记录）

字段名类型描述
idSERIAL (PK)订单 ID
customer_idINTEGER (FK → customers.id)所属客户
order_dateTIMESTAMP下单时间
created_byINTEGER (FK → users.id)哪位员工录入的
is_deliveryBOOLEAN是否需要司机配送
remarkTEXT备注（如客户退货等）
statusTEXT状态（如：pending, fulfilled, canceled）
created_atTIMESTAMP本记录创建时间

order_items （订单出库的商品明细）

字段名类型描述
idSERIAL (PK)明细 ID
order_idINTEGER (FK → orders.id)所属订单
product_idINTEGER (FK → products.id)产品
quantityINTEGER数量
unit_priceNUMERIC(10, 2)下单时的单价（防止价格变动影响历史）
created_atTIMESTAMP本记录创建时间

order_operators （订单出库的协同人员）

字段名类型描述
idSERIAL (PK)明细 ID
order_idINTEGER (FK → orders.id)所属订单
user_idINTEGER (FK → users.id)出库的仓库人员是谁（因为一般是两人或两人以上完成一张订单）
created_atTIMESTAMP本记录创建时间

各表之间的一对多关系：

主表子表关系类型
customers → orders1 → ∞
inbounds → inbound_items1 → ∞
orders → order_items1 → ∞
orders → order_operators1 → ∞
products → order_items1 → ∞
products → product_locations1 → ∞
inbounds → inbound_items1 → ∞
products → inbound_items1 → ∞
users → orders, inbounds1 → ∞

-- 数据库表的建表语句

-- ==========================================
-- 1. users （系统用户表）
-- ==========================================
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password_hash TEXT NOT NULL,
    full_name TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'warehouse',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE users IS '系统用户表';
COMMENT ON COLUMN users.id IS '用户 ID，自增主键';
COMMENT ON COLUMN users.username IS '登录名（唯一）';
COMMENT ON COLUMN users.password_hash IS '加密后的密码';
COMMENT ON COLUMN users.full_name IS '用户姓名';
COMMENT ON COLUMN users.role IS '角色：如 admin、office、warehouse';
COMMENT ON COLUMN users.created_at IS '创建时间';


-- ==========================================
-- 2. products （商品信息表）
-- ==========================================
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    specification TEXT,
    unit TEXT,
    price NUMERIC(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE products IS '商品信息表';
COMMENT ON COLUMN products.id IS '产品 ID，自增主键';
COMMENT ON COLUMN products.name IS '产品名称（如 角铝、圆管 等）';
COMMENT ON COLUMN products.specification IS '规格（如 20x20mm、2mm 厚）';
COMMENT ON COLUMN products.unit IS '单位（如 米、条、根）';
COMMENT ON COLUMN products.price IS '当前标准单价（如 12.50）';
COMMENT ON COLUMN products.created_at IS '添加时间';

-- ==========================================
-- 3. product_locations （商品库存分布与默认货位表）
-- ==========================================
CREATE TABLE product_locations (
    id SERIAL PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    location_code TEXT NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0 CHECK (quantity >= 0),
    is_default BOOLEAN NOT NULL DEFAULT FALSE,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- 联合唯一约束：同一个商品在同一个货位只能有一条记录
    CONSTRAINT unique_product_location UNIQUE (product_id, location_code)
);

COMMENT ON TABLE product_locations IS '商品库存分布与默认货位表（支持一物多位与习惯性留白）';
COMMENT ON COLUMN product_locations.id IS '自增主键';
COMMENT ON COLUMN product_locations.product_id IS '关联的产品 ID';
COMMENT ON COLUMN product_locations.location_code IS '货位编码（如 A1, C1, S1）';
COMMENT ON COLUMN product_locations.quantity IS '该特定货位上的商品当前库存数量（默认货位允许为0）';
COMMENT ON COLUMN product_locations.is_default IS 'TRUE: 设为该商品的默认/常用位置（即使库存为0也不释放）';
COMMENT ON COLUMN product_locations.updated_at IS '最后一次位置库存变动时间';

-- ==========================================
-- 4. customers （客户信息表）
-- ==========================================
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    contact_number TEXT,
    email TEXT,
    address TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE customers IS '客户信息表';
COMMENT ON COLUMN customers.id IS '客户 ID，自增主键';
COMMENT ON COLUMN customers.name IS '客户名称（公司名或个人名）';
COMMENT ON COLUMN customers.contact_number IS '电话号码';
COMMENT ON COLUMN customers.email IS '邮箱（可选）';
COMMENT ON COLUMN customers.address IS '地址';
COMMENT ON COLUMN customers.created_at IS '添加时间';


-- ==========================================
-- 5. inbounds （入库单主表）
-- ==========================================
CREATE TABLE inbounds (
    id SERIAL PRIMARY KEY,
    shipment_code TEXT,
    arrival_date DATE,
    inbound_date DATE,
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    remark TEXT,
    status TEXT NOT NULL DEFAULT 'draft',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE inbounds IS '入库记录主表';
COMMENT ON COLUMN inbounds.id IS '入库记录 ID，自增主键';
COMMENT ON COLUMN inbounds.shipment_code IS '货柜编号或批次号（可选）';
COMMENT ON COLUMN inbounds.arrival_date IS '集装箱到货日期';
COMMENT ON COLUMN inbounds.inbound_date IS '正式点完核对入库的日期';
COMMENT ON COLUMN inbounds.created_by IS '哪位员工录入的单据';
COMMENT ON COLUMN inbounds.remark IS '备注（手动调整库存原因等）';
COMMENT ON COLUMN inbounds.status IS '状态：draft(办公室草稿), pending(清点中), completed(已入库)';
COMMENT ON COLUMN inbounds.created_at IS '入库登记时间';


-- ==========================================
-- 6. inbound_items （入库商品明细表）
-- ==========================================
CREATE TABLE inbound_items (
    id SERIAL PRIMARY KEY,
    inbound_id INTEGER NOT NULL REFERENCES inbounds(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    remark TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE inbound_items IS '入库商品明细表';
COMMENT ON COLUMN inbound_items.id IS '明细 ID，自增主键';
COMMENT ON COLUMN inbound_items.inbound_id IS '关联的入库主单 ID';
COMMENT ON COLUMN inbound_items.product_id IS '关联的产品 ID';
COMMENT ON COLUMN inbound_items.quantity IS '本次实际入库数量';
COMMENT ON COLUMN inbound_items.remark IS '单项备注';
COMMENT ON COLUMN inbound_items.created_at IS '本条记录创建时间';


-- ==========================================
-- 7. orders （客户订单/出库记录主表）
-- ==========================================
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    order_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    is_delivery BOOLEAN NOT NULL DEFAULT FALSE,
    remark TEXT,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE orders IS '客户下的订单/出库记录主表';
COMMENT ON COLUMN orders.id IS '订单 ID，自增主键';
COMMENT ON COLUMN orders.customer_id IS '所属客户 ID';
COMMENT ON COLUMN orders.order_date IS '客户下单时间';
COMMENT ON COLUMN orders.created_by IS '哪位办公室员工录入的';
COMMENT ON COLUMN orders.is_delivery IS 'TRUE: 司机开 UTE 配送 / FALSE: 客户自提';
COMMENT ON COLUMN orders.remark IS '备注（退换货说明、配送要求等）';
COMMENT ON COLUMN orders.status IS '订单状态：pending(待配货), fulfilled(已出库), canceled(已取消)';
COMMENT ON COLUMN orders.created_at IS '本记录创建时间';


-- ==========================================
-- 8. order_items （订单商品明细表）
-- ==========================================
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE order_items IS '订单中的商品明细表';
COMMENT ON COLUMN order_items.id IS '明细 ID，自增主键';
COMMENT ON COLUMN order_items.order_id IS '所属订单 ID';
COMMENT ON COLUMN order_items.product_id IS '关联的产品 ID';
COMMENT ON COLUMN order_items.quantity IS '购买数量';
COMMENT ON COLUMN order_items.unit_price IS '下单时的实际成交单价（防止商品调价影响历史对账）';
COMMENT ON COLUMN order_items.created_at IS '本记录创建时间';


-- ==========================================
-- 9. order_operators （订单出库协同人员中间表）
-- ==========================================
CREATE TABLE order_operators (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE order_operators IS '订单出库协同人员中间表';
COMMENT ON COLUMN order_operators.id IS '记录 ID，自增主键';
COMMENT ON COLUMN order_operators.order_id IS '所属订单 ID';
COMMENT ON COLUMN order_operators.user_id IS '参与出库的仓库人员 ID';
COMMENT ON COLUMN order_operators.created_at IS '协同出库确认时间';

测试数据：

-- 1. 插入初始化用户 (密码暂用明文替代，后续开发需改用 Bcrypt/Argon2 密码哈希)
INSERT INTO users (username, password_hash, full_name, role) VALUES 
('admin_tony', 'hashed_password_123', 'Tony (老板)', 'admin'),
('office_lucy', 'hashed_password_456', 'Lucy (文员)', 'office'),
('wh_jack', 'hashed_password_789', 'Jack (仓库小哥)', 'warehouse'),
('wh_sam', 'hashed_password_abc', 'Sam (仓库小哥)', 'warehouse');

-- 2. 插入部分铝材产品
INSERT INTO products (name, specification, unit, price) VALUES 
('等边角铝', '20x20mm, 2mm厚, 6米长', '根', 15.50),
('方管', '50x50mm, 3mm厚, 6米长', '根', 32.00),
('槽铝', '40x20mm, 2mm厚, 6米长', '根', 22.00);

-- 3. 初始化货位数字化：为这些产品绑定默认货位 (初始数量为0，落实习惯性留白)
INSERT INTO product_locations (product_id, location_code, quantity, is_default) VALUES 
(1, 'A1', 0, TRUE), -- 角铝默认放A1
(1, 'S1', 0, TRUE), -- 角铝大宗囤货放S1
(2, 'B3', 0, TRUE), -- 方管默认放B3
(3, 'A-FLOOR', 0, TRUE); -- 槽铝默认堆在地板区A

-- 4. 插入几个本地建筑商客户
INSERT INTO customers (name, contact_number, email, address) VALUES 
('ABC Alum Windows Pty Ltd', '0412345678', 'info@abcwindows.com.au', '123 Springvale Rd, Glen Waverley VIC 3150'),
('Melbourne Building Supplies', '0398765432', 'order@melbbuild.com.au', '45 Dandenong Rd, Dandenong VIC 3175');

可以写在简历上的东西：

好吧这是AI帮我生成的，但是到时候写简历也可以参考下：

QuickStore Warehouse Management System (Personal Project)

-   Built a full-stack Warehouse Management System using React, Spring Boot, PostgreSQL and JWT authentication.

-   Designed and implemented a PostgreSQL database schema consisting of 9 relational tables supporting inventory management, inbound shipments, customer orders and warehouse operations.

-   Developed normalized database relationships using primary keys, foreign keys, constraints and cascading rules to ensure data integrity.

-   Implemented inventory location management supporting multi-location stock allocation and warehouse tracking.

-   Created RESTful APIs for user authentication, product management, customer management and warehouse workflows.

Translated real-world warehouse business requirements into a normalized PostgreSQL relational database schema consisting of 9 interconnected tables.
avatar
文章
阅读
粉丝