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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
I think it’s a very good design, and should meet your needs nicely. One thing; you should probably make the
following foreign keys. Oh, and for simplicity sake, I’d make the
TIMESTAMP DEFAULT CURRENT_TIMESTAMP just to capture the datetime the user subscribed.
I would suggest you make follower and following together the primary key. There is no need for a seperate