Dealing with multiple login systems (Facebook Connect, Google Account, and others)

Question

If you want to allow your site users to be able to login with Facebook Connect, Google Account, etc, how do you design your database so that they are all integrated?

Answer 1

I suggest using an Accounts table to hold your local account data that would then be related to an AthenticationMethods table or a Credentials table where you would hold related external authentication details. This allows your site and all of it's complexity to be related to Accounts and your login process to nicely abstract the various forms of authentication that you might want to support. Given that each authentication method may have different levels of complexity I suggest looking at the various APIs and cross referencing them all to find common similarities. More important you will want to locate where they are different so that you credentials table can handle the various bits of data that each API might requrie. Don't forget to include OpenID!

Answer 2

I utilize two fields in my local account or user table. One is called "externalSystemId" (TINYINT), and the other is "externalId" (DOUBLE). I have some code constants that define the various externalSystemId values, ie. EXTERNAL_SYSTEM_FACEBOOK = 1, EXTERNAL_SYSTEM_OPENID = 2, etc. The externalId is of course the user's id from that system.

While handling a User object in my code, I can easily determine what system a user is from, and load the proper code mechanisms to authenticate or display something that is system-appropriate, based on the externalSystemId.

Note that you may wish to use a database table to store records of external systems, rather than code constants, depending on how many you are going to be dealing with and whether you need a bunch of extra information about the system.

Answer 3

Are you sure you want to store other informations than "login":"password" ? I would recommend you that you have an enumeration for saving which login system the user comes from.