I am trying to look for the best table design for any "follow/unfollow user" feature, much like Twitter (edit: I am not creating a Twitter-like application.) Below is my the present table design and php handling. I am not sure whether it's a great way and would actually thank you for 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 fans id is put into the
follower area and also the id from the user that'll be adopted is put into
following. I am also recording the date a person activated to follow along with another user within the
subscribed area. The date of subscription may be employed for sorting.
Whenever a user unfollows, I merely remove the particular row.
To be able to prevent duplicate rows I made posts
following unique. If your user has already been following an id and clicks to follow along with again, I recieve database error 1062. For example
Duplicate entry '62-88' for key 'follow_unique'
Clearly when already carrying out a user, the follower sees a
unfollow button. This really is accomplished by checking if your follower-following table row is available between 2 customers. If
rows > 0 I display
Well, what is your opinion?
I believe it's an excellent design, and really should suit your needs nicely. One factor you need to most likely result in the
following foreign secrets. Oh, as well as for simplicity sake, I'd result in the
TIMESTAMP DEFAULT CURRENT_TIMESTAMP simply to capture the datetime the consumer activated.
I recommend you are making follower and following together the main key. There's no requirement for a seperate