CODEWITHSUNDEEP
phpmysqldatabase
user1307016
user1307016

Reputation: 405

MySQL Database Structure for Followers

I would like to implement a follow/favorite system. I can think of 2 ways of implementing a database/table structure but am unsure of which one to implement. Which one of these would be considered best practices and most importantly why?

I put all of my followers in a single string. By putting all followers in a single string it reduces the amount of redundant rows.

Ex.

id (1) || user_id (1) || follower_ids (2, 3, 45)

'CREATE TABLE `users` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `username` varchar(20) NOT NULL, 
        PRIMARY KEY (`id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1';

'CREATE TABLE `follow` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `user_id` int(10) unsigned NOT NULL,
        `follower_ids` text NOT NULL, 
        PRIMARY KEY (`id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1';

OR

I put each follow_id individually but adds redundancy by having 3 rows for the same user_id.

Ex.

id (1) || user_id (1) || follower_id (2)

id (2) || user_id (1) || follower_id (3)

id (3) || user_id (1) || follower_id (45)

'CREATE TABLE `users` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `username` varchar(20) NOT NULL,
        PRIMARY KEY (`id`) 
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1';

'CREATE TABLE `follow` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `user_id` int(10) unsigned NOT NULL,
        `follower_id` int(10) unsigned NOT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1';

Upvotes: 1

Views: 5622

Answers (2)

Bradley
Bradley

Reputation: 2141

Your second option with a slight modification on the field names, as both the follower and the followed are BOTH user_id's. As mentioned by John, add foreign keys to both of the *_user_id fields in the follow table.

Additionally, never have plural table names. 'user' and 'follow' are sufficient. I personally prefer tables like 'follow' to have a prefix like 'xref_' so that i know it's simply a cross reference table allowing a many-to-many relationship (A user can follow many users, and a user may have many following users).

'CREATE TABLE `user` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `username` varchar(20) NOT NULL, 
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1';

'CREATE TABLE `follow` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `followed_user_id` int(10) unsigned NOT NULL,
        `follower_user_id` int(10) unsigned NOT NULL,
        PRIMARY KEY (`id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1';

Upvotes: 8

MIIB
MIIB

Reputation: 1849

The best way is neither. You should have a middle table between follow table and followers table. The middle table have only two columns. follow_id and followers_id. With this kind of approach you omit the disadvantages of two solutions you have mentioned. You do not have to proccess a string and you have not duplicate entries and the performances with only indexes included are pretty fast.

'CREATE TABLE `follow` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1';

'CREATE TABLE `user` (
        `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
        `username` varchar(255) unsigned NOT NULL,
        ....
        PRIMARY KEY (`id`)
        ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1';
'CREATE TABLE `follow_user` (
        `user_id` int(10) unsigned NOT NULL,
        `follower_id` int(10) unsigned NOT NULL,
)

Cause you change your post a lot, i think that your second approach is better if followers are the same as users. Because you only store the indexex of follow and users and a good select query to see what a single user follows something is way more better than parsing and searching a string The duplicate entries are no problem cause they are only indexes and there's no problem to that.

Upvotes: 4

Related Questions