1

I have a requirement to create a notification module on a web application and I would like to know which user has read the notification so I won't have to display the Red Badge that indicates that a notification is not yet read.

I have thought of two solutions.

The first one is a bit naive but I find it easier to implement and maintain:

Solution #1:

CREATE TABLE [Notifications1] 
(
    Id integer NOT NULL,
    Title varchar(255) NOT NULL,
    Description varchar(255) NOT NULL,
    DateInserted datetime NOT NULL,
    ReadByUsers varchar(MAX) NOT NULL
)

So in this case, when a user opens an unread notification, I will append the User ID on the ReadByUsers column, so it will look like this 10,12,15,20 ...

Then when I need to check whether the user has read the notification, I will simply query the column to see whether the user id is included.

The second solution looks more of 'best practice' approach but it gets bit more complicated and a bit more heavy I guess..

Solution #2:

CREATE TABLE [Notifications] 
(
    Id integer NOT NULL,
    Title varchar(255) NOT NULL,
    Description varchar(255) NOT NULL,
    DateInserted datetime  NOT NULL,

    CONSTRAINT [PK_NOTIFICATIONS] 
        PRIMARY KEY CLUSTERED
)

CREATE TABLE [Notification_Users] 
(
    NotificationId integer NOT NULL,
    UserId integer NOT NULL
)

In this case I store the user that has seen the notification in a different table with a relationship (One to Many) on the Notification Id on the main table. And then I can check the Notification_Users table to check if the user has seen the notification.

Also note that :

  • I don't need to know the time the user has seen the notification.

  • The application is not expected to reach more than 10000 users any time soon.

  • Notifications will be deleted from database after few months. So max 10 notifications at a time.

Do you have any suggestions for better implementation?

Thanks

knewit
  • 55
  • 1
  • 6
  • 5
    . . Storing lists in a character string is the wrong way to design a SQL database. That should answer your question. – Gordon Linoff Sep 04 '20 at 20:39
  • heavy is a matter of perspective. option 2 seems simpler, at the db level is is only an insert then exists or not. Also with a primary clustered key on the 2 fields it would be not so onerous on the sql engine. Option 1 requires a much more unspecific query on read and then you would need to parse the readbyusers column. Given that read is the more common operation, that is the use case to consider first. – Gavin Sep 04 '20 at 20:46
  • binary? nope. It should be the same datatype (and preferably the same name) as the PK of Notifications - along with the appropriate foreign key (or keys - is not UserID a FK to some user table?). And don't develop the habit of defining datatypes without lengths (for those that support them). `binary` is exactly the same as `binary(1)` – SMor Sep 04 '20 at 20:51
  • And DateInserted should be datetime or datetime2 with the desired precision. The Time datatype does not support 255 fractional digits and has no date component. – SMor Sep 04 '20 at 20:54
  • You could create a 3rd table called Notification_User_Events and when you add rows to it you could also update a bit column in the Notification_Users table to indicate the message has been read – SteveC Sep 04 '20 at 21:52
  • This isn't an exact duplicate, but you should read [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad), where you will see a lot of reasons why the answer to this question is **Absolutely yes!** and draw your conclusion from that. – Zohar Peled Sep 06 '20 at 15:38

1 Answers1

1

Solution #2 would be the preferred solution.

Storing a list of Ids is not a normalized solution. In my opinion, the return on investment of breaking this out into two tables out weights the perceived complexity.

Some good info below, and honestly all over the net.

A Database "Best" Practice

https://softwareengineering.stackexchange.com/questions/358913/is-storing-a-list-of-strings-in-single-database-field-a-bad-idea-why

Poweredbytones
  • 81
  • 5