0

Goal

Monitor records of temperatures for each room, daily.

What I have created so far

Firstly, this table shows the rooms and their minimum & maximum values of the temperatures they should meet.

CREATE TABLE `Rooms` (
    Id INT NOT NULL PRIMARY KEY auto_increment,
    RoomDescription VARCHAR(30) NOT NULL,
    MinTemperature DECIMAL(4,2) NOT NULL,
    MaxTemperature DECIMAL(4,2) NOT NULL
);

Secondly, in this table - I can see the ActualTemperature recorded, on what Date and for which RoomId.

CREATE TABLE `Records` (
    Id INT NOT NULL PRIMARY KEY auto_increment,
    DateRecorded TIMESTAMP,
    RoomId INT NOT NULL,
    ActualTemperature DECIMAL(4,2) NOT NULL,
    FOREIGN KEY (RoomId) REFERENCES `Rooms`(Id)
);

And this is where I am kind of stuck.

The record that didn't meet it's min. or max. temperature, it needs to be Re-Tested until it's a valid value (between min. & max. temperature). Bearing in mind, I need to keep a hold of the failed records.

I need a way of keeping track of failed records, it's re-tested values and how many times it's been re-tested until it became a valid value. I am unsure, if it's necessarily to create a separate table? Or can I modify any of my existing tables to achieve my goal.

0
3

One way to do this would be to expand your Records table to include a field that states whether something is valid or not:

CREATE TABLE `Records` (
    Id INT NOT NULL PRIMARY KEY auto_increment,
    DateRecorded TIMESTAMP,
    RoomId INT NOT NULL,
    ActualTemperature DECIMAL(4,2) NOT NULL,
    IsValid BIT NOT NULL DEFAULT 1,
    FOREIGN KEY (RoomId) REFERENCES `Rooms`(Id)
);

From here, you can use a pair of triggers that will check the validity of the temperature for the room, and set the IsValid value to 1 if it's valid, or 0 if it's not:

DELIMITER ;;
DROP TRIGGER IF EXISTS `before_records_insert`;;
CREATE TRIGGER `before_records_insert`
BEFORE INSERT ON `Records`
   FOR EACH ROW
 BEGIN
    SET new.`IsValid` = (SELECT CASE WHEN new.`ActualTemperature` BETWEEN rm.`MinTemperature` AND rm.`MaxTemperature` THEN 1 ELSE 0 END as IsValid 
                           FROM `Rooms` rm 
                          WHERE rm.`id` = new.`RoomId`);
   END
;;
DROP TRIGGER IF EXISTS `before_records_update`;;
CREATE TRIGGER `before_records_update`
BEFORE UPDATE ON `Records`
   FOR EACH ROW
 BEGIN
    SET new.`IsValid` = (SELECT CASE WHEN new.`ActualTemperature` BETWEEN rm.`MinTemperature` AND rm.`MaxTemperature` THEN 1 ELSE 0 END as IsValid 
                           FROM `Rooms` rm 
                          WHERE rm.`id` = new.`RoomId`);
   END
;;
DELIMITER ;

Now let's test this with some dummy data. First the Rooms:

INSERT INTO `Rooms` (`RoomDescription`, `MinTemperature`, `MaxTemperature`)
VALUES ('Kitchen', 23, 29),
       ('Living Room', 21, 25),
       ('Server Closet', 15, 20);

Now the Records:

INSERT INTO `Records` (`DateRecorded`, `RoomId`, `ActualTemperature`)
VALUES ('2021-09-24 01:00:00', 3, 13.12),
       ('2021-09-24 01:01:00', 3, 15.44),
       ('2021-09-24 01:02:00', 3, 15.25),
       ('2021-09-24 01:03:00', 3, 17.33),
       ('2021-09-24 01:04:00', 3, 19.74),
       ('2021-09-24 01:05:00', 3, 21.31),
       ('2021-09-24 01:06:00', 3, 22.14),
       ('2021-09-24 01:07:00', 3, 21.05),
       ('2021-09-24 01:08:00', 3, 20.45),
       ('2021-09-24 01:09:00', 3, 18.91),
       ('2021-09-24 01:10:00', 3, 16.61);

Now let's check that everything is good:

SELECT * FROM `Records`;

Results:

id DateRecorded RoomId ActualTemperature IsValid
1 2021-09-24 01:00:00 3 13.12 0
2 2021-09-24 01:01:00 3 15.44 1
3 2021-09-24 01:02:00 3 15.25 1
4 2021-09-24 01:03:00 3 17.33 1
5 2021-09-24 01:04:00 3 19.74 1
6 2021-09-24 01:05:00 3 21.31 0
7 2021-09-24 01:06:00 3 22.14 0
8 2021-09-24 01:07:00 3 21.05 0
9 2021-09-24 01:08:00 3 20.45 0
10 2021-09-24 01:09:00 3 18.91 1
11 2021-09-24 01:10:00 3 16.61 1

Looking back at the core of your question:

I need a way of keeping track of failed records, it's re-tested values and how many times it's been re-tested until it became a valid value.

You can now do all of this thanks to a single additional field and a pair of triggers.

Some Items to Note:

  • the IsValid field cannot be directly manipulated with an INSERT or UPDATE. The triggers, as they're written, will always override whatever value is passed in IsValid
  • the IsValid field will not auto-update if the MinTemperature or MaxTemperature values are changed in the future for seasonal variations

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.