Best Way To Store Multiple Flags In Database

Question

I have a web-based application that notifies users of activity on the site via email. Users can choose which kinds of notifcations they want to receive. So far there are about 10 different options (each one is a true/false).

I'm currently storing this in one varchar field as a 0 or 1 separated by commas. For example: 1,0,0,0,1,1,1,1,0,0

This works but it's difficult to add new notification flags and keep track of which flag belongs to which notification. Is there an accepted standard for doing this? I was thinking of adding another table with a column for each notification type. Then I can add new columns if I need, but I'm not sure how efficient this is.

Thanks in advance!

Answer 1

I would use two tables. One table would store the user data and the other the notifications that they subscribe to. The second table would look something like this:

create table notifications (
   user_id int,
   notification_type int
);

I'd make a FK relationship between user_id and the user's id in the users table with a cascade on delete. Use both the user_id and notification_type as the primary key. To check if a user wants a particular notification simply do a join between the two tables and select rows where the notification_type matches the one in question. If the result set is non-empty the user wants the notification.

Adding new notifications becomes trivial (as does deleting). Simply add (delete) a new type value and let users choose to accept it or not. If you wanted to keep the notification types in a table to manage via the application that would work, too, but it would be a little more complex.

Answer 2

Using MySQL?

Then, SET datatype is the answer.

"The MySQL SET datatype is stored as an integer value within the MySQL tables, and occupies from one to eight bytes, depending on the number of elements available." - http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html"