Best way to store events in a (My) SQL database

Question

I am trying to decide how best to store events in a MySQL database. They should be as flexible as possible and be able to present "individual events" (starts at a specific time, you do not need to end time), "all day" and "multi-day" events, recurring events, recurring events throughout the day, perhaps events like "3rd Saturday of the month", etc.

Please offer some tried and tested database schemas.

Answer 1

Table: Events

• StartTime (dateTime)
• EndTime (dateTime) null for unlimited time
• RepeatUnit (int) null = noRepeat, 1 = , 2 = , 3 = , 4 = OfMonth, 5 = , 6 =
• NthDayOfMonth (int)
• RepeatMultiple (int), , RepeatUnit 3, 2 .
• Id - StartTime .
• () - , ,

. . , , , . ... NthDayOfMonth .

, , , , .

Answer 2

, iCalendar ( ). RFC 2445 , Apple Inc. icalendar, , .

( / )

event (event_id, # primary key
       dtstart,
       dtend,
       summary,
       categories,
       class,
       priority,
       summary,
       transp,
       created,
       calendar_id, # foreign key
       status,
       organizer_id, # foreign key
       comment,
       last_modified,
       location,
       uid);

calendar_id

calendar(calendar_id, # primary key
         name);

organizer_id ( , ..)

organizer(organizer_id, # primary key
          name); 

, ,

,

Answer 3

. (table repeatatevent) ( ). . , . , , , . , -.

, . . SQL -, .

create table repeatevent (
id int not null auto_increment, 
type int, // 0: daily, 1:weekly, 2: monthly, ....
starttime datetime not null, // starttime of the first event of the repetition
endtime datetime, // endtime of the first event of the repetition
allday int, // 0: no, 1: yes
until datetime, // endtime of the last event of the repetition
description varchar(30)
)

create table event (
id int not null auto_increment,
repeatevent null references repeatevent, // filled if created as part of a repeating event
starttime datetime not null,
endtime datetime,
allday int,
description varchar(30)
)

Answer 4

Does cron do the same ? Record start and end times this way.

Answer 5

Use datetime and mysql built into the NOW () function. Create a record at the start of the process, update the column that tracks the end time of the process.