1

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?!
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.