用于存储和检索通知数据的阵列设计

Question

I want to store and retrieve user notification data for community site. The notifications should be generated when someone comments on a user's post or when someone follows's a user. I want this behaviour to replicate Facebook notifications. So it should look like this:

User A, User B and 3 other started following you.
User A, User Z and 2 others commented on your post Super Duper.

If I create a generic array for holding the notifications then it would look like this:

$notification = array ($notification_id, notification_time, $read_status, $notification_recipient_id, $notification_initiator_id, $post_id=NULL, $comment_id=NULL); // This array represents a single notification

$notifications = array ($notification, ...); // This array is made up of many single notification array.

So, if I were to retrieve the data from $notifications array (the one containing all individual notification array), I would use a for loop and echo the results after sorting the array based on time. This would give me something like this as the result:

User A started following you.
User B started following you.
User C started following you.
User D started following you.
User E started following you.
User A commented on your post Super Duper.
User B commented on your post Super Duper.
User C commented on your post Super Duper.
User D commented on your post Super Duper.

So, if you look at the result I intend to achieve and the one that I would get out of the array design I currently have, both differ. I may be able to achieve the desired result by doing N number of operations that may involve flattening, recursive for loops, sorting, etc to the $notifications array. But, I think I could reduce the overhead by re-designing how the data is stored in the array so that when data needs to be retrieved I can perform least number of operations. Also, since read status needs to be taken into account while displaying data, it would even so more become complicated to achieve my desired result. I request for suggestions on designing the array structure and examples on how I could retrieve the data from the array to achieve the result I intend to.

Answer 1

You could do the folowing:

• select the notifications of type 'following', where read_status = 'not_read', LIMIT 2
• count the notifications of type 'following'
• select the notifications of type 'comment', where read_status = 'not_read', LIMIT 2
• count the notifications of type 'comment'

Whenever someone reads a notification, you set it to 'read'.

Whenever someone comments or follows something, you create a notification for all the related persons. Those persons are for example all the persons involved in a discussion, or just the person being replied to. Same thing for a comment.

This way, you are able to format and display this message without manipulating too much data. (You can probably pack those requests for more efficiency, but you get the idea)

User A, User B and 3 other started following you.
User A, User Z and 2 others commented on your post Super Duper.

I would say that changing your data structure for a problem like this is overkill. With this approach, you would be constantly redefining your data model, which will soon become a nightmare. The data model should reflect the big constraints of your application, not 'little" formatting problems like this one.