0

I'm looking for the efficient way to store week days and times of each event. How do I achieve the following result

Events table

id name dates rel_id
1 hello world Su 14:56:59, Mo 14:56:59, We 14:56:59, Th 14:56:59, Fr 14:56:59, Sa 14:56:59 1

Update

Is it correct to use an enum list like sa','su','mo','tu','we','th','fr' and then add a record for each day, so each rel_id will have 7 records!

Christian
  • 53
  • 1
  • 8
  • 3
    You would store the dates separately from the Events in a table with a foreign key to Event Id, with 1 row per date. – Stu May 07 '22 at 08:21
  • 1
    In addition , use proper date datatype – Ergest Basha May 07 '22 at 08:34
  • @Stu This event will take a place on each day but with different time, so the user has to select his schedule for a year? it doesn't make sense. I want him to select days of week with time to each day and that will cycle to forever! – Christian May 07 '22 at 09:08
  • 1
    @Christian the problem lies more in https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad in databases you store the data not in that way, as you can't retrieve and fetch them easily – nbk May 07 '22 at 10:01
  • @nbk don't worry about the problems that are mentioned in that question – Christian May 07 '22 at 11:42
  • @Christian i worry about that you can't access the data and update them without a massive amount of programming. the link is posted here often, so that people may have a look at the perspective of professionals – nbk May 07 '22 at 11:45
  • @nbk Is it correct to use an enum list like sa','su','mo','tu','we','th','fr' and then add a record for each day from the list? – Christian May 07 '22 at 11:53
  • @Stu I've updated my question please take a look. – Christian May 07 '22 at 11:57
  • you have a timetable with all dates relvant, and you have a linking table, that points from event to the time timetable, so you have a normalized database, the time table could be filles prior, but it is not really necessary as not all days have events and many events can be on the same day, you can use joins to get all information – nbk May 07 '22 at 11:57
  • you would *not* store a "day" in addition to the date, you simply use a *datetime* type, if you need to retrieve the day there is a function to do exactly that. – Stu May 07 '22 at 12:09

1 Answers1

0

To be honest, store every event by itself.
The reason

  1. Su 14:56:59 - this can be any Sunday out of 52 Sundays a year - not very efficient from my point of view
  2. If you ever need a report of some sort, you would spend more time extracting dates from a string compared to just read the DB table rows
Guido Faecke
  • 644
  • 1
  • 3
  • 7
  • See my comment https://stackoverflow.com/questions/72150718/how-do-i-set-up-an-sql-table-to-save-week-days-and-time-in-each-event-record#comment127481697_72150718 – Christian May 07 '22 at 09:09