I have an events table in which various events could be happening in time slots each day. What I mean is
each event has many time slots each day of the week. For example, event A can be have slots such as 1-2 pm, 5-6 pm on Mondays, 2-4 pm, 5-7 pm on Tuesdays etc.
What's a good way to structure this in postgres taking into account that queries could be on events by timings as well. For example,
find all events on Monday which occur between 5 - 7 pm.
I was thinking of having a second table with event id as the first column, day of the week in the second column and then it's timing in the third column. So events having multiple timings in a day would be stored in individual rows for each timing on each day.
Alternatively, I was also thinking of a table with first two columns same as above, and a third column of array of timings, so events having multiple timings in a day would be stored in just one row for each day.
Which one is better or is there an even better way? If the former is better, in what scenario is use of arrays in databases useful? IMO, anything that can be done with arrays can be done with splitting tables or storing in different rows which are rather helpful when querying.
Also, how would I go about storing time such as 5-6 pm etc in the databse? I've a fair idea about storing the day of the week by using 7 bit boolean value. So Sunday would be 0000001, Sunday and Monday would be 0000011 etc, but how would I store the time ranges?