CODEWITHSUNDEEP
mysqldatabase
Amar
Amar

Reputation: 971

Design tables which can store different values and respective indicators

Hi I need help on designing database table for the following rules :

There is a need of a storage of data for the weather of each day.It needs to store either 1 or 2 values of weather as defined by the system.

The following parameter needs to be applied while designing the table.

Thanks in advance.

Upvotes: 0

Views: 226

Answers (1)

PerlDuck
PerlDuck

Reputation: 5730

If you are using MySQL 5.7 you could use GENERATED COLUMNS:

create table wheather (
  temp int,
  hot  int generated always as (if(temp>30,1,0)) virtual,
  cold int generated always as (if(temp<20,1,0)) virtual
);

insert into wheather (temp) values (10), (20), (30), (40);
select * from wheather;

Gives:

temp    hot    cold
-------------------
10      0      1
20      0      0
30      0      0
40      1      0

If using earlier versions of MySQL, like 5.5, use a view that does the same.

Update: From the comments I learned that the thresholds vary for different locations. Given that, I suggest a second table that holds the thresholds for each location and then join the two tables:

create table wheather (
    location char(20),
    temp     int
);

insert into wheather (location, temp) values 
    ('Florida', 10), ('Florida', 20), ('Florida', 30), ('Florida', 40),
    ('Alaska', 10),  ('Alaska', 20),  ('Alaska', 30),  ('Alaska', 40);

create table thresholds (
    location        char(20),
    low             int,
    high            int,
    inrange_lower   int,
    inrange_upper   int,
    outrange_lower  int,
    outrange_upper  int
);

insert into thresholds values 
    ('Florida', 30, 60, 35, 45, 20, 50),        
    ('Alaska',  10, 30, 15, 25,  5, 40);

select w.location,
       w.temp, 
       if(w.temp < t.low,'yes','no') as cold,
       if(w.temp > t.high,'yes','no') as hot,
       if(w.temp between t.inrange_lower and t.inrange_upper,'yes','no') as in_range,
       if(w.temp < t.outrange_lower or w.temp > t.outrange_upper,'yes','no') as out_of_range
  from wheather w left join thresholds t on w.location=t.location;

Gives:

+----------+------+------+-----+----------+--------------+
| location | temp | cold | hot | in_range | out_of_range |
+----------+------+------+-----+----------+--------------+
| Florida  |   10 | yes  | no  | no       | yes          |
| Florida  |   20 | yes  | no  | no       | no           |
| Florida  |   30 | no   | no  | no       | no           |
| Florida  |   40 | no   | no  | yes      | no           |
| Alaska   |   10 | no   | no  | no       | no           |
| Alaska   |   20 | no   | no  | yes      | no           |
| Alaska   |   30 | no   | no  | no       | no           |
| Alaska   |   40 | no   | yes | no       | no           |
+----------+------+------+-----+----------+--------------+
8 rows in set (0.00 sec)

The code works, but is simplified, i.e. it lacks PKs, INDEXEs, FK constraints, and a DATE column. And I may have gotten the actual values and/or comparisons wrong, but it may point you to a direction.

Of course you can create a view for the SELECT statement. If you really want to store the values, then I suggest four extra columns in the WHEATHER table (cold/hot/in_range/out_of_range) and populate them with an INSERT trigger with the values from the THRESHOLD table. This way you can change the thresholds (in THRESHOLDS) later without changing the values in WHEATHER. For example, if you'd created your database 50 years ago, then 50 °F would be considered hot whereas meanwhile 55 °F is hot (due to climate change). It depends on your use case. The view gives "life updates", the trigger gives "historical values".

Upvotes: 2

Related Questions