Database Schema for multiple authentications, Facebook Connect, Twitter, OpenID, etc

Question

I am building an application that I want to interface with Facebook Connect, Twitter, OpenID, and potentially other social networks. Users will be able to login using any number of these methods at the same time. My application uses MySQL as a backend database.

Can someone give me guidance on what my db schema should look like for capturing user info from various social networks at the same time? One idea I have (based on my reading online) is something like:

User {userid, ...}
UserFacebook {fbid, userid, ...}
UserTwitter (twid, userid, ...}
etc., etc.

Then to get a complete picture of a user I would join across all the user tables. Is that how other sites do it or is there a smarter/better way?

Answer 1

I suggest you have your User schema in two tables: Users and Identities. Identities should have: Id, User Id, Adapter, Hash.

For username/password authentication adapter, the Hash will be a hashed (MD5/SHA1 for example) password, while other adapters (Facebook, Twitter, etc) will be the token provided by Auth Provider.

Good luck.