Postgresql – Notifications database design

database-designpostgresql

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

Best Answer

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.
Related Question