Notifications database design

Question

So I am in the process of updating an existing notifications database design AS it wouldn't be optimal for storing group chat notifications.

Notifications:

• id
• user_id
• type (enum)
• thread_id (used for chat notifs)
• contact_request_id (used for linking contact requests to a notif)
• created_at
• read (whether they read it or not)

At the moment, for each contact request, we would need to store 2 rows, for every single message, we would need to UPDATE 2 existing rows (each user has there own notif)...this is the main reason I want to find an optimal solution! Because when we add group chats, storing and updating 2..* rows will get ugly, and I can see data getting out of sync quickly.

I have thought of 3 solutions:

1. We create 1 notification AND link the users to that notification in another table (notification_users)
2. We create a seperate table for message notifications. So that we can facilitate for multiple users per notif?!

Answer 1

If you have to know read status for each notification, use separate table similar to this:

CREATE TABLE notification_read_status (
  notif_id bigint not null references notifications,
  user_id bigint not null references users,
  read_when timestamptz not null default now(),
  primary key (notif_id, user_id)
);

The boolean is not needed here. Existing row means true, missing row means false.

Possibly, if this is a very large scale app, and if you can simplify the model, then you can save some resources:

1. notification ID will be a monotonic bigint never going down and
2. store only one the last ID read (which means all previous notifications for given user are also read) - this will be just one field in users table.