0

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.

Table:- appointment

appointment_id,
user_id,
start_date,
end_date,   --> End date for the repeat reminder. Null means never ending
start_time,
end_time

Table:- appointment_frequency

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?

http://sqlfiddle.com/#!9/b540da/5

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.