database design for notification settings

Or maybe this one which propagates natural keys. This has wider tables, but requires less joins. For example, you can get notifications for a UserName directly from the NotificationQueue.

Or this one, which is good enough if you have phone and email only. So far the simplest -- I think that currently I like this one the best.

What you've done looks pretty good actually. I would out of personal preference do the following:

• Eliminate the UserId column on User_Notification_Setting as it should already be on your User_Device table
• Get rid of the _s in your table names
• Change the Code fields in Notification_Setting and Notification_Type to be Id (even if they are not Identity columns) and then change the foreign key references from other tables to have a more consistent NotificationTypeId field name.
• Eliminate the IsEnabled field. The fact that a record exists at the intersection should suffice for having the notification. Deletion of that record means that there is no notification. I can see why you might want to remember that a notification was there at one time and maybe have it there to easily re-enable but I see no information stored at the intersection so deletion is just as good.

Looks good, only a few minor suggestions:

• Naming of code fields, use table name then _Code
• Add a notification for all changes

There are a couple of things I do not agree with Tahbaza on:

• I would leave the user id in, it is then faster to get all notifications for a user
• I would leave the isEnabled in, it is then possible to temporarily stop all notifications