database design for notification settings

Question

• A user can turn on or off notification settings for his account, for notifications such as Changed Account Profile Information, Received New Message etc

• Notification can be sent via email or mobile phone (either push or sms), user can have 1 email only and many mobile phone devices.

Is there any way you would improve the following database design or would you do it differently?

let me know thanks

USER_NOTIFICATION_SETTING
Id
UserId
Notification_SettingCode
NotificationTypeCode
UserDeviceId -- the mobile deviceid
IsEnabled -- true (notification is on), false (notification is off)

NOTIFICATION_SETTING
Code - e.g 1001, 1002
Name -- e.g Changed Account Profile Information, Received New Message etc

NOTIFICATION_TYPE
Code - e.g 1001, 1002
Name -- e.g Email, SMS, Push


USER_DEVICE -- the mobile phone device information
etc...etc...

Answer 1

---

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.

Answer 2

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

Answer 3

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.