I would like to allow users to follow other users as with Twitter, Instagram etc. Am thinking that this should include table structure something like following:
Users with userid, password, email, fname, lname
Followers with userid1(INT), followstatus(TINYINT), usererid2 (INT)
New row is created when user1 decides to follow user2. Later follow status can be turned off if user1 decides to no longer follow user2. There would be a separate entry if user2 follows user 1
I can tell user1 whom he is following with query to this table. And tell users how many people are following them with count query. Then conditional on this relationship, print out info on user2s actions (followee) to user1 (follower).
Is that best practice? Or is there more to it.... Are there other fields, tables required?
Note: users have another table of people, not necessarily users but I'm thinking follower status should be in separate table.