0

In my database I manage timetables like this:

timetables table
id - int (primary key)
start_time - int (minutes)
end_time - int (minutes)
week_day - int (day number: 1 for Monday, 7 for Sunday)

the id field is foreign key in other tables
since if a timetable is repeated for all days of the week and I have to store 7 rows in timetables and each other table with the foreign key
what about instead storing the week_day fields as an int of up to 7 digits (1234567) and query it as a string:

... WHERE week_day LIKE '%3%' /*wednesday*/

are there any cons in doing this?

UPDATE
my intention is to change timetables table
from:

id  shop_id start_time  end_time    week_day (int)
1   1       480         720         1           /*shop1 08:00 - 12:00 monday*/
2   1       480         720         2           /*shop1 08:00 - 12:00 tuesday*/
3   1       480         720         3           /*shop1 08:00 - 12:00 wednesday*/
4   1       900         1140        7           /*shop1 15:00 - 19:00 sunday*/

into:

id  shop_id start_time  end_time    week_day (int)
1   1       480         720         123         /*shop1 08:00 - 12:00 monday,tuesday,wednesday*/
2   1       900         1140        7           /*shop1 15:00 - 19:00 sunday*/

example query:

SELECT * FROM timetables WHERE shop_id=1 AND week_day LIKE '%3%'

OTHER POSSIBLE SOLUTION

id  shop_id start_time  end_time    monday  tuesday wednesday   thursday    friday  saturday    sunday
1   1       480         720         1       1       1           NULL        NULL    NULL        NULL    /*shop1 08:00 - 12:00 monday,tuesday,wednesday*/
2   1       900         1140        NULL    NULL    NULL        NULL        NULL    NULL        1       /*shop1 15:00 - 19:00 sunday*/
Michele
  • 31
  • 6
  • write the queries that retrieve this data for both approaches and hopefully an answer will be obvious. – danblack Jun 24 '20 at 07:37
  • What is `start_time` and `end_time` - And how would you "merge" 7 of them into a single row? – Paul Spiegel Jun 24 '20 at 07:42
  • @danblack and @PaulSpiegel `start_time` and `end_time` are the same for each timetable e.g. 08:00 (480 minutes) and 12:00 (720 minutes) so instead of repeating them for each week day I'd concat them in a single row – Michele Jun 24 '20 at 07:53
  • IMHO both designs look bad. And it's still not clear, what you are storing there. – Paul Spiegel Jun 24 '20 at 08:01
  • @PaulSpiegel please see edit, I'm storing shop timetables, how would you do? – Michele Jun 24 '20 at 08:17
  • Your statements (in question and comments) are not consistent. You write `start_time/end_time` are always the same, but in the sample data it's not. – Paul Spiegel Jun 24 '20 at 08:24
  • Most drawbacks of the second approach are descibed here: https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad – Paul Spiegel Jun 24 '20 at 08:30
  • @PaulSpiegel sorry I meant `start_time/end_time` are always the same **for each timetable** which can be repeated for x days of the week, regarding those drawbacks the `INSERT INTO timetables` query is managed by a php script which always writes a maximum of 7 digits from 1 to 7, anyway creating one BIT(1)NULL field for each of the 7 week days would be a better approach? – Michele Jun 24 '20 at 08:53
  • If in doubt - use the most normalized design. Do you have any troubles with the current design? – Paul Spiegel Jun 24 '20 at 08:55
  • @PaulSpiegel the only trouble I see in the current design is the redundancy of fields `start_time/end_time` and up to 6 more rows for timetables in all days of the week and storing the list I could keep 1 field instead of 7 – Michele Jun 24 '20 at 09:05

0 Answers0