中小企业MES系统数据库设计-CSDN博客

本文链接：https://blog.csdn.net/qq407155634/article/details/147673662

版本：V1.0
日期：2025年5月2日

一、数据库架构概览

1.1 数据库选型

数据类型	数据库类型	技术选型	用途
时序数据（传感器读数）	时序数据库	TimescaleDB	存储设备实时监控数据
结构化业务数据	关系型数据库	PostgreSQL	工单、质量、设备等核心业务数据
非结构化数据	对象存储	MinIO	质检图片、SOP文档等
实时缓存	内存数据库	Redis	看板数据、会话状态

二、核心表结构设计

2.1 生产执行模块

工单表 (work_order)

字段名	类型	约束	描述
order_id	VARCHAR(36)	PRIMARY KEY	工单唯一标识
product_code	VARCHAR(20)	NOT NULL	产品型号
plan_qty	INTEGER	CHECK>0	计划数量
priority	ENUM(‘H’,‘M’,‘L’)	DEFAULT ‘M’	优先级
status	ENUM(‘Created’,‘Scheduled’,‘Running’,‘Completed’,‘Closed’)	NOT NULL	工单状态
start_time	TIMESTAMPTZ		实际开始时间
end_time	TIMESTAMPTZ		实际结束时间

工序表 (work_step)

字段名	类型	约束	描述
step_id	SERIAL	PRIMARY KEY	工序自增ID
order_id	VARCHAR(36)	FOREIGN KEY REFERENCES work_order(order_id)	关联工单
seq_num	INTEGER	CHECK>0	工序顺序号
equipment_id	VARCHAR(20)		绑定设备编号
target_cycle	FLOAT		标准节拍（秒/件）

2.2 质量管理模块

质检记录表 (quality_inspection)

CREATE TABLE quality_inspection (
  inspection_id UUID PRIMARY KEY,
  order_id VARCHAR(36) REFERENCES work_order(order_id),
  step_id INTEGER REFERENCES work_step(step_id),
  defect_code VARCHAR(10) NOT NULL,  -- 缺陷代码
  severity_level SMALLINT CHECK (severity_level BETWEEN 1 AND 5),
  inspector VARCHAR(32),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  CHECK (
    (defect_code = 'OK' AND severity_level IS NULL) 
    OR 
    (defect_code <> 'OK' AND severity_level IS NOT NULL)
  )
);

SPC数据表 (spc_data)

字段名	类型	约束	描述
sample_id	UUID	PRIMARY KEY	采样数据ID
parameter_name VARCHAR(50)	NOT NULL	监控参数名（如"直径"）
measurement	FLOAT	NOT NULL	测量值
usl	FLOAT		规格上限
lsl	FLOAT		规格下限
collection_time TIMESTAMPTZ	NOT NULL	采集时间

2.3 设备管理模块

设备表 (equipment)

CREATE TABLE equipment (
  equipment_id VARCHAR(20) PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  type VARCHAR(20) CHECK (type IN ('CNC','AGV','Sensor')),
  protocol_type VARCHAR(20) NOT NULL,  -- 协议类型（OPCUA/Modbus等）
  ip_address INET,
  last_maintenance_date DATE,
  oee_target FLOAT CHECK (oee_target BETWEEN 0 AND 1)
);

维护记录表 (maintenance_log)

字段名	类型	约束	描述
log_id	SERIAL	PRIMARY KEY	维护记录ID
equipment_id	VARCHAR(20)	REFERENCES equipment(equipment_id)	设备ID
maintenance_type ENUM(‘Preventive’,‘Corrective’)	NOT NULL	维护类型
	downtime_duration INTERVAL	NOT NULL	停机时长

2.4 时序数据表（TimescaleDB超表）

传感器数据表 (sensor_data)

CREATE TABLE sensor_data (
  time TIMESTAMPTZ NOT NULL,
  device_id VARCHAR(20) REFERENCES equipment(equipment_id),
  sensor_type VARCHAR(20),
  value DOUBLE PRECISION,
  status_code SMALLINT
);

-- 转换为TimescaleDB超表
SELECT create_hypertable(
  'sensor_data',
  'time',
  chunk_time_interval => INTERVAL '1 day',
  partitioning_column => 'device_id',
  number_partitions => 16
);

-- 创建压缩策略
ALTER TABLE sensor_data SET (
  timescaledb.compress,
  timescaledb.compress_orderby = 'time DESC',
  timescaledb.compress_segmentby = 'device_id'
);

三、索引设计

3.1 关系型数据库索引

表名	索引字段	类型	用途
work_order	(status, start_time)	复合索引	快速查询进行中工单
quality_inspection	(order_id, defect_code)	复合索引	缺陷统计分析
equipment	(type, protocol_type)	GIN索引	设备类型快速检索

3.2 时序数据库索引

-- 时间范围查询优化
CREATE INDEX idx_sensor_time ON sensor_data (time DESC);

-- 设备维度查询优化
CREATE INDEX idx_sensor_device ON sensor_data (device_id, time DESC);

四、数据分区策略

4.1 关系型数据分区

-- 按时间范围分区（工单表）
CREATE TABLE work_order_2023 PARTITION OF work_order
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- 按设备类型哈希分区（设备表）
CREATE TABLE equipment_cnc PARTITION OF equipment
  FOR VALUES WITH (MODULUS 4, REMAINDER 0)
  WHERE type = 'CNC';

4.2 时序数据压缩

# TimescaleDB压缩策略
compression:
  segment_by: device_id
  order_by: time DESC
  chunk_time_interval: 1d
  compression_algorithm: zstd

五、安全设计

5.1 权限模型

-- 角色定义
CREATE ROLE production_manager;
GRANT SELECT, UPDATE ON work_order TO production_manager;

-- 行级安全（RLS）
ALTER TABLE quality_inspection ENABLE ROW LEVEL SECURITY;
CREATE POLICY qc_policy ON quality_inspection
  USING (inspector = CURRENT_USER);

5.2 审计日志表

CREATE TABLE audit_log (
  log_id BIGSERIAL PRIMARY KEY,
  table_name VARCHAR(50) NOT NULL,
  operation_type VARCHAR(10) CHECK (operation_type IN ('INSERT','UPDATE','DELETE')),
  old_record JSONB,
  new_record JSONB,
  user_name VARCHAR(50) NOT NULL,
  operation_time TIMESTAMPTZ DEFAULT NOW()
);

六、性能优化设计

6.1 物化视图

-- OEE每日汇总
CREATE MATERIALIZED VIEW daily_oee
AS
SELECT 
  equipment_id,
  time_bucket('1 day', time) AS bucket,
  AVG(availability) AS avg_availability,
  AVG(performance) AS avg_performance,
  AVG(quality) AS avg_quality
FROM oee_calculations
GROUP BY equipment_id, bucket
WITH DATA;

-- 每日定时刷新
SELECT add_continuous_aggregate_policy('daily_oee',
  start_offset => INTERVAL '3 days',
  end_offset => INTERVAL '1 hour',
  schedule_interval => INTERVAL '1 day');

6.2 查询缓存

-- 高频查询结果缓存
CREATE TABLE query_cache (
  cache_key VARCHAR(128) PRIMARY KEY,
  result JSONB NOT NULL,
  expires_at TIMESTAMPTZ NOT NULL
);

-- 索引加速缓存查询
CREATE INDEX idx_cache_expiry ON query_cache (expires_at);