工厂物资管理数据库系统：从Excel台账到可追溯库存的落地路径

简介这份《工厂物资管理数据库系统》设计报告面向计算机与信息管理相关专业的学生及数据库初学者帮助其完成从需求分析到数据库落地的完整课程设计或实训项目。资源包内含1个doc文档压缩包约224KB以Word报告形式呈现便于直接阅读、参考与二次编辑。报告围绕工厂物资采购、入库、领用、盘点与报废等业务展开依次讲解设计任务说明、需求分析、概念模型设计中的E-R图与实体联系描述、逻辑模型设计、物理模型设计中的数据库选型、数据表描述、触发器、视图与存储过程以及数据库实施阶段的建库、备份、建表、索引与修改语句等内容并附有总结与参考文献。目前已有397人学习下载适合需要掌握数据库建模全流程、撰写规范设计文档或准备课程答辩的读者参考借鉴。1. 工厂物资管理数据库系统从 Excel 台账到可追溯库存的落地路径很多工厂的物资管理起点都是一张共享 Excel入库加一行、出库减一行月底对不上就翻聊天记录。问题不在人懒而在于 Excel 没有事务、没有约束、没有并发控制三个人同时改一张表最后谁覆盖了谁根本查不出来。工厂物资管理数据库系统要解决的就是这件事把物资主数据、出入库流水、库存余额、供应商与领用部门之间的关系用关系模型固定下来让每一次库存变动都有单据可追溯、有字段可校验、有权限可约束。它适合两类人一类是工厂 IT 或设备科里被拉来“搞个系统”的工程师另一类是想用最小成本替换掉 Excel 台账、又不想上重型 ERP 的现场管理者。下面按选型、建表、写事务、做查询、避坑、进阶的顺序把一条能复现的路径讲清楚。2. 选型与建模为什么先定物资主数据再谈库存2.1 数据库选型单机工厂场景下 SQLite 与 PostgreSQL 的取舍工厂物资管理的并发量通常不高几十个领料员、几个仓管峰值也就每秒几次写入。这个量级下选型的核心不是性能而是部署成本和事务可靠性。常见做法有两类一类是单机 SQLite整个库就是一个文件备份就是复制文件适合单仓库、单台电脑或局域网共享盘另一类是 PostgreSQL支持真正的多用户并发、行级锁、更完整的权限体系适合多仓库、多客户端同时写入。我一般会这样判断如果同时写入的客户端不超过 3 个且没有跨机房的访问需求SQLite 足够配合 WAL 模式能扛住日常出入库如果超过 3 个客户端并发写或者需要按角色做细粒度权限直接上 PostgreSQL别在 SQLite 上硬撑后期迁移的代价比一开始就选对更大。维度SQLitePostgreSQL部署单文件零服务需安装服务端并发写单写者WAL 下可读并发多写者行级锁权限文件级角色/表/列级备份复制文件pg_dump / 物理备份适用单仓、少量客户端多仓、多角色2.2 物资主数据表物料编码、规格、单位的字段设计库存对不上十有八九是主数据没定死。同一个螺丝有人写“M4×10”有人写“m4*10”有人写“4mm螺丝”出库时按名字模糊匹配必然错。物资主数据表的核心是给每个物料一个稳定、唯一、不随描述变化的编码描述字段只做展示不参与匹配。-- 物资主数据表编码唯一描述与规格分离 CREATE TABLE material ( material_id BIGSERIAL PRIMARY KEY, material_code VARCHAR(32) NOT NULL UNIQUE, -- 物料编码业务唯一键 material_name VARCHAR(128) NOT NULL, -- 物料名称仅展示 spec VARCHAR(128), -- 规格型号仅展示 unit VARCHAR(16) NOT NULL, -- 计量单位如 个/米/千克 category_id BIGINT, -- 分类便于统计 safety_stock NUMERIC(14,3) DEFAULT 0, -- 安全库存用于预警 is_active BOOLEAN DEFAULT TRUE, -- 软删除标记 created_at TIMESTAMP DEFAULT now() );逻辑说明material_code加唯一约束保证业务上不会出现两个“同一物料”spec和material_name只做展示任何匹配、对账都走material_codeis_active做软删除历史单据仍能关联到已停用物料避免外键断裂。参数上NUMERIC(14,3)支持到千分位够覆盖大多数按重量或长度计量的物资如果工厂有按批次管理的需求这里先不急着加批次字段批次属于库存层放到流水表里更合适。2.3 出入库流水与库存余额用事务保证账实一致流水表记录“发生了什么”余额表记录“现在剩多少”。两者必须在一个事务里更新否则会出现流水写了、余额没减的脏数据。常见做法是流水表只追加不修改余额表按物料维度维护一行当前值每次出入库用UPDATE ... WHERE配合行锁或乐观锁更新。-- 出入库流水表只追加不修改 CREATE TABLE stock_txn ( txn_id BIGSERIAL PRIMARY KEY, material_id BIGINT NOT NULL REFERENCES material(material_id), txn_type VARCHAR(8) NOT NULL, -- IN / OUT qty NUMERIC(14,3) NOT NULL CHECK (qty 0), warehouse_id BIGINT NOT NULL, ref_no VARCHAR(64), -- 关联单据号 operator VARCHAR(64), txn_time TIMESTAMP DEFAULT now() ); -- 库存余额表按物料仓库维度维护当前量 CREATE TABLE stock_balance ( material_id BIGINT NOT NULL, warehouse_id BIGINT NOT NULL, qty_on_hand NUMERIC(14,3) NOT NULL DEFAULT 0, updated_at TIMESTAMP DEFAULT now(), PRIMARY KEY (material_id, warehouse_id) );逻辑说明stock_txn的qty用CHECK (qty 0)约束方向由txn_type决定避免出现负数数量这种语义混乱stock_balance用(material_id, warehouse_id)做联合主键天然保证一个物料在一个仓库只有一行余额。更新余额时不要用“先查再算再写”要在一条 SQL 里完成加减减少并发窗口。3. 把出入库写成事务一条 SQL 完成扣减与流水3.1 入库事务插入流水并累加余额入库相对简单先插流水再对余额做INSERT ... ON CONFLICT累加。PostgreSQL 的ON CONFLICT能在一条语句里完成“有则加、无则插”避免先查后插的竞态。BEGIN; INSERT INTO stock_txn (material_id, txn_type, qty, warehouse_id, ref_no, operator) VALUES (1001, IN, 50, 1, PO-20240101-001, 仓管A); INSERT INTO stock_balance (material_id, warehouse_id, qty_on_hand) VALUES (1001, 1, 50) ON CONFLICT (material_id, warehouse_id) DO UPDATE SET qty_on_hand stock_balance.qty_on_hand EXCLUDED.qty_on_hand, updated_at now(); COMMIT;逻辑说明EXCLUDED是 PostgreSQL 在冲突时代表“本次想插入的那行”用它拿到本次入库数量DO UPDATE里对qty_on_hand做加法整个操作在一条语句内完成行锁由数据库保证。参数上material_id和warehouse_id必须真实存在否则外键会报错这正好挡住“给不存在的物料入库”这类脏操作。3.2 出库事务先校验余额再扣减防止负库存出库是容易翻车的地方。如果先扣再校验并发下可能扣成负数正确做法是把校验写进WHERE条件让数据库在更新时判断。BEGIN; -- 扣减余额条件里带 qty_on_hand 出库量扣不动就影响 0 行 UPDATE stock_balance SET qty_on_hand qty_on_hand - 20, updated_at now() WHERE material_id 1001 AND warehouse_id 1 AND qty_on_hand 20; -- 检查上一步是否真的扣成功没扣成功就回滚 -- 应用层读取 UPDATE 的影响行数若为 0 则执行 ROLLBACK INSERT INTO stock_txn (material_id, txn_type, qty, warehouse_id, ref_no, operator) VALUES (1001, OUT, 20, 1, WO-20240101-007, 领料员B); COMMIT;逻辑说明WHERE ... AND qty_on_hand 20是防负库存的关键余额不足时这条UPDATE影响 0 行应用层据此回滚整个事务流水也不会写入。参数上出库量必须与CHECK (qty 0)一致方向由txn_typeOUT表达不要用负数表示出库否则对账时符号容易搞混。3.3 用应用层代码包住事务Python 示例与重试数据库事务要靠应用层控制提交与回滚。下面用 Python 的psycopg演示一个出库函数包含影响行数判断和简单重试。import psycopg def outbound(conn, material_id, warehouse_id, qty, ref_no, operator): with conn.transaction(): # 进入事务异常自动回滚 with conn.cursor() as cur: cur.execute( UPDATE stock_balance SET qty_on_hand qty_on_hand - %s, updated_at now() WHERE material_id %s AND warehouse_id %s AND qty_on_hand %s, (qty, material_id, warehouse_id, qty), ) if cur.rowcount 0: # 余额不足或物料不存在 raise ValueError(库存不足出库取消) cur.execute( INSERT INTO stock_txn (material_id, txn_type, qty, warehouse_id, ref_no, operator) VALUES (%s, OUT, %s, %s, %s, %s), (material_id, qty, warehouse_id, ref_no, operator), ) # with 块正常退出即提交抛异常即回滚逻辑说明conn.transaction()是 psycopg 3 的事务上下文块内任何异常都会触发回滚避免“流水写了余额没扣”的半截状态cur.rowcount判断扣减是否命中命中 0 行说明余额不够直接抛错让事务回滚。参数上qty建议在入口做一次Decimal转换避免浮点误差ref_no用于关联工单或采购单方便后续追溯。4. 库存查询与预警把“还剩多少”和“该不该补”分开算4.1 实时库存查询按物料编码与仓库聚合日常问得最多的是“某物料现在还有多少”。余额表已经按物料仓库存了当前值直接查即可如果要跨仓库汇总用GROUP BY聚合。-- 单物料单仓库 SELECT m.material_code, m.material_name, b.qty_on_hand FROM stock_balance b JOIN material m ON m.material_id b.material_id WHERE m.material_code M-1001 AND b.warehouse_id 1; -- 单物料跨仓库汇总 SELECT m.material_code, SUM(b.qty_on_hand) AS total_qty FROM stock_balance b JOIN material m ON m.material_id b.material_id WHERE m.material_code M-1001 GROUP BY m.material_code;逻辑说明余额表是“当前快照”查询走它比每次从流水表SUM快得多流水表用于对账和追溯不用于日常查询。参数上material_code走唯一索引warehouse_id走联合主键前缀两个查询都能命中索引。4.2 安全库存预警用余额与安全库存做差预警不是“低于安全库存就报警”这么简单还要考虑在途量和已分配量。最小可用版本先做“当前余额 安全库存”的清单后续再叠加在途。SELECT m.material_code, m.material_name, m.safety_stock, COALESCE(SUM(b.qty_on_hand), 0) AS on_hand, m.safety_stock - COALESCE(SUM(b.qty_on_hand), 0) AS gap FROM material m LEFT JOIN stock_balance b ON b.material_id m.material_id WHERE m.is_active TRUE GROUP BY m.material_id, m.material_code, m.material_name, m.safety_stock HAVING COALESCE(SUM(b.qty_on_hand), 0) m.safety_stock ORDER BY gap DESC;逻辑说明用LEFT JOIN保证没有任何库存记录的物料也能出现在预警里COALESCE把NULL当 0 处理HAVING在聚合后过滤gap表示还差多少才到安全库存按缺口降序排优先补缺口大的。参数上safety_stock为 0 的物料不会进预警符合“没设安全库存就不预警”的预期。4.3 流水对账用流水重算余额并与余额表比对余额表可能因为历史脏数据或手工改库而不准定期用流水重算一遍和余额表比对是发现问题的有效手段。-- 按流水重算每个物料每个仓库的余额 WITH recomputed AS ( SELECT material_id, warehouse_id, SUM(CASE WHEN txn_type IN THEN qty ELSE -qty END) AS calc_qty FROM stock_txn GROUP BY material_id, warehouse_id ) SELECT r.material_id, r.warehouse_id, r.calc_qty, b.qty_on_hand, r.calc_qty - b.qty_on_hand AS diff FROM recomputed r JOIN stock_balance b ON b.material_id r.material_id AND b.warehouse_id r.warehouse_id WHERE r.calc_qty b.qty_on_hand;逻辑说明CASE WHEN把入库当正、出库当负SUM得到理论余额与余额表JOIN后只输出不一致的行diff就是差异量。参数上这个查询在流水量大时较慢建议放在低峰期跑或按时间范围分段重算。5. 避坑与排查库存对不上时先看这五处5.1 现象余额出现负数但流水里没有负数量原因通常是出库时先扣了余额、后校验或者应用层没判断rowcount就提交了。解决把校验写进UPDATE ... WHERE qty_on_hand ?并在应用层检查影响行数为 0 就回滚同时给stock_balance.qty_on_hand加CHECK (qty_on_hand 0)作为最后一道防线让数据库直接拒绝负库存。5.2 现象同一物料在余额表里出现两行原因多是余额表没建联合主键或者早期用“先查再插”导致并发插了两行。解决给(material_id, warehouse_id)加主键或唯一约束把插入改成INSERT ... ON CONFLICT DO UPDATE已经出现重复的先按物料仓库汇总合并再补约束。5.3 现象流水和余额对不上差额刚好是某几次操作原因可能是事务没包住流水写了但余额更新失败或者反过来。解决确认出入库的插入流水和更新余额在同一个事务里用第 4.3 节的重算查询定位差异物料再按ref_no找到对应单据核对。血泪经验是任何“先写日志再更新”的拆分操作都要问一句“中间失败了怎么办”。5.4 现象并发出库时偶尔超卖原因是用“先查余额再扣减”的两步操作两个请求都查到够然后都扣。解决改成单条UPDATE ... WHERE qty_on_hand ?让数据库在行锁下判断或者用SELECT ... FOR UPDATE锁住余额行再算。前者更简单推荐优先用。5.5 现象物料编码重复对账时合并出错原因是编码规则没定死或者导入时没做唯一校验。解决material_code加唯一约束导入前先做一次去重检查编码规则建议“分类前缀流水号”不要用名称拼音避免同音物料撞码。已经重复的保留一条为主其余停用并把历史流水迁到主编码上。6. 进阶用触发器兜底与用视图简化日常查询6.1 用触发器禁止直接改流水表流水表只追加不修改是账实一致的前提。但总有人图省事直接UPDATE流水导致重算结果和余额对不上。可以在流水表上加一个触发器禁止更新和删除。CREATE OR REPLACE FUNCTION forbid_txn_change() RETURNS trigger AS $$ BEGIN RAISE EXCEPTION stock_txn 只允许追加不允许修改或删除; END; $$ LANGUAGE plpgsql; CREATE TRIGGER trg_txn_no_update BEFORE UPDATE OR DELETE ON stock_txn FOR EACH ROW EXECUTE FUNCTION forbid_txn_change();逻辑说明BEFORE UPDATE OR DELETE在行被改动前触发直接抛异常中断操作FOR EACH ROW保证逐行拦截。参数上这个触发器对批量删除同样生效如果确实需要清理历史流水先禁用触发器再操作操作完立即启用并记录原因。6.2 用视图把常用查询固化下来仓管和采购每天要看的东西差不多与其让他们记 SQL不如建几个视图把关联和过滤都封进去。-- 当前库存视图物料编码、名称、仓库、数量 CREATE VIEW v_current_stock AS SELECT m.material_code, m.material_name, m.spec, m.unit, b.warehouse_id, b.qty_on_hand, b.updated_at FROM stock_balance b JOIN material m ON m.material_id b.material_id WHERE m.is_active TRUE; -- 待补货物料视图低于安全库存的清单 CREATE VIEW v_reorder_list AS SELECT m.material_code, m.material_name, m.safety_stock, COALESCE(SUM(b.qty_on_hand), 0) AS on_hand FROM material m LEFT JOIN stock_balance b ON b.material_id m.material_id WHERE m.is_active TRUE GROUP BY m.material_id, m.material_code, m.material_name, m.safety_stock HAVING COALESCE(SUM(b.qty_on_hand), 0) m.safety_stock;逻辑说明视图不存数据只保存查询定义底层表结构变化时视图可能需要重建但日常查询不用改v_current_stock过滤掉停用物料v_reorder_list直接给出待补货清单。参数上视图的权限可以单独授予只读账号避免仓管直接碰基表。6.3 一个具体技巧用导出快照做月度对账月底对账时与其在线上库跑重查询不如先导出一份快照在快照上慢慢算。PostgreSQL 可以用COPY把余额和流水导成 CSV再用任意工具比对。# 导出当前余额快照 psql -d factory -c \copy (SELECT * FROM stock_balance) TO balance_snapshot.csv CSV HEADER # 导出本月流水 psql -d factory -c \copy (SELECT * FROM stock_txn WHERE txn_time 2024-01-01) TO txn_jan.csv CSV HEADER逻辑说明\copy是 psql 客户端命令把查询结果写到本地文件不占用服务端文件权限导出后可以用脚本重算余额和快照比对差异行再回线上库定位。参数上时间范围按对账周期调整导出前建议先VACUUM ANALYZE让统计信息新鲜查询计划更稳。我自己踩过最深的一个坑是早期图快把出入库写成了“先查余额、再算、再更新”三步测试时单人操作一切正常上线后两个领料员同时出同一种料直接超卖月底盘亏才发现。从那以后凡是涉及数量增减的地方我都坚持把校验写进WHERE让数据库在一条语句里完成判断和更新应用层只负责看影响行数。这个习惯帮我省掉了后面无数次对账的麻烦。希望帮到你。本文还有配套的精品资源点击获取