I need some help on reminder data model design with repeat.
Admin can set reminder for any client. Like, a reminder for calling as client on so and so date, or mailing a client on so and so date. One reminder can be assigned to multiple user so that anybody can react to it.
Tables:- reminder_repeat
repeat_id
name --> possible values ( Daily, Weekly, Monthly, Yearly )
Tables:- reminder
reminder_id
name
reminder_date
client_id
repeat_id
reminder_status --> 'O' for Open and 'C' for Closed
Table:- reminder_assigned_user
reminder_id
user_id
Here, Logged in user will be able to see the reminders assigned and react to that and mark the status as Completed. I need to make sure that non more than one user is reacting for the same reminder.
Not sure, How to handle the repeat as we need to maintain the status which will mark the reminder as completed. One approach I can think of is to run a batch Job every night which will add entry for that day if there is any repeat.
Best Answer
Reminder systems seem simple at first, but can be wonderfully complex as you think through all the things modern reminder apps are capable of.
One way to approach the problem would be with the use of a “transaction” table that can contain a number of types. By doing this, a person could “take” a reminder, which would let their colleagues know that something is in progress but not yet complete. This would also make it possible to determine whether a task was completed on a given day, or whether something was started, but not marked as complete.
For the
Reminder
table, something like this might work:The
note
anddue_at
values would be optional.For the
ReminderRepeat
table, you can probably go a bit overboard if youreally
want to …start_at
would default to today anduntil_at
would beNULL
to allow repeats until “the end of time”. Thetype
could be any ofonetime,daily,weekly,monthly,yearly
and so on. Theon_
values would let you say “Weekly on Monday, Thursday, and Friday”. By having thereminder_id
here you can allow edits to the repeat schedule to be tracked over time.Then there’s the transaction table, which would be a simple:
With this you can query the transaction table to determine if a reminder is done. If the reminder is to run once (or for a limited period of time), then you can update the
is_deleted
value inReminder
to keep lookups clean.Mind you, I do tend to overthink these things before the first line of code is even written … ?