3

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 :)

2

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.

  • I did not understand your SCHEDULE table. What's contain NAME column? Thank you very much ;)... – sineverba Aug 5 '16 at 17:44
  • 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?" – sineverba Aug 5 '16 at 17:46
  • 1
    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 Aug 5 '16 at 17:46
  • 1
    You coud improve the schedule table by adding start_date and end_date, although that I did not contenplate. – Tulains Córdova Aug 5 '16 at 17:47
  • 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 ;) – sineverba Aug 5 '16 at 17:49

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.