0

I have a forum website where I want members to get notified whenever there is a new post, and when someone commented on their comment but I'm not sure my implementation is the best way considering factors associated with each suggestion:

  1. I thought of having a table with the following fields,

    notification_id INT NOT NULL,
target_member_id INT,
notification_text VARCHAR(150),
redirect_to_url VARCHAR(255),
date_inserted DATE,
active_status TINYINT DEFAULT 1,
PRIMARY KEY (notification_id)
);```

Explanation: New record gets inserted when there is something they need to get notified of. But the bad side is that, if I have 10,000 users on the platform, I'll have to insert new 10,000 records in the table when there's a new post. Not only will this lead to slow running time but also bring about a large database.

  1. I thought of having a table that has the following fields:

    notification_id INT NOT NULL,
seen_by TEXT,
notification_text VARCHAR(150),
redirect_to_url VARCHAR(255),
date_inserted DATE,
PRIMARY KEY (notification_id)
);```

Explanation: Where the seen_by field will have a comma-separated value of member_id for only members that have seen the notification, resulting in a case that I'll have to loop through all records in the table while checking the seen_by field to determine if a particular user have seen the notification or not.

I know it's possible there's a better way to implement this feature, I'll be glad to see your solution.

Cradoe
  • 106
  • 1
  • 8
  • 2
    For "new posts" to all users. Record the last date/time accessed for each user and therefore new posts are relative to that. Don't do comma separated lists in SQL, its horrible to write/parse. Just a simplified version of `target_member,new_post_id` is needed to record for replying to comments. – danblack Feb 04 '20 at 05:58
  • 1
    I don't think you need to insert notification record for ```users.length``` times. Simply create notification record and show it to users you want. then if some user sees it then in a new table named ```notification_seen``` insert user_id, notification_id. And write code to show seen notification for that user. by this way you need to track only seen behavior of users not every notification. – Hemant Sankhla Feb 04 '20 at 06:38
  • Every time I see the words 'comma separated', my heart sinks just a little bit. – Strawberry Feb 04 '20 at 06:54

0 Answers0