I am trying for a table design and logic for appointment similar to google calendar reminders.
Requirement is to store single and repeated reminders for multiple users and find reminders for the given time period so as to view in the calendar.
appointment_id, user_id, start_date, end_date, --> End date for the repeat reminder. Null means never ending start_time, end_time
appointment_id, frequency_type, --> D(Daily),W(Weekly),M(Monthly),Y(Yearly) interval, --> Every 3 days, Every 2 weeks, Every 4 Months, ... sunday, --> Weekly interval that happens on Sundays monday, tuesday, wednesday, thursday, friday, saturday, --> Weekly interval that happens on Saturdays on_day, --> On 5th day of each Month, ... on_week, --> On 1st week of each Month, On 2nd week of each month on_last_week, --> On last week of the month max_repeat. --> Maximum repeat time
Is there any better way to store this?
Now, I need to find the reminders for the given period so as to show in the calendar? Say, Find reminders between October 1st and 15th for user 5 and show them in calendar.
I am currently, fetching all the reminders for a user and then running a java code to find reminders b/w the period. Is there any way to get using SQL query?
The output I am looking for is,
reminder_id, user_id, name, Date, Start_time, End_time
For the daily reminder with no end date,
When I say, give me reminders b/w 10/15/21 and 10/12/21,
it has to return multiple entries since the daily reminder will occur everyday in the calendar.