What should table structure look like when adding open auth login to User model

Question

I have a Rails app with a User model. I want to allow users to login with a handful of services. There are 2 ways that I can think to store this in the database but I'm not sure which is best. I should note that I would like users to be able to connect with more than one service and link the accounts.

Method 1: For each service, add token/secret fields to the User table. This seems a bit problematic because what if I want to store, for example, twitter token, twitter secret, twitter screenname, and twitter profile img? I could see the User table having many unused columns. There may be extra info I want to store with each service. It would look something like this:

id
twitter_token
twitter_screenname
twitter_secret
twitter_pic
facebook_token
facebook_secret
facebook_pic
facebook_name
google_token
google_secret
google_name
etc.

Method 2:
Or each user could have many social logins and each social login belong to one user. Then I would have a table that looked something like

user_id
token
secret
social_type  # foreign key to a social_site look up table
social_pic

and the social type table would look something like:

id
social_site_name
oauth_url

The only negative of this approach is that I have to generalize what I will store about all services. How do you guys do it? Maybe STI is in order here...where there would be a class for each type of login which inherits from a base login class.

Thanks!

Answer 1

I would suggest something like this:

• Each user can have many logins, each login belongs to one user only.
• Logins are of a specific type, one login is of one type only.
• SocialLogin table has fields common to all login-types, Google, FaceBook and Twitter tables have fields specific to each one.