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:

enter image description here

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

enter image description here

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.

• sineverba: I did not understand your SCHEDULE table. What's contain NAME column? Thank you very much ;)...
• sineverba: Ops. maybe schedule could be "from may to september leave the heater off totally" and "from Jan, 1st, to Jan 6th I'm on the snow, so start the heater at 12°C? To defrozen it?"
• Tulains Córdova: Just a human readable description for your schedule, like 'AUTUM' or 'NO-ONE-HOME'. So users can select one by a name from a GUI.
• Tulains Córdova: You coud improve the schedule table by adding start_date and end_date, although that I did not contenplate.
• sineverba: Genius answer. If you don't offend, I'll accept in some hour, just to see if someone has others ideas. But, your design is very well, IMHO ;)