Hello guys!

Lost all my work last year but I'm rebuilding everything and making some changes along the way.

In one of my projects, I had a participants field [saved as TEXT] under my activity table. this field held the IDs of people who commented on a status or topic using '|' as the delimiter for each.  The aim being to easily notify people of new comments and stuff.

This approach seems convenient as I don't have to do any other search after I select the activity but it is costly in terms of SPACE.

Another possible approach is to search for all comments related to an activity and retrieve the user IDs, join the main poster's ID to the list if it isn't already there and send notifications. This way, I save space but the SEARCH might take time depending on the number of people who have commented or participated in another way.

Which approach do you recommend and why?

*I cant understand this* Just woke up. head not clear yet.

@*dhtml: Is it better to store something in mysql TEXT format (eg IDs of participants in an activity such as people who comment on a status[activity]) when there are other ways of retrieving the data? The same data (user ID) can be retrieved(searched) from comments table but this might take time depending on the number of users who have commented. If I store their IDs in text format along with the original status, I get to save time but space might be used up.

Caramba. Yo no se - I don't know. But I believe you need a lesson on rdbms - relational database management systems.
You need to understand the one-one relationship and others.
U need different tables of course, and u need to link them with primary key and all that.

This is not easy to put into words just like that. Maybe you should go through the mysql classroom on my website - www..net

dhtml,

u r not alone. I have read the OP at least thrice but still don't get it. I guess some things are better communicated verbally. Then again, it is about 715am local time in my locale.

When i woke up at 02:17:46 AM this was the first post i saw it. I thought well, maybe i was not fully awake. . .

lolz 

I guess I haven't providing enough detail.

Assume I have four tables like this:

USERS:
fields: ID, NAME

ACTIVITIES:
fields: ID, USER_ID, ENTRY, DATE

COMMENTS:
fields: ID, USER_ID, ACTIVITY_ID, ENTRY, DATE

NOTIFICATIONS:
fields: ID, USER_ID, MESSAGE, VIEWED, DATE

Now imagine a user has written a status. This is added to the ACTIVITIES table along with other details.

Next: friends of the user post comments to the same status, each is added to the COMMENTS table.

The user including his/her friends who have commented on the status could be said to have participated in an activity.

Now, we want to notify each participant every time a new comment is made. There are two ways to do this:

The First way: Have a PARTICIPANTS field [saved as TEXT] in the ACTIVITIES table so that the USER_ID of each friend who comments on the status as well as that of the user is added to it.

An entry looks like '2 | 1 | 5 | 6 '. Note '|' is a delimiter and the entries will have to be parsed using a function such as explode('|', $IDS);

2 is the ID of the user (status poster) and other numbers refer to those of other participants.

All I have to do now is just retrieve this field, parse it (separate items using '|' as delimiter) enter the IDs in an array and send notifications to everyone while making sure I don't send to the person who makes the newest comment.

Second Way: I could search COMMENTS for entries whose ACTIVITY_ID is the ID of the user's status since if you think about it, each corresponding USER_ID is already of that a participant and store them in an array. Before storing them though, I also sure the USER_ID is not that of the person making this new comment.


Now the question is which way is faster? Storing as TEXT using the PARTICIPANTS field [cost = space] or just searching the COMMENTS table [ cost = time]?

*Peeps* Runs away, yawatide, please help!

If I am right, I believe you are trying to create a facebook element where a user can comment on a friend's status and every other person associated with this friend can receive the update. Right or wrong?

*Creeps in* Kudos Temy *creeps out* on all fours

@teemy: yes you're right. I've already done it. I just want to know which way is faster.

The question in simple terms:

Is it better to store all the IDs of users to be notified in one file [mysql TEXT] or is it better to do a search and retrieve the IDs? its basically a space vs time question.

@*dhtml: This one wey u dey jonz my question  I go report to Seun o.

*Farts* loudly. Trys run away but get caught by seun.

There is a technique in PHP used to test how long a function takes to execute by using microtime and checking elapsed period.
Mayhaps you should employ that technique so that you can answer the question for yourself.

Methinks that with a well-designed database, the search/retrieve would be the best way to go. It seems based on explanation that the storing in a file approach adds another (unnecessary) layer of processing, especially if the data set gets very large.

Just my humble opinion.

"The First Way" is flawed, you don't store multiple entries
in a single column, yes it takes space, and it doesn't save
time either(you'll have to spend a lot of time explode-ing
the entries in the column)- not the way to go.

The database scheme you have already is ok, since what you
are trying to achieve is to get the ID's of those who post
statuses and of those who post comments on those statuses, just
use a simple JOIN to SELECT those columns from the ACTIVITIES
and COMMENTS tables, with ACTIVITY_ID as foreign key (this,
i think is what you mean by 'search').

@*dhtml: lolz! Nice idea, I'll have to add dummy data though and I might not get it right pus I don't know much about mysql's internals.

@yawa-ti-de: Search/retrieve seems good but I keep wondering about an extreme scenario where there are about a million comment entries and only 5 related to the activity of interest. How does mysql fare in such a case? I'm guessing it might not be a problem since mysql's search algorithm seems very very fast.

@bakenda: You're right about the second way.

Regarding the first way: maybe you're right but I think it does save time. Exploding x1 | x2 | , xn, where n = 1000 or more doesn't seem like it'd take too much time, will test though.

*Strolls by*

@ gameaddict : what happens if there is a need to delete a particular
comment-delete all the entries in the PARTICIPANTS column?

*truncate* gameaddict

The comments are not stored in the PARTICIPANTS FIELD. only the IDs are. All I have to do is check if the user has other comments relating to the status. If he doesn't, I retrieve the participants, explode the data, join all in a new string 'x1 |'.'x2 |' etc except the ID of the friend who deletes a comment and replace the old field conent.  This looks like it will take time depending on the number of IDs though 

I also have a NO_FOLLOW table for activities for users who do not want to be notified. This is useful First or Second way.

I know this is old but I feel I should come back and relay some info.

Bakenda's suggestions were right on! and JOINs are awesome and are simply a case of intersections and unions. They're intuitive and allow for both well designed databases and well defined/structured code.
And... if you're like me and you're worried about using up the database space, don't. If you design the database well enough, everything will work out fine. If the database gets used up, it'll be because a lot of people are using your site by which time you'll probably have made enough to triple database size.

Cheers.

gameaddict: I know this is old but I feel I should come back and relay some info.

Bakenda's suggestions were right on! and JOINs are awesome and are simply a case of intersections and unions. They're intuitive and allow for both well designed databases and well defined/structured code.
And... if you're like me and you're worried about using up the database space, don't. If you design the database well enough, everything will work out fine. If the database gets used up, it'll be because a lot of people are using your site by which time you'll probably have made enough to triple database size.

Cheers.

There was another way I achieved your feat.

The target is notifying other posters abi?

I had a 'notifications' table and 'followed' table. When anyone posts on any topic, his 'user_id' and 'topic_id' gets inserted into the 'followed' table.
Now each time theres a new comment, after performing the comment insert query, I also perform notification insert which inserts a notification message for every user in the followed table where topic_id matches the current topic.
Now the notification can also go to a user who simply 'followed' the topic without actually posting anything.


**** To prevent notification table being overcrowded, i remove each notification row whenever a user opens the notification page matched with the notification id.

@onye_ngbu: Awesome. I implemented something similar with minor differences (and caveats) for Ask Africa: https://gcdc2013-askafrica.appspot.com

In the implementation, I simply used a saved (following in your case) table and added a LAST_KNOWN_ANSWER field (simply the id of the last seen activity eg. answer/comment [most likely yours if your comment is what led the system to add you to the list of topic followers] etc) to it. This way, I know that the user should be notified about each activity with an id greater than LAST_KNOWN_ANSWER. The rest is simple: get and display each activity with id greater than last_known.

The caveat pertaining to this system is that, each time the user chooses to delete an individual 'notification', we have to set the ID of the activity that caused this notification as LAST_KNOWN_ANSWER, thus, unread notifications for the same topic that occurred earlier will be lost. To avoid the possible confusion, notify the user that earlier notifications for same topic will be removed or alternatively notifications should be grouped by topic (eg. add a 'see other items you've missed button') and deletion should be by topic ('are you sure you want to delete all notifications for this topic?') rather than per notification item.