I’m trying to determine the best table design for a "follow/unfollow user" feature, similar to Twitter (edit: I’m not making a Twitter-like application.) Below is my the current table design and php handling. I’m not certain if it’s the best approach and would really appreciate your feedback.
CREATE TABLE IF NOT EXISTS `user_follow` ( `id` int(11) NOT NULL AUTO_INCREMENT, `follower` int(11) NOT NULL, `following` int(11) NOT NULL, `subscribed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`id`), UNIQUE KEY `follow_unique` (`follower`,`following`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=76 ;
On follow, the followers id is added to the
follower field and the id of the user that will be followed is added to
following. I’m also recording the date a user subscribed to follow another user in the
subscribed field. The date of subscription might be used for sorting.
When a user unfollows, I simply delete the respective row.
In order to prevent duplicate rows I made columns
following unique. If a user is already following an id and clicks to follow again, I get database error 1062. For instance
Duplicate entry ’62-88′ for key ‘follow_unique’
Obviously when already following a user, the follower sees a
unfollow button. This is achieved by checking if a follower-following table row exists between 2 users. If
rows > 0 I display
Well, what do you think?