4. 钻孔数据库逻辑设计

概念模型解决的是“有哪些实体、实体之间是什么关系”，逻辑设计则要把这些实体落实为数据库中可以建表、可以约束、可以查询的关系模式。钻孔数据库的逻辑设计核心，是把一个钻孔从“空间位置—地层分层—样品采集—物探测井”这条完整信息链，拆解为若干张职责单一、又能通过键值严密关联的关系表。本节围绕钻孔主表、分层表、采样表、测井表四张核心表，讲清字段组织、主键选取、外键指向以及参照完整性约束的设定方法。

一、四张核心表的职责划分

钻孔主表（drillhole）是整条信息链的“锚点”，一行记录对应一个钻孔，存放不随深度变化的属性：钻孔编号、孔口坐标X/Y/Z、开孔日期、终孔深度、钻孔倾角与方位角、施工单位、孔口标高、钻孔类型（岩芯钻、冲击钻、水文孔等）。主表不存放分层或样品信息，避免同一钻孔的属性在多行中重复。

分层表（layer）描述钻孔穿越的地层段，一行对应一个分层段，字段包括所属钻孔、分层序号、顶板深度、底板深度、岩性代码、地层代号、接触关系、描述。采样表（sample）描述取样位置与样品属性，字段包括所属钻孔、采样起止深度、样品编号、样品种类（岩芯样、刻槽样、水样）、分析项目、送样日期。测井表（log_curve）描述测井曲线，字段包括所属钻孔、曲线类型（自然伽马、电阻率、密度等）、起止深度、采样间隔、曲线数据文件或数值序列。

四张表的关系是典型的一对多：一个钻孔有多个分层段、多个样品、多条测井曲线。这种“一主三从”的结构，正是关系模型表达钻孔信息链的自然方式。

二、主键与外键的设定

主键的选择要兼顾唯一性、稳定性和查询效率。钻孔主表建议采用代理主键 hole_id（整型自增或序列），同时把业务编号 hole_code 设为唯一约束（UNIQUE）。这样做的原因是：钻孔编号在野外可能因重名、补号、改号而变动，而代理键一旦生成就不再变化，从表引用它更稳定。

三张从表的主键有两种常见方案。方案一是代理主键加业务唯一约束，例如分层表用 layer_id 作主键，并对 (hole_id, from_depth) 建唯一约束；方案二是复合主键，直接用 (hole_id, layer_no) 作主键。对于分层表，推荐复合主键或“代理键+唯一约束”，因为同一钻孔内分层序号天然唯一，且能直接防止重复录入。采样表因样品编号全局唯一，可用 sample_id 作主键、sample_code 唯一；测井表则常用 (hole_id, curve_type, from_depth) 组合约束，防止同一曲线同一深度重复入库。

外键统一指向钻孔主表的 hole_id，并在从表上建立索引以加速按孔查询。外键列必须与主键列同类型、同精度，否则数据库会拒绝创建约束或导致隐式转换、索引失效。

三、参照完整性约束的设定

参照完整性保证从表中的 hole_id 一定能在主表中找到对应记录，杜绝“孤儿分层”“孤儿样品”。建表时用 FOREIGN KEY (hole_id) REFERENCES drillhole(hole_id) 声明，并明确删除与更新动作。工程上通常采用 ON DELETE RESTRICT：只要某钻孔还有分层、样品或测井记录，就不允许删除主表记录，强制先清理从表数据，防止误删造成信息链断裂。若业务允许整孔作废，可改为 ON DELETE CASCADE，但必须配合权限控制和操作日志，避免级联删除失控。

除参照完整性外，还要补充域完整性与业务规则约束。例如分层表加 CHECK (to_depth > from_depth AND from_depth >= 0)；采样表的起止深度应落在主表 final_depth 范围内，可用触发器或应用层校验实现；测井表的 sample_interval > 0。这些约束把“深度不能为负、顶板必须浅于底板、样品不能超出终孔深度”等地质常识固化到数据库层，比事后人工检查可靠得多。

四、一个可执行的建表示例

下面给出 PostgreSQL 语法的核心建表片段，示例参数仅用于说明结构，实际字段可按项目扩展：

CREATE TABLE drillhole (
  hole_id      SERIAL PRIMARY KEY,
  hole_code    VARCHAR(20) NOT NULL UNIQUE,
  x_coord      NUMERIC(12,3) NOT NULL,
  y_coord      NUMERIC(12,3) NOT NULL,
  z_elev       NUMERIC(8,3),
  final_depth  NUMERIC(8,2) CHECK (final_depth > 0),
  dip          NUMERIC(5,2),
  azimuth      NUMERIC(5,2),
  hole_type    VARCHAR(20),
  start_date   DATE
);

CREATE TABLE layer (
  hole_id      INTEGER NOT NULL REFERENCES drillhole(hole_id) ON DELETE RESTRICT,
  layer_no     INTEGER NOT NULL,
  from_depth   NUMERIC(8,2) NOT NULL,
  to_depth     NUMERIC(8,2) NOT NULL,
  lith_code    VARCHAR(10),
  strata_code  VARCHAR(20),
  PRIMARY KEY (hole_id, layer_no),
  CHECK (to_depth > from_depth AND from_depth >= 0)
);

CREATE TABLE sample (
  sample_id    SERIAL PRIMARY KEY,
  hole_id      INTEGER NOT NULL REFERENCES drillhole(hole_id) ON DELETE RESTRICT,
  sample_code  VARCHAR(30) NOT NULL UNIQUE,
  from_depth   NUMERIC(8,2) NOT NULL,
  to_depth     NUMERIC(8,2) NOT NULL,
  sample_type  VARCHAR(20),
  CHECK (to_depth > from_depth)
);

CREATE TABLE log_curve (
  log_id       SERIAL PRIMARY KEY,
  hole_id      INTEGER NOT NULL REFERENCES drillhole(hole_id) ON DELETE RESTRICT,
  curve_type   VARCHAR(20) NOT NULL,
  from_depth   NUMERIC(8,2) NOT NULL,
  to_depth     NUMERIC(8,2) NOT NULL,
  sample_interval NUMERIC(6,3) CHECK (sample_interval > 0),
  UNIQUE (hole_id, curve_type, from_depth)
);

建表后应做一次约束有效性验证：尝试插入一条 hole_id = 9999 的分层记录，数据库应报外键违反；尝试插入 to_depth < from_depth 的分层，应报 CHECK 违反；尝试删除仍有分层记录的钻孔，应被 RESTRICT 拒绝。这三项测试通过，说明参照完整性与域约束已真正生效，而不是停留在设计文档里。

逻辑设计的成果不是四张孤立的表，而是一套“主表锚定、从表挂接、约束兜底”的结构。字段职责清晰、键值指向明确、约束可验证，后续的钻孔查询、剖面生成与储量估算才有可靠的数据基础。