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.

  `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`)

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

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 follower and following foreign secrets. Oh, as well as for simplicity sake, I'd result in the subscribed column 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 id area.