"Follow user" database table design

Question

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 follower and 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 unfollow otherwise follow.

Well, what do you think?

Answer 1

I think it's a very good design, and should meet your needs nicely. One thing; you should probably make the follower and following foreign keys. Oh, and for simplicity sake, I'd make the subscribed column TIMESTAMP DEFAULT CURRENT_TIMESTAMP just to capture the datetime the user subscribed.

Answer 2

I would suggest you make follower and following together the primary key. There is no need for a seperate id field.