SQL数据库设计：数据字段超规格校验方案咨询

Got it, let's break down how to design this SQL database for out-of-spec measurement validation. The core idea is to decouple your measurement data from the threshold rules—since different locations have different specs for each metric, we don't want to hardcode those values into queries or application logic. Here's a practical, maintainable approach:

We'll need two main tables to keep things organized:

• A specifications table to store location-specific thresholds for each measurement metric (temperature, pressure, flow rate). This makes updating specs a breeze without touching code.
• A measurement records table to store the actual collected data points.

First, let's define the specifications table. Each row represents a unique threshold rule for a specific location and metric:

CREATE TABLE location_specs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    location VARCHAR(50) NOT NULL,
    metric_name VARCHAR(50) NOT NULL, -- e.g., 'temperature', 'pressure', 'flow_rate'
    min_value DECIMAL(10,2) NOT NULL,
    max_value DECIMAL(10,2) NOT NULL,
    UNIQUE KEY unique_location_metric (location, metric_name) -- Prevent duplicate rules for the same location+metric
);

Next, the measurement records table to hold your actual collected data:

CREATE TABLE measurement_records (
    id INT PRIMARY KEY AUTO_INCREMENT,
    record_date DATETIME NOT NULL, -- Using DATETIME to capture exact measurement timestamp
    location VARCHAR(50) NOT NULL,
    temperature DECIMAL(10,2), -- Allow NULL if this metric wasn't measured for the record
    pressure DECIMAL(10,2),
    flow_rate DECIMAL(10,2),
    FOREIGN KEY (location) REFERENCES location_specs(location) -- Ensure only locations with defined specs can have records
);

Add your location-specific rules here. For example:

-- Example specs: Location A has temp 20-35, pressure 100-150; Location B has temp 18-32, pressure 90-140
INSERT INTO location_specs (location, metric_name, min_value, max_value)
VALUES
('Location A', 'temperature', 20.00, 35.00),
('Location A', 'pressure', 100.00, 150.00),
('Location A', 'flow_rate', 5.00, 20.00),
('Location B', 'temperature', 18.00, 32.00),
('Location B', 'pressure', 90.00, 140.00),
('Location B', 'flow_rate', 4.00, 18.00);

To pull all records that violate their location's specs, use UNION ALL to check each metric individually:

-- Get all out-of-spec temperature records
SELECT 
    mr.id,
    mr.record_date,
    mr.location,
    'temperature' AS violated_metric,
    mr.temperature AS measured_value,
    ls.min_value AS allowed_min,
    ls.max_value AS allowed_max
FROM measurement_records mr
JOIN location_specs ls ON mr.location = ls.location AND ls.metric_name = 'temperature'
WHERE mr.temperature < ls.min_value OR mr.temperature > ls.max_value

UNION ALL

-- Get all out-of-spec pressure records
SELECT 
    mr.id,
    mr.record_date,
    mr.location,
    'pressure' AS violated_metric,
    mr.pressure AS measured_value,
    ls.min_value AS allowed_min,
    ls.max_value AS allowed_max
FROM measurement_records mr
JOIN location_specs ls ON mr.location = ls.location AND ls.metric_name = 'pressure'
WHERE mr.pressure < ls.min_value OR mr.pressure > ls.max_value

UNION ALL

-- Get all out-of-spec flow rate records
SELECT 
    mr.id,
    mr.record_date,
    mr.location,
    'flow_rate' AS violated_metric,
    mr.flow_rate AS measured_value,
    ls.min_value AS allowed_min,
    ls.max_value AS allowed_max
FROM measurement_records mr
JOIN location_specs ls ON mr.location = ls.location AND ls.metric_name = 'flow_rate'
WHERE mr.flow_rate < ls.min_value OR mr.flow_rate > ls.max_value;

If you want to prevent invalid data from entering the database entirely, use a trigger to validate before insert/update. Here's a MySQL example:

DELIMITER //
CREATE TRIGGER validate_measurement_before_insert
BEFORE INSERT ON measurement_records
FOR EACH ROW
BEGIN
    -- Validate temperature if provided
    IF NEW.temperature IS NOT NULL THEN
        SELECT min_value, max_value INTO @temp_min, @temp_max
        FROM location_specs
        WHERE location = NEW.location AND metric_name = 'temperature';
        
        IF NEW.temperature < @temp_min OR NEW.temperature > @temp_max THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Temperature out of spec for this location';
        END IF;
    END IF;
    
    -- Validate pressure if provided
    IF NEW.pressure IS NOT NULL THEN
        SELECT min_value, max_value INTO @press_min, @press_max
        FROM location_specs
        WHERE location = NEW.location AND metric_name = 'pressure';
        
        IF NEW.pressure < @press_min OR NEW.pressure > @press_max THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Pressure out of spec for this location';
        END IF;
    END IF;
    
    -- Validate flow rate if provided
    IF NEW.flow_rate IS NOT NULL THEN
        SELECT min_value, max_value INTO @flow_min, @flow_max
        FROM location_specs
        WHERE location = NEW.location AND metric_name = 'flow_rate';
        
        IF NEW.flow_rate < @flow_min OR NEW.flow_rate > @flow_max THEN
            SIGNAL SQLSTATE '45000' 
            SET MESSAGE_TEXT = 'Flow rate out of spec for this location';
        END IF;
    END IF;
END //
DELIMITER ;

-- Create a similar trigger for BEFORE UPDATE if you need to validate edits to existing records

Key Benefits of This Design #

• Maintainable: Update specs by editing the location_specs table instead of rewriting queries or triggers.
• Scalable: Add new metrics (e.g., humidity) by just adding a row to location_specs and a column to measurement_records.
• Clear: Separates raw data from business rules, making the schema easier to understand for other developers.

内容的提问来源于stack exchange，提问作者Vito