All geek questions in one place

Database design for notification settings

  • The user can enable or disable notification settings for his account, for notifications, such as Changed account profile information, Received new message, etc.

  • The notification can be sent by email or to a mobile phone (push or sms), the user can have only 1 email and many mobile devices.

Is there a way to improve the following database design or will you do it differently?

tell me 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 - eg 1001, 1002 Name -- eg Changed Account Profile Information, Received New Message etc NOTIFICATION_TYPE Code - eg 1001, 1002 Name -- eg Email, SMS, Push USER_DEVICE -- the mobile phone device information etc...etc...
+4
source share
3 answers

alt text

Or maybe one that applies to natural keys. This has wider tables, but requires fewer joins. For example, you can receive notifications for UserName directly from NotificationQueue .

alt text

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

alt text

+3
source

That you actually looked pretty good. From personal preferences, I would do the following:

  • Eliminate the UserId column in User_Notification_Setting, as it should already be in your User_Device table.
  • Get rid of _s in your table names
  • Change the Code fields in Notification_Setting and Notification_Type as Id (even if they are not Identity columns), and then change the foreign key links from other tables to have a more consistent NotificationTypeId field name.
  • Exclude the IsEnabled field. The fact that the entry exists at the intersection should be sufficient to receive notification. Deleting this entry means that there is no notification. I can understand why you might want to remember that the notification was there at the same time and maybe it was there to easily turn on, but I donโ€™t see any information stored at the intersection, so deleting is just as good.
0
source

It looks good, only a few minor suggestions:

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

There are several things that I disagree with Tahbaza:

  • I would leave a user id and then get all notifications for the user faster.
  • I would leave isEnabled in, then you can temporarily stop all notifications
0
source

Source: https://habr.com/ru/post/1336679/

More articles:


All Articles