Hi I need to create an event system where user can set recurrent events (every week, month or year) what is the best mysql schema to store these events? Many thanks
A brief look at your post history indicates that this question is beneath your level of knowledge.
From your description, the granularity of your data is Day.
You need to store the Start Date, and the Reoccurrence Rate.
Your query for retrieving alarms would pull records where DAYOFWEEK, DAY, or MONTH AND DAY are equal to the start date, depending on the Reoccurrence Rate.
(Note: This would mean that events scheduled for the 31st would only occur in months with 31 days.)
Hi @m_hutley thanks for your help. So I only need to store the event once than. The only thing I’m not sure how to achieve is how to build the query depending on recurrence rate, could you please give me any example on that? Many thanks
Well, you tell me.
In regular plain english, explain to me how, given only the two pieces of information from the database:
11/07/2020,
Monthly"
And the third piece of information abstracted from the query (Today’s date.):
12/07/2020
Given those pieces of information, how can you tell whether or not today is an alert for this row?
1 Like
Hi, maybe I would select the start date and the reoccurence rate from database then analyse the result from select query and if the reoccurence rate is monthly I would check if the current day from current date is the same to the day from started date and if current month from today’s date is greater than the month from start date. I’m sure is not the right method though.
actually, it is
1 Like
Hi @r937 thanks for your reply. Know i ned to find a way to solve the problem when a month has 31 days as hilighetd by @m_hutley
Well, if someone sets a Monthly recurring event for the 31st, when should the event occur in the event of the month with 30 days?
What happens in February?
(This is why dates and times are such a pain to every computer programmer.)
Yes you are right, I found this library which could help https://github.com/simshaun/recurr
If you use one record to handle multiple subscription payment/cases keep two date fields for the subscription date, one for when it was initiated, and one for when it expires. This way handling subscription set up at the end of the month from the 29th becomes a piece of cake.
If you keep only one, the issue becomes what date to move it back to after you have reduced it for a month, i.e. if you reduced it to 28th on Feb, should it change to 29th, 30th, or 31st for March when you update the next subscription date? While when you have the date it was initiated, you know what date to move it back to, and can handle this easily.
I would not ever have advocated altering a date in the table; Rather, that the query should take all alerts that occur after the end of the month, and register them as a match on the last day of the month.
For just a Monthly query, it would be something like:
WHERE recurse = "M" AND MIN(DAY(start_date),LAST_DAY(CURDATE())) = DAY(CURDATE())
(the min-evaluation could be done as the select, also.)
There is nothing wrong with updating the content of a column in a table. Do you advocate the same for profile information as well? Only write to it once? What do you do when the user wants to make an update? Delete the record and make a new one?
If your database model has a clear layout, having a table dedicated to recurring events that should not be handled as “fire and forget” events, is not uncommon. Even if you offload these to a queue before processing, you want to have a master record that is updated as the queue process it.
In this case, utilizing a table for these recurring events, allow you to keep one record per unique recurring event a user setup, and have one column which contains the next process/expiration date, as well as last processed, etc. information.
Adding this denormalization to the recurring events makes it easy/cheap to get a hold of this information, without running expensive queries on log tables to get it.
If you have millions of rows in the recurring events table, this is not the best query to run if you want to get the result set back quickly.
I did not say the rows are immutable; just that the DATABASE should not be editing USER records to accomplish it’s AUTOMATED tasks. Please don’t put words in my mouth.
Which gets updated… when? What if your user doesn’t log onto your site every day? Or are you going to trigger a global update of the table every time every user touches the site? If you have control of the database server, perhaps you can set a recurring pattern; but most don’t. Perhaps you can use more tables for storing records about the last time you updated the table, pull a record, run the update query the first time a day someone logs on…? Is that the strategy?
I agree, it uses too many function calls. But it’s the best way to do it that I can think of. I fully acknowledge that i’m not a DBA. I await your schema and query set that can be run by a standard read/write user that does it better.
Hi @TheRedDevil thanks for your input, what I’m trying to achieve is not a subscription but an agenda with some reminders for things that have an expiry date and need renewal at some point.
Assuming it is the same event that can/will be triggered multiple times, it is not a bad idea to handle it similar to how you would with a subscription.
This is, of course, assuming that you want to keep them atomic, and not fire and forget (i.e. you want to ensure they are processed at each “event timer”).
Having the exact time set, and changing it as the event is processed is not bad architecture, and in many cases, it’s quick to set up, and effective when running against indexes.
In the event we are talking about a smaller amount of events, perhaps below/from 500,000 to 5,000,000 active events, you can also add them all to a table with the trigger date as the primary key.
In this scenario, if the user wants to add twelve events, each triggering at the end of the month, you add them all at once, one record per event. Then as each event fires, you delete the record from the table. Efficiently making it an atomic queue. Just make sure you have a failsafe, so it does not keep firing the same event, over and over again upon an error with a provider, etc.
Note.
If you do not have access to a dedicated database server with enough resources, the number of active events you can have without causing a slowdown in the query goes down.
In the same way, if you prewarm a cache (Redis etc.) with the records for the day and next day, you can have tens of millions of active events. You just need a daemon or cron that handles the prewarming once a day. Just keep in mind that you should also be able to add/remove from the cache if an event is added that should trigger the same day, or a user removes an event.
If that was what you meant, would it not have been better to have made that clear in the first place, instead of saying “would not ever have advocated altering a date in the table”.
?
The only time you could have the event be controlled/triggered by when a user visits the website is if the event is tied to the user, and it does not matter if it is triggered after the fact. I.e. it should only be triggered the next time the user access the website, no matter if it is tomorrow or next year. (In this case, this approach would work fine assuming the table has a composite key/index for the expire date which includes the user id before the expire date.)
Other than this you would preferrable use a daemon, or if you don’t want to write one, a cron job.
For the strategy, I already explained the most common one we apply to this problem, and it is having the table denormalized to contain helper data, which makes handling this simpler.
By having a dedicated trigger date, all you need to do is look for records that have just expired, and execute them. By utilizing composite key/index, you can make very effective queries even if there are other requirements than the date itself.
After executing the event, the expire date would be updated with the next time it should be executed, together with other information updates as times run, last processed date, etc.
Basically, we store information tied directly to the event denormalized, to make the cost of getting the information cheaper. Depending on the type of event, and the project requirements you can also store the exact event trigger information in an audit log as well. Though as mentioned above, in normal cases you avoid utilizing these logs for pulling any data other than when needed due to cost.
It is usually not a good idea to strongly argue against something if you are completely sure…
The query you provided will only work when up to a set number of records in the table (all depending on available hardware) before it will start to slow down. The reason is that it will cause a full table scan, and it will modify the date of every record in the table looking for a match.
If you instead have a date column that is set to the execution time, you do not need to modify it, and all you need to do is check for records that are lower than it and still active. This will allow the use of an index, keep in mind to write the query in such a way that it uses the index/composite index you want it to use.
If you want to write performance SQL, avoid writing any queries that need to modify a column in the WHERE section.
Hi after a search on Google I’ve found this article https://theeventscalendar.com/knowledgebase/k/recurring-events-event-series-in-events-calendar-pro/
This is a WordPress plugin and they store each single recurring event as a single row in the database. If the event doesn’t have an end date then they store re currencies for two years and then generate new events for another two years when the event itself is about to expire. Not sure how to create a daemon that does this job, maybe they use mysql event schedule? Many thanks
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.