MYSQL - Data Model design for reminders

Question

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.

Answer 1

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:

id
subject
note
client_id
due_at
created_at
is_deleted

The note and due_at values would be optional.

For the ReminderRepeat table, you can probably go a bit overboard if you really want to ...

id 
reminder_id
start_at
until_at
type
remind_at
on_sunday
on_monday
on_tuesday
on_wednesday
on_thursday
on_friday
on_saturday

start_at would default to today and until_at would be NULL to allow repeats until "the end of time". The type could be any of onetime,daily,weekly,monthly,yearly and so on. The on_ values would let you say "Weekly on Monday, Thursday, and Friday". By having the reminder_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:

id
reminder_id
user_id
status
event_at

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 in Reminder to keep lookups clean.

Mind you, I do tend to overthink these things before the first line of code is even written ... 🤔