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 nightfrom 07:00 to 08:00 >> plan is hotfrom 16:00 to 18:00 >> plan is hotfrom 18:00 to 22:00 >> plan is confortfrom 22:00 to 23:59 >> plan is night[...]SATURDAY:from 00:00 to 23:59 >> plan is confortNote 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 simpleSELECT * 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 :)
Modeling a database for a heater domotic app
database design;mysql
Your problem arises from the fact that your TEST table is not in NF1A 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.tempfrom    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_hourYou 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.