Best way to store status flags

Question

Looking for the best way to store flags for the following scenario.

My application has to send certain number of notifications or reminders to users in a specific interval.I am planning to write a batch job to do it. To avoid resending of reminders I want to have some flags.

Example case: Send reminders on 2nd, 5th and 10th day from which the user logged in to the application.

The number of reminders might increase or decrease in the future. I would like to know what is the best way to store the flags - Is it better to store it as a INT and have binary equivalents used for internal representations or use one column for each interval(like one TINYINT(1) for 2nd day, one for 5th and so on...)

The storage space is not a constraint, I am just looking for the best practices across the industry.

Answer 1

Generally you would save yourself a lot of future headaches by going with several columns instead of a combined one.

But consider that you are sending several reminders to a user, therefore you have a 1:n relationship in your data. This calls for a new table, especially since you are already anticipating changing the number of reminders in the future.

So you should add a new table, with the information about the reminders sent (userID, date_of_sending, type_of_reminder).

Answer 2

For an maximum amount of flexiblity, i would recommend you your own suggested solution.

[...] store it as a INT and have binary equivalents used for internal representations or use one column for each interval(like one TINYINT(1) for 2nd day, one for 5th and so on...)

The only questions you need to ask yourself is which unit (hours/days/weeks) fits the best and whether it should be possible to send more frequent notifications (e.g. each 0.5 days).