Modeling a database for a heater domotic app

Question

Imagine a domotic webapp, to command a heater.

You could setup N plans, for a plan I mean a similar table:

+----+---------+--------------------+
| id | name    | target_temperature |
+----+---------+--------------------+
| 1  | confort | 19                 |
+----+---------+--------------------+
| 2  | hot     | 22                 |
+----+---------+--------------------+
| 3  | night   | 17                 |
+----+---------+--------------------+


PSEUDOCODE
(if plan is confort && temperature<19 then heater on)

Imagine you would setup a similar scenario:

MONDAY:

from 00:00 to 07:00 >> plan is night
from 07:00 to 08:00 >> plan is hot
from 16:00 to 18:00 >> plan is hot
from 18:00 to 22:00 >> plan is confort
from 22:00 to 23:59 >> plan is night

[...]

SATURDAY:

from 00:00 to 23:59 >> plan is confort

Note that on monday from the 08:00 to the 18:00 there is no plan. Software will be setup OFF the heater.

How you would design the database of the days? A singular table with 7 colums (from Monday to Sunday) and every columns N rows? I don't like it, because saturday is totally different from monday.

A singular table with 24 (or 48) rows and 7 column? WHere the rows goes from 00:00 to 23:59

+-----+-------+-------+---------+-----+---------+-------+
| id  | hour  | M     | T       | […] | S       | S     |
+-----+-------+-------+---------+-----+---------+-------+
| 1   | 00:00 | night | hot     |     | night   | night |
+-----+-------+-------+---------+-----+---------+-------+
| 2   | 00:30 | night | night   |     | confort | null  |
+-----+-------+-------+---------+-----+---------+-------+
| 3   | 01:00 | night | night   |     | confort | null  |
+-----+-------+-------+---------+-----+---------+-------+
| 4   | 01:30 | hot   | confort |     | confort | null  |
+-----+-------+-------+---------+-----+---------+-------+
| 5   | 02:00 | hot   | hot     |     | confort | null  |
+-----+-------+-------+---------+-----+---------+-------+
| 6   | 02:30 | null  | null    |     | confort | hot   |
+-----+-------+-------+---------+-----+---------+-------+
| […] |       |       |         |     |         |       |
+-----+-------+-------+---------+-----+---------+-------+
| 50  | 23:30 | hot   | hot     |     | null    | null  |
+-----+-------+-------+---------+-----+---------+-------+
| 51  | 23:59 | night | night   |     | night   | null  |
+-----+-------+-------+---------+-----+---------+-------+

And where the NULL means "Hey! Put the heater OFF!" ...

So, query to get the CURRENT APPLICABLE PLAN could be a simple

SELECT * FROM TEST WHERE hour = 

(

SELECT MAX(hour) FROM TEST WHERE hour < CURTIME()

)

But in this case the difficult could be select the right day! How select only the "Monday" column?

Thank you :)

Answer 1

Your problem arises from the fact that your TEST table is not in NF1

A more normalized model will be something like this:

The table you name TEST I call SCHEDULE_DETAIL, data woul dbe like this:

The query would be like this:

select
    p.plan_id,
    p.name,
    p.temp
from
    schedule s join
    schedule_detail sd on (sd.sch_id=sd.sch_id) join
    plan p on (sd.plan_id=p.plan_id) join
    day_of_week d on (sd.dow_id=d.dow_id)
where
    s.sch_id=1 and --we are using schedule 1 this time of year
    d.dow_id=1 and --monday, we can substitute the literal with a
                       --function that returns day of week (0-6)
    curtime() >= sd.start_hour and curtime() <= sd.end_hour;

NOTE:

• Some restrictions cannot be modeled so you will have to write a sanity procedure to check that no two rows of SCHEDULE_DETAIL for the same schedule_id have time overlap. In your example the first two rows for monday have a time overlap (one ends at 07:00 and the next one begins at 07:00, I corrected the time overlap in the sample data I provide)

• A check constraint must be added so start_hour < end_hour

• You can have different schedules and switch them during the year as you wish.

• You can create a view from that query (minus the where conditions) so you query that view instead of making the joinds all the time.

• When the query returns no rows, switch off the heater.