Database structure for social login implementation?

Question

In my application, I have a "user" table that has the following structure.

CREATE TABLE IF NOT EXISTS `users` (
  `userId` int(10) unsigned NOT NULL auto_increment,
  `username` varchar(128) NOT NULL default '',
  `password` varchar(32) NOT NULL default '',
  `email` text NOT NULL,
  `newsletter` tinyint(1) NOT NULL default '0',
  `banned` enum('yes','no') NOT NULL default 'no',
  `admin` enum('yes','no') NOT NULL default 'no',
  `signup_ip` varchar(20) NOT NULL default '',
  `activation_key` varchar(60) NOT NULL default '',
  `resetpassword_key` varchar(60) NOT NULL default '',
  `createdon` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`userId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

I want to implement social login via Facebook, Twitter, and OpenID in my application, just like StackOverflow did. Please suggest to me what all changes I will require in my DB and how the logic will be implemented in PHP, alongside with existing login facility.

Thanks!

Answer 1

I would suggest that you introduce the concept of an AuthenticationProvider:

CREATE TABLE IF NOT EXISTS `AuthenticationProvider` (
`ProviderKey` varchar(128) NOT NULL,
`userId` int(10) unsigned NOT NULL,
`ProviderType` enum('facebook','twitter', 'google') NOT NULL, 
PRIMARY KEY  (`ProviderKey`) )  
ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Each login provider provides a unique key for the user. This is stored in ProviderKey. The ProviderType contains information about which login provider this ProviderKey belongs to, and finally, the userId column couples the information with the users table. So when you receive a succesful login from one of the login providers you find the corresponding ProviderKey in the table and use set the authentication cookie for the user in question.

I'm not sure that you want the ProviderType to be an enum. It would probably be more correct to make another table that could hold these.

When a user first registers with your site, and logs in via Facebook, for example, you will have to create a row in the users table. However, there will be no password, activation_key and resetpassword_key involved. So you may want to move those fields to a separate table, such that your users table only contains the core user data, and no data that is only relevant for a single login mechanism (username/password).

I hope this makes sense and that it points you in the right direction.

/Klaus