mysql - 在 MySQL 中存储每日事件的开始和结束时间

Question

我目前有一个事件表，用于存储事件以及事件在一周中的哪几天发生，该表看起来像这样。

EventID  Title       Mon Tue Wed Thu Fri Sat Sun
1        EventName   1   1   0   0   1   0   0   

我希望能够为每个事件添加开始和结束时间，通常我只会添加两个额外的列，例如开始时间和结束时间，但一周中每一天的开始和结束时间通常都不同。

设置表格以实现此目的的最佳方法是什么？

谢谢

Answer 1

You should add Table Event_Details with below schema

EventDetailsID
EventID (Foreign Key)
Mon_Timings
Tue_Timings
Wed_Timings
Thrus_Timings
Fri_Timings
Sat_Timings
Sun_Timings

Sample data entry for the Event_Details table should be like below


EventDetailsID = 1
EventID  = 1
Mon_Timings = 08:00-20:00  
Tue_Timings = 09:00-19:00
Wed_Timings = NULL
Thrus_Timings = NULL
Fri_Timings = 10:30-21:30
Sat_Timings = NULL
Sun_Timings = NULL

At time of saving data you can have StartTime-EndTime format
and when you want to retrieve you can split by '-'

This will have less number of rows , which will retrieve data faster