CODEWITHSUNDEEP
phpmysqldatabase-designnotificationsprivate-messaging
TheDeveloper
TheDeveloper

Reputation: 940

Private Messaging Notification - Database Table Design

I have have created a Private messaging system using PHP and mySQL with notification a bit like Facebook.

The database table has following fields( not all listed):

All replies are stored in a second table, since each message create a thread. The second table looks a bit like this:

At the moment when a new message is sent out to a user i change the 'status' of the message to unread, From this can run a count query to list all of the unread messages in notification.

But if the users replies back to that message i cant set the original 'status' field to unread, since this will appear on both users notification. so i created another field called 'RepliedStatus ' but i am unsure how would i use this to show notification on message reply?

thanks guys.

Upvotes: 2

Views: 2776

Answers (3)

Jean-Christophe Duperron
Jean-Christophe Duperron

Reputation: 54

Me I'll put deleted column once and the same things for the read or not-read like:

[{"0":"both", "1":"Sender", "2":"receiver"}];

And then fetching a tread messaging like:

$sql = "SELECT * FROM messagetreads 
WHERE (senderID OR receiverID = ".$_SESSION['MyCurrentId'].")
AND deleted !== 0
ORDER by TreadID AND DateTime ASC";

When a sender "delete" is tread... All tread relatedID in the database change for 1 or 0 if delete colomn is 2...

But I think it's better to creat another colomn for getting of the repeated deleted and notifications data like

  • TreadID (FK_message_Table)
  • delete (0=both deleted UserID=don't appear to this sender or receiver)
  • notify (0=both read UserID=read to this sender or receiver)

Then it's ezee to change a status or a delete statement.

The SELECT will be sometings like this:

$SQL = "SELECT * 
FROM messagetreads
WHERE (senderID OR receiverID = ".$_SESSION['MyCurrentId'].")
IN (SELECT TreadID WHERE delete !== (0 OR ".$_SESSION['MyCurrentId']."))";

If is our member id involve in the colomn delete all the tread don't appear but if is the id of the receiver when the sender will delete 0 can be attributed so that both of the members can "delete" the message. Same thing for notifications!

very far later a cron-job can come delete very-old message read(0)...

PS: this can be notification system too like notifying for a new post in wall or comment on a photos or new events on calendar ... just add more information in column data and faormat it with php or java-ajaxed alike...

Upvotes: 0

Uboonto
Uboonto

Reputation: 69

Why dont you add an INT and nullable column to the first table (let's say, "messages" table) named "previous_message"?

ALTER TABLE messages ADD COLUMN previous_message INT DEFAULT NULL;

So every message will have in the same table the previous one, and you can work out the sequence. If it helps you can have a "next_message" column with same definition and update the relevant record on reply.

Doing so you can use the status column on each reply.

If you want to keep the same DB organisation I would suggest to add a column status on the second table (let's say "replies").

Hope this helps

Upvotes: 0

plenderj
plenderj

Reputation: 573

If you have a replies table then you don't need a replied status column on your first status. By virtue of there existing a record in the replies table you know that a user has replied to a message

Upvotes: 2

Related Questions