📚 课程目录第4章基础数据建模

4. 基础数据建模

试剂耗材数字化管理系统能否长期稳定运行，很大程度上取决于基础数据模型是否合理。主数据建模的核心任务，是把实验室日常管理中反复出现的对象抽象为稳定的实体，并明确它们之间的引用关系。一个常见的误区是先把“入库单”“领用单”这类业务单据建出来，再倒推主数据；更稳妥的顺序是先定义主数据实体，再让业务单据通过外键引用主数据。这样做的直接好处是：同一瓶试剂不会因为录入人不同而出现三种名称，同一间冷库不会在系统里被重复创建四次。

一、核心实体与关系

本课程建议至少建立六类主数据实体：试剂耗材主数据（Material）、供应商（Supplier）、存储位置（Location）、人员（Person）、项目（Project），以及作为可选扩展的危化品属性（HazardProfile）。它们之间的关系可以概括为：一种物料可由多个供应商供货，一个供应商可供应多种物料，因此物料与供应商之间是多对多关系，需要一张“物料—供应商”关联表来承载货号、供货规格、参考单价等随供应商变化的属性；物料必须存放在某个具体存储位置，一个位置可存放多种物料，属于一对多关系；人员与项目之间是多对多关系，一个人可参与多个项目，一个项目也有多名成员；领用、入库等业务记录则同时引用物料、人员、项目和位置。

需要特别强调的是“位置”的建模方式。实验室的存储位置天然具有层级：楼栋 → 房间 → 冰箱/柜 → 层/抽屉 → 盒/架 → 孔位。如果只用一个文本字段记录“A栋201冰箱第二层”，后续做库存盘点、效期预警、按位置统计时几乎无法可靠查询。推荐采用自引用树形结构：位置表中设置 parent_id 指向同表的上级位置，用 level_type 标识层级类型（BUILDING/ROOM/FRIDGE/SHELF/BOX/POSITION），用 full_path 冗余保存完整路径便于展示和模糊检索。这样既能表达任意深度，又能在盘点时按任意层级汇总。

二、关键表设计与字段规范

下面给出核心表的字段设计示例，字段命名统一采用小写下划线风格，主键统一使用自增整数或 UUID，时间字段统一使用 DATETIME 并记录创建与更新时间。

-- 试剂耗材主数据表
CREATE TABLE material (
  material_id     BIGINT PRIMARY KEY AUTO_INCREMENT,
  material_code   VARCHAR(32)  NOT NULL UNIQUE COMMENT '内部物料编码',
  name_cn         VARCHAR(128) NOT NULL COMMENT '中文名称',
  name_en         VARCHAR(128)          COMMENT '英文名称',
  cas_no          VARCHAR(32)           COMMENT 'CAS号，混合物可为空',
  spec            VARCHAR(64)  NOT NULL COMMENT '规格，如500mL/瓶',
  unit            VARCHAR(16)  NOT NULL COMMENT '计量单位',
  category        VARCHAR(32)  NOT NULL COMMENT '类别：试剂/耗材/标准品',
  storage_cond    VARCHAR(32)  NOT NULL COMMENT '存储条件：常温/2-8℃/-20℃',
  is_hazardous    TINYINT(1)   NOT NULL DEFAULT 0 COMMENT '是否危化品',
  default_loc_id  BIGINT                COMMENT '默认存储位置',
  status          TINYINT      NOT NULL DEFAULT 1 COMMENT '1启用 0停用',
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 存储位置表（自引用树）
CREATE TABLE location (
  location_id   BIGINT PRIMARY KEY AUTO_INCREMENT,
  parent_id     BIGINT          COMMENT '上级位置，根节点为NULL',
  location_code VARCHAR(64) NOT NULL UNIQUE COMMENT '位置编码',
  location_name VARCHAR(64) NOT NULL,
  level_type    VARCHAR(16) NOT NULL COMMENT 'BUILDING/ROOM/FRIDGE/SHELF/BOX/POSITION',
  full_path     VARCHAR(255) NOT NULL COMMENT '冗余完整路径',
  temperature_zone VARCHAR(16) COMMENT '常温/冷藏/冷冻',
  status        TINYINT NOT NULL DEFAULT 1
);

-- 物料—供应商关联表
CREATE TABLE material_supplier (
  id            BIGINT PRIMARY KEY AUTO_INCREMENT,
  material_id   BIGINT NOT NULL,
  supplier_id   BIGINT NOT NULL,
  supplier_cat_no VARCHAR(64) COMMENT '供应商货号',
  ref_price     DECIMAL(10,2) COMMENT '参考单价',
  is_preferred  TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否首选供应商',
  UNIQUE KEY uk_mat_sup (material_id, supplier_id)
);

字段规范上有几条经验值得坚持。第一，编码与名称分离：material_code 由系统按规则生成（如“SH-试剂-0001”），名称允许修改，编码一旦生成不再变更，避免历史单据因改名而失去可追溯性。第二，枚举值统一：category、storage_cond、level_type 等字段应通过字典表或应用层枚举约束，禁止自由文本。第三，软删除优先：主数据被业务单据引用后不应物理删除，用 status 字段标记停用，停用后不可再被新单据选择，但历史记录仍可正常显示。

建模时建议先画 ER 图再建表。ER 图中每一条连线都应在数据库中有对应的外键或关联表，反之亦然。如果发现某条业务规则无法在 ER 图中表达，通常说明缺少一个实体或关联表。

三、建模中的常见问题与处理

实际落地时最容易出问题的是“一物多码”和“一码多物”。前者指同一种试剂因不同供应商、不同包装被建成多条主数据，导致库存分散、统计失真；后者指不同浓度或不同规格的同一试剂共用一个编码，导致领用时无法区分。处理原则是：以“可独立管理的最小库存单元”为一条主数据。例如无水乙醇 500mL/瓶与 2.5L/桶应建为两条主数据，因为它们的存储位置、领用习惯和盘点方式都不同；而同一规格的无水乙醇无论从哪家供应商采购，都应是同一条主数据，供应商差异通过 material_supplier 关联表体现。

另一个高频问题是位置树的深度失控。建议在系统层面限制最大层级为 6 层，并在创建位置时强制选择 level_type，由类型决定其允许的父级类型。例如 FRIDGE 的父级只能是 ROOM，BOX 的父级只能是 SHELF。这样可以在录入阶段就拦截大部分错误结构，而不是等到盘点时才发现某台冰箱被挂在了楼栋下面。

最后，人员与项目的建模要预留组织维度。person 表中除姓名、工号外，建议保留 department 与 role 字段，project 表保留 status（进行中/已结题）与 leader_id。当项目结题后，其关联的领用记录仍应可查，但项目本身不再出现在新领用的下拉列表中。这些约束看似琐碎，却直接决定了系统在运行两三年后是否仍然可用。