2. 农业物联网数据模型设计

农业物联网的数据不是“一张表装到底”的。一个温室大棚里，网关、采集节点、空气温湿度传感器、土壤墒情探头、水肥一体机控制器同时存在，它们产生的数据在时间粒度、变化规律和业务含义上差别很大：设备属性相对稳定，传感器读数按分钟级持续增长，而灌溉启动、断电恢复、阈值越限这类事件则是稀疏且带业务语义的。因此，本节的核心任务是把这些异构信息拆解为四类实体——设备表、传感器表、观测表、事件表，并用关系模型表达“谁属于谁”，用时序模型表达“什么时候变成了多少”。

一、四类实体的职责边界

设计数据模型的第一步不是写建表语句，而是明确每类实体回答什么问题。设备表回答“现场有哪些物理装置、部署在哪里、由谁管理”，它描述的是相对静态的资产信息，例如设备编号、设备类型（网关/采集节点/控制器）、安装位置（大棚编号、区域）、通信方式（LoRa、NB-IoT、RS-485）、上线时间与状态。传感器表回答“某台设备上挂了哪些测量通道”，因为一台采集节点往往带多路探头，每路探头有独立的物理量类型、量程、精度和安装深度，例如土壤温度探头埋深 10cm、土壤含水率探头埋深 20cm。传感器是设备的从属实体，一台设备对应多个传感器，构成一对多关系。

观测表是数据量最大的表，回答“某个传感器在某个时刻测到了什么值”。它必须同时携带传感器标识、时间戳、数值和单位/质量标记，是典型的时间序列事实表。事件表回答“系统或设备在某个时刻发生了什么值得记录的事”，例如灌溉阀门开启、设备离线、温度越限告警、固件升级。事件与观测的区别在于：观测是周期性、连续、可聚合的；事件是离散、稀疏、带业务语义的。把两者混在一张表里，会导致查询时大量空字段和语义混乱，因此必须分开建模。

二、关系模型：用外键把层级串起来

四张表之间的层级关系是：设备 1—N 传感器，传感器 1—N 观测，设备 1—N 事件。落到关系模型上，就是传感器表持有 device_id 外键，观测表持有 sensor_id 外键，事件表持有 device_id 外键。这里有一个容易踩的坑：观测表是否也要冗余 device_id？在写入量很大的场景下，冗余设备号可以减少一次关联查询，但会带来一致性维护成本。教学示例中建议先保持规范化，等查询压力明确后再考虑冗余，而不是一开始就反范式。

另一个关键点是主键选择。观测表若用自增 id 作主键，在按传感器和时间范围查询时仍需额外索引；更合理的做法是以 (sensor_id, ts) 作为联合主键或唯一约束，天然支持“某传感器某时间段”的范围扫描。事件表则用 event_id 作主键，并给 (device_id, ts) 建索引，便于按设备回溯事件序列。

三、时序模型：时间戳、粒度与分区

观测表是典型时序数据，设计时要明确三件事。第一是时间戳语义：统一采用采集时刻的 UTC 时间存储，展示时再转换为本地时区，避免跨时区大棚数据错位。第二是采集粒度：空气温湿度可 1 分钟一条，土壤墒情可 10 分钟一条，粒度由传感器表里的 sample_interval 字段声明，而不是写死在代码里。第三是分区策略：按时间范围（如按月）分区，配合 (sensor_id, ts) 索引，使“查某传感器最近 24 小时数据”只扫描少量分区。

下面是一段可直接参考的建表示例（以 PostgreSQL 语法为例，参数为教学示例）：

CREATE TABLE device (
  device_id      VARCHAR(32) PRIMARY KEY,
  device_type    VARCHAR(20) NOT NULL,      -- gateway / node / controller
  greenhouse_id  VARCHAR(16) NOT NULL,
  comm_protocol  VARCHAR(16),
  status         SMALLINT DEFAULT 1,
  install_time   TIMESTAMPTZ
);

CREATE TABLE sensor (
  sensor_id      VARCHAR(32) PRIMARY KEY,
  device_id      VARCHAR(32) REFERENCES device(device_id),
  measure_type   VARCHAR(20) NOT NULL,      -- air_temp / soil_moisture
  unit           VARCHAR(10),
  range_min      NUMERIC(8,2),
  range_max      NUMERIC(8,2),
  sample_interval INT                        -- 秒
);

CREATE TABLE observation (
  sensor_id      VARCHAR(32) REFERENCES sensor(sensor_id),
  ts             TIMESTAMPTZ NOT NULL,
  value          NUMERIC(10,3),
  quality_flag   SMALLINT DEFAULT 0,
  PRIMARY KEY (sensor_id, ts)
) PARTITION BY RANGE (ts);

CREATE TABLE event (
  event_id       BIGSERIAL PRIMARY KEY,
  device_id      VARCHAR(32) REFERENCES device(device_id),
  event_type     VARCHAR(32) NOT NULL,
  ts             TIMESTAMPTZ NOT NULL,
  severity       SMALLINT,
  payload        JSONB
);
CREATE INDEX idx_event_dev_ts ON event(device_id, ts);

四、数据关联与典型查询

模型设计是否合理，最终要看查询是否顺畅。农业场景中最常见的三类查询是：某大棚当前各传感器最新值、某传感器某时间段的变化曲线、某设备近期的异常事件。第一类查询需要“每传感器取最新一条”，可用窗口函数或 DISTINCT ON 实现；第二类直接命中 (sensor_id, ts) 主键范围扫描；第三类走事件表索引。若把事件混入观测表，第二类查询就会被大量空值拖慢，这正是分表的价值所在。

最后要强调层级关系的完整性。设备下线时，其传感器与历史观测不应被级联删除，而应通过状态字段标记为停用，保留历史数据用于追溯。外键约束负责“引用不能悬空”，业务状态负责“历史不能丢失”，两者配合，才能让从设备到数据流的关系模型既严谨又可用。