---

版本：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_orderFOR VALUES FROM ('2025-01-01') TO ('2026-01-01');-- 按設備類型哈希分區（設備表）
CREATE TABLE equipment_cnc PARTITION OF equipmentFOR VALUES WITH (MODULUS 4, REMAINDER 0)WHERE type = 'CNC';

4.2 時序數據壓縮

# TimescaleDB壓縮策略
compression:segment_by: device_idorder_by: time DESCchunk_time_interval: 1dcompression_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_inspectionUSING (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);

---

七、數據字典

7.1 關鍵枚舉值

表名	字段名	枚舉值
work_order	status	Created/Scheduled/Running/Completed/Closed
equipment	type	CNC/AGV/Sensor
maintenance_log	maintenance_type	Preventive/Corrective

---

設計驗證要點：

1. 時序數據寫入：測試500設備持續寫入時，TimescaleDB的吞吐量是否≥10萬數據點/秒
2. 復雜查詢響應：驗證跨工單-工序-質檢的關聯查詢在1億數據量下的執行計劃
3. 分區切換：模擬年度數據歸檔時，新分區自動創建是否正常

該數據庫設計通過以下創新點支撐系統需求：

• 混合存儲策略：結合關系型與時序數據庫優勢，平衡事務處理與時間序列分析需求
• 動態壓縮：TimescaleDB自動壓縮策略降低存儲成本70%+
• 多級安全：行級安全（RLS）+ 列權限控制實現細粒度數據訪問