Database table structure for different notification events

I set out to build one of my favorite projects some time around June, and while I was able to complete it to some extent and successfully launched it, I did face some setbacks while trying to implement certain features in the application. One of the most difficult feature I had to implement was notifications. I spent quite some time trying to figure out how to model this form of data in the database. I am using a relational database (PostgreSQL). The main problem I faced with the notifications model was based on the requirements needed to store notifications in a way that still references certain rows and columns in multiple tables. Here's the explanation in details:

a. In the database, here's a User table that contains columns like id(primary key) name, email, profileUrl, etc

class User extends Model { }

User.init(
  {
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    bio: {
      type: DataTypes.STRING,
      allowNull: false,
      defaultValue: "Hey there!"
    },
    id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
      defaultValue: DataTypes.UUIDV4,
      unique: true,
    },
    username: {
      type: DataTypes.TEXT,
      allowNull: false,
      unique: true,
    },
    profile_url: {
      type: DataTypes.STRING,
      allowNull: false,
      defaultValue:
        "https://someimagesite.com/default_image.jpg",
    },
    email: {
      type: DataTypes.TEXT,
      allowNull: false,
      unique: true,
    }
  },
  {
    sequelize: instance,
    modelName: "users",
    tableName: "users",
    timestamps: true,
    createdAt: true,
    updatedAt: true,
  }
);


User.hasMany(Post, {
  foreignKey: "userId",
  foreignKeyConstraint: true,
  constraints: true,
  onUpdate: "CASCADE",
  onDelete: "CASCADE",
});


User.hasMany(Comment, {
  foreignKey: "userId",
  foreignKeyConstraint: true,
  constraints: true,
  onUpdate: "CASCADE",
  onDelete: "CASCADE",
});

Post.belongsTo(User)
Comment.belongsTo(User)

b. Here's a Post table that contains columns like id(primary key), content, title, userId, timestamp. "userId" references a row in the User table and it represents the author of the posts. It has a foreign key constraints set as "onUpdate: CASCASDE" & "onDelete: CASCADE" on the User table which ensures that when a user row is deleted/updated from the users table, any other references to the row in any table gets updated as well.

class Post extends Model { }

Post.init(
  {
    content: {
      type: DataTypes.TEXT,
      allowNull: false,
    },
    id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
      defaultValue: UUIDV4,
      unique: true,
    },
    userId: {
      type: DataTypes.UUID,
      allowNull: false,
    },
  },
  {
    sequelize: instance,
    modelName: "posts",
    tableName: "posts",
    timestamps: true,
    createdAt: true,
    updatedAt: true,
  }
);

Now, imagine we need to to display a notification to certain users in the following typical format(show picture of notification):
"@echoeyecodes just made a post"

In the sample format above, we can see a couple of references to certain attributes of 2 tables in the database.

• a. username of user (User table)
• b. post thumbnail (Post table)
• c. description (Post table)
• d. profile photo of user (User Table)
• e. timestamp (Post table)

To model this as a table in the database for notifications, we'll need the following columns:

• a. postId
• b. userId
• c. targetUserId (recipient of the notification)

class Notification extends Model {}

Notification.init(
  {
    id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
      defaultValue: UUIDV4
    },
    postId: {
      type: DataTypes.UUID,
      allowNull: false,
    },
    userId: {
      type: DataTypes.UUID,
      allowNull: false,
    },
    targetUserId: {
      type: DataTypes.UUID,
      allowNull: false,
    },
    isRead: {
        type: DataTypes.BOOLEAN,
        allowNull: false,
        defaultValue: false
    },
  },
  {
    sequelize: instance,
    modelName: "notifications",
    tableName: "notifications",
    timestamps: true,
    createdAt: true,
    updatedAt: true,
  }
);

Now to ensure our data here is consistent with the tables it references and avoid null data, we'll add foreign key constraints --> onUpdate: CASCASDE & onDelete: CASCADE to the userId, and postId columns to User and Post table

With this model, we can query for notifications on posts created by a certain user with no issues at all. But the 'gotcha!' in this is that it only works well for post notifications. What about when we need notifications for the following events:

• a. when a user mentions another user in the content of a post?
• b. when a user publishes a comment on someone else's post?
• c. when a user mentions another user in a comment/reply
• d. when a user likes a post
• e. when a user likes a comment/reply

If we analyze these events, you'll notice that each event references certain columns in different tables beyond Post and User. We could go ahead and add more attributes like "commentId", "replyId" to our notification model to adjust to the requirements for notifications, but that would make our model contain redundant columns, and make it harder to debug or understand. We would also have more than a few null columns for a row that actually only references at most 2 tables.

How do we solve this problem?

I looked up for a conventional approach for storing notifications in databases, and found some that didn't exactly solve this specific issue, but gave me insights on how to resolve this. I didn't want to create separate tables for different kinds of notifications, especially when these notifications are expected to be arranged in a chronological order. Well, thanks to this stackoverflow answer, I figured that I could have a separate table for notification event-types, e.g (post-likes, comment-likes, post-mentions, comment-mentions etc) and a notification table that holds only generalized attributes for every form of notification. This notification will contain attributes like timestamp, eventId, recepientId since all forms of notification are expected to have these attributes. That was the first step towards the solution. So now we have an events table like:

class NotificationEvent extends Model {}

NotificationEvent.init(
  {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true
    },
    type: {
      type: DataTypes.STRING,
      allowNull: false,
    }
  },
  {
    sequelize: instance,
    modelName: "notification_events",
    tableName: "notification_events",
    timestamps: true,
    createdAt: true,
    updatedAt: true,
  }
);


NotificationEvent.hasMany(Notification, {
    foreignKey: "eventId",
    foreignKeyConstraint: true,
    constraints: true,
    onUpdate: "CASCADE",
    onDelete: "CASCADE",
})

Notification.belongsTo(NotificationEvent, {
  foreignKey: "eventId",
  onDelete: "CASCADE",
  onUpdate: "CASCADE",
  foreignKeyConstraint: true,
  constraints: true
})

Then our revamped notification model would look like:

class Notification extends Model {}

Notification.init(
  {
    id: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
      defaultValue: UUIDV4
    },
    eventId: {
        type: DataTypes.INTEGER,
        allowNull: false
    },
    targetId: {
      type: DataTypes.UUID,
      allowNull: false,
    },
    isRead: {
        type: DataTypes.BOOLEAN,
        allowNull: false,
        defaultValue: false
    },
  },
  {
    sequelize: instance,
    modelName: "notifications",
    tableName: "notifications",
    timestamps: true,
    createdAt: true,
    updatedAt: true,
  }
);

Going back to notification format for various events I listed above, I observed a pattern for each event:

1. (a) is a form of notification between a User and a Post table. That is pretty straightforward, so we can have a UserPostNotification table that contains the columns "userId" and "postId".

2. (b) is a form of notification between a Comment, and a Post table. So the main attributes needed here would be the commentId, and the postId for the PostCommentNotification table. With these columns we can easily construct our notification message by fetching the related data they reference. You know that a comment would always be from a User, therefore it would have a column called "userId" so we could fetch the user information based on this column to get data like username, and profileUrl. Also, every post has a "userId" attribute that references a certain user, so we could also get the user that owns the post based on this attribute and use it as the recipient of the notification.

We can now have something like @username commented on your post

1. (c) is a form of notification between a User and a Comment table. We can construct our notification message like the image below using only the attributes "userId" and "commentId".

2. (d) is also a form of notification between a User and a Post table. To get a message like the image below, we can obtain the information using the userId and postId attributes.

3. (e) is a form of notification between a "userId" and commentId and the message like @echoeyecodes liked your comment could be constructed using these two attributes.

After observing these patterns, I realized I could have a single table for notifications that are between a User & Post, User & Comment, or User & Reply. These tables would have an id attribute as primary key that references a row in the notifications table as a One-To-One Relationship. So to query all notifications in chronological order with the various attributes from different notification types, we could use an INNER JOIN for the Notification table and NotificationEvents Table, plus a LEFT JOIN with the UserPostNotification table, and PostCommentNotification table.

We could also insert an INNER JOIN clause to each of the sub-tables based on the parent tables they share constraints with, without having to re-query for these pieces of information after obtaining the foreignKey attributes since UserPostNotification would have foreign key constraints between a User and Post (userId, postId), PostCommentNotification would have a foreign key constraints between a Post and Comment userId, commentId).

here's how the UserPostNotification would look like:

class UserPostNotification extends Model {}

UserPostNotification.init(
  {
    notificationId: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
    },
    sourceId: {
      type: DataTypes.UUID,
      allowNull: false
  },
    referenceId: {
        type: DataTypes.UUID,
        allowNull: false
    },
  },
  {
    sequelize: instance,
    modelName: "user_post_notifications",
    tableName: "user_post_notifications",
    timestamps: true,
    createdAt: true,
    updatedAt: true,
  }
);

then the PostCommentNotification:

class PostCommentNotification extends Model {}

PostCommentNotification.init(
  {
    notificationId: {
      type: DataTypes.UUID,
      allowNull: false,
      primaryKey: true,
    },
    sourceId: {
      type: DataTypes.UUID,
      allowNull: false
  },
    referenceId: {
        type: DataTypes.UUID,
        allowNull: false
    },
  },
  {
    sequelize: instance,
    modelName: "post_comment_notifications",
    tableName: "post_comment_notifications",
    timestamps: true,
    createdAt: true,
    updatedAt: true,
  }
);

Now to query for all notifications available for a particular user, we would query via the notification table and use a left join for various notification types available like:

const NOTIFICATION_INCLUDE_OPTIONS : IncludeOptions[] = [{model: User, required: true},
    {model: NotificationEvent, required: true},
    {model: UserPostNotification, include: [{model: User}]},{model: PostCommentNotification, include: [{model: User}, {model: Comment, include: [{model: User}]}]}]


async function getUserNotifications(userId:string(){
   const result = await Promise.all((await Notification.findAll({include: NOTIFICATION_INCLUDE_OPTIONS, where:{targetId: userId}}))
        .filter((item) => {
//here we filter off notifications with possible all notification type as null. this kind of state cannot possibly exist since every notification object would be of at least one type, but just to be safe, we need this check
            const result = item.get({plain: true}) as NotificationType
            if(!result.user_post_notification && !result.post_comment_notification){
                return false
            }
            return true
        })
        .map((item) => formatNotification(item.get({plain: true}))))
        return result
}

Finally, we can write a small utility function that constructs the notification message based on the event type for every notification like the one below:

async function formatNotification(instance:NotificationType){
    const type = instance.notification_event.type as NotificationEventType

   if(type == "post_comment"){
     //format notification based on requirements e.g @echoeyecodes commented on your post
   }else if(type == "post_create"){
     //format notification based on requirements e.g @echoeyecodes uploaded a photo
}
}