日历循环/重复事件 - 最佳存储方法 [英] Calendar Recurring/Repeating Events - Best Storage Method
问题描述
我正在建立自订活动系统，如果您有重复的活动，如下所示：
活动A从2011年3月3日起每4天重复一次
或
事件B从2011年3月1日起每周二重复一次b
$ b
如何以一种简单的方式存储在数据库中。
存储简单重复模式
对于我的基于PHP / MySQL的日历，我想尽可能高效地存储重复/重复的事件信息。我不想有大量的行，我想轻松查找在特定日期发生的所有事件。
以下方法很好存储以规则间隔发生的重复信息，例如每天，每天，每周，每月，每年等等。这也包括每个星期二和星期四类型模式，因为它们
假设我有两个表，一个叫 events
ID NAME
1样例事件
2另一个事件
像这样： events_meta p>
ID event_id meta_key meta_value
1 1 repeat_start 1299132000
2 1 repeat_interval_1 432000
repeat_start是没有时间作为unix时间戳的日期，repeat_interval是间隔之间的秒数（432000是5天）。
repeat_interval_1与ID 1的repeat_start一起使用。因此，如果我有一个事件在每个星期二和每个星期四重复，repeat_interval将是604800（7天）将是2 repeat_starts和2 repeat_intervals。该表格如下所示：
ID event_id meta_key meta_value
1 1 repeat_start 1298959200 - 重复
2 1 repeat_interval_1 604800
3 1 repeat_start 1299132000 - 这是星期四重复
4 1 repeat_interval_3 604800
5 2 repeat_start 1299132000
6 2 repeat_interval_5 1 - - 使用1作为值给我们一个只发生一次的事件
然后，如果你有一个日历每天循环，抓住当天的事件，查询将如下所示：
SELECT EV。*
FROM`events` EV
RIGHT JOIN`events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN`events_meta` EM2 ON EM2 .`meta_key` = CONCAT（'repeat_interval_'，EM1.`id`）
WHERE EM1.meta_key ='repeat_start'
AND（
（CASE（1299132000 - EM1.`meta_value`）
WHEN 0
THEN 1
ELSE（1299132000 - EM1.`meta_value`）
END
）/ EM2.`meta_value`
）= 1
LIMIT 0，30
更换 {current_timestamp} 使用当前日期的unix时间戳记（减去时间，因此小时，分钟和秒的值将设置为0）。
希望这将有助于还有其他人！
存储复杂重复模式
此方法更适合存储复杂模式，例如
事件A从2011年3月3日起每月的第三天重复
或
活动A重复从2011年3月11日开始的第二个星期
我建议与上述系统结合使用，这个表格应该像：
ID NAME
1示例事件
2另一个事件
像这样： events_meta p>
ID event_id meta_key meta_value
1 1 repeat_start 1299132000 - 2011年3月3日
2 1 repeat_year_1 *
3 1 repeat_month_1 *
4 1 repeat_week_im_1 2
5 1 repeat_weekday_1 6
repeat_week_im 表示当前月份的星期，可能介于1到5之间。 repeat_weekday 。
现在假设你在一周中的几天要在日历中创建月视图，您可以撰写如下所示的查询：
SELECT EV 。 *
FROM`events` AS EV
JOIN`events_meta` EM1 ON EM1.event_id = EV.id
AND EM1.meta_key ='repeat_start'
LEFT JOIN`events_meta` EM2 ON EM2.meta_key = CONCAT（'repeat_year_'，EM1.id）
LEFT JOIN`events_meta` EM3 ON EM3.meta_key = CONCAT（'repeat_month_'，EM1.id）
LEFT JOIN`events_meta` EM4 ON EM4.meta_key = CONCAT（'repeat_week_im_'，EM1.id）
LEFT JOIN`events_meta` EM5 ON EM5.meta_key = CONCAT（'repeat_weekday_'，EM1.id）
WHERE（
EM2.meta_value = 2011
OR EM2.meta_value ='*'
）
AND（
EM3.meta_value = 4
OR EM3.meta_value ='*'
）
AND（
EM4.meta_value = 2
OR EM4.meta_value ='*'
）
AND（
EM5.meta_value = 6
OR EM5.meta_value ='*'
）
AND EM1.meta_value> = {current_timestamp}
LIMIT 0，30
结合上述方法可以合并覆盖大多数重复/重复的事件模式。如果我错过了任何内容，请发表评论。
I am building a custom events system, and if you have a repeating event that looks like this:
Event A repeats every 4 days starting on March 3, 2011
or
Event B repeats every 2 weeks on Tuesday starting on March 1, 2011
How can I store that in a Database in a way that would make it simple to lookup. I don't want performance issues if there are a large number of events, and I have to go through each and every one when rendering the calendar.
Storing "Simple" Repeating Patterns
For my PHP/MySQL based calendar, I wanted to store repeating/recurring event information as efficiently as possibly. I didn't want to have a large number of rows, and I wanted to easily lookup all events that would take place on a specific date.
The method below is great at storing repeating information that occurs at regular intervals, such as every day, every n days, every week, every month every year, etc etc. This includes every Tuesday and Thursday type patterns as well, because they are stored separately as every week starting on a Tuesday and every week starting on a Thursday.
Assuming I have two tables, one called events like this:
ID NAME
1 Sample Event
2 Another Event
And a table called events_meta like this:
ID event_id meta_key meta_value
1 1 repeat_start 1299132000
2 1 repeat_interval_1 432000
With repeat_start being a date with no time as a unix timestamp, and repeat_interval an amount in seconds between intervals (432000 is 5 days).
repeat_interval_1 goes with repeat_start of the ID 1. So if I have an event that repeats every Tuesday and every Thursday, the repeat_interval would be 604800 (7 days), and there would be 2 repeat_starts and 2 repeat_intervals. The table would look like this:
ID event_id meta_key meta_value
1 1 repeat_start 1298959200 -- This is for the Tuesday repeat
2 1 repeat_interval_1 604800
3 1 repeat_start 1299132000 -- This is for the Thursday repeat
4 1 repeat_interval_3 604800
5 2 repeat_start 1299132000
6 2 repeat_interval_5 1 -- Using 1 as a value gives us an event that only happens once
Then, if you have a calendar that loops through every day, grabbing the events for the day it's at, the query would look like this:
SELECT EV.*
FROM `events` EV
RIGHT JOIN `events_meta` EM1 ON EM1.`event_id` = EV.`id`
RIGHT JOIN `events_meta` EM2 ON EM2.`meta_key` = CONCAT( 'repeat_interval_', EM1.`id` )
WHERE EM1.meta_key = 'repeat_start'
AND (
( CASE ( 1299132000 - EM1.`meta_value` )
WHEN 0
THEN 1
ELSE ( 1299132000 - EM1.`meta_value` )
END
) / EM2.`meta_value`
) = 1
LIMIT 0 , 30
Replacing {current_timestamp} with the unix timestamp for the current date (Minus the time, so the hour, minute and second values would be set to 0).
Hopefully this will help somebody else too!
Storing "Complex" Repeating Patterns
This method is better suited for storing complex patterns such as
Event A repeats every month on the 3rd of the month starting on March 3, 2011
or
Event A repeats Friday of the 2nd week of the month starting on March 11, 2011
I'd recommend combining this with the above system for the most flexibility. The tables for this should like like:
ID NAME
1 Sample Event
2 Another Event
And a table called events_meta like this:
ID event_id meta_key meta_value
1 1 repeat_start 1299132000 -- March 3rd, 2011
2 1 repeat_year_1 *
3 1 repeat_month_1 *
4 1 repeat_week_im_1 2
5 1 repeat_weekday_1 6
repeat_week_im represents the week of the current month, which could be between 1 and 5 potentially. repeat_weekday in the day of the week, 1-7.
Now assuming you are looping through the days/weeks to create a month view in your calendar, you could compose a query like this:
SELECT EV . *
FROM `events` AS EV
JOIN `events_meta` EM1 ON EM1.event_id = EV.id
AND EM1.meta_key = 'repeat_start'
LEFT JOIN `events_meta` EM2 ON EM2.meta_key = CONCAT( 'repeat_year_', EM1.id )
LEFT JOIN `events_meta` EM3 ON EM3.meta_key = CONCAT( 'repeat_month_', EM1.id )
LEFT JOIN `events_meta` EM4 ON EM4.meta_key = CONCAT( 'repeat_week_im_', EM1.id )
LEFT JOIN `events_meta` EM5 ON EM5.meta_key = CONCAT( 'repeat_weekday_', EM1.id )
WHERE (
EM2.meta_value =2011
OR EM2.meta_value = '*'
)
AND (
EM3.meta_value =4
OR EM3.meta_value = '*'
)
AND (
EM4.meta_value =2
OR EM4.meta_value = '*'
)
AND (
EM5.meta_value =6
OR EM5.meta_value = '*'
)
AND EM1.meta_value >= {current_timestamp}
LIMIT 0 , 30
This combined with the above method could be combined to cover most repeating/recurring event patterns. If I've missed anything please leave a comment.
这篇关于日历循环/重复事件 - 最佳存储方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
