What is a good-practice of a schema for a site that accepts logins from multiple sites (e.g. facebook connect, google, flickr, etc..)?

Question

I asked this question at http://dba.stackexchange.com, but it was closed with a note that I should ask it here as it relates to design and not administration.

I'm building a site ("The Site") and I'm considering how to design the DB schema for it.

I have a few scenarios that will affect the outcome:

• The user can choose to register a native account on the site, which will make him use that account for login in to his account on the site in the future.
• The user can choose to login via another site ("external site") (such as facebook connect, google, foursquare, etc..), which will make him use that for login in to his account on the site in the future.
• Doesn't really matter which login method he chose (native account or external site account), the user will be able to authenticate his account on the site with external sites (such as facebook, twitter, instagram, etc...) for using the data he has on the external site (such as his tweets from twitter, his photos from flickr, etc..), in the site.

How should I design the schema of the DB ?

For the first two scenarios, I thought of having a table called Users that has an id, and a type fields. The type reference a different table, such as FacebookUsers or GoogleUsers, each has a user_id field - which has a foreign relation to the Users.id field - and other fields such as the first_name, profile_picture, etc..

But this approach doesn't support the third scenario. So I thought about the following:
Having a base table called Users and table for each external site I want to allow the user to connect with my site (which is the site).
The Users table will hold basic information about the user (such as first name, last name, username, password, email - all optional) with a column named accounts that will hold values such as native|facebook|twitter, or a serialized array of these values.
The external sites tables (e.g. FlickrUsers) will hold the relevant data for that external site relevant to the user, and a reference to the Users table' user_id field.

When a user connects his account with an external site (such as flickr, instagram), I populate that external site table (e.g. FlickrUsers) with the relevant data for that external site, and add the name of this external site as a another value to the accounts field in the Users table.

I am asking this as a general best-practice question, for a site that uses a multi-account login.

Having that said, I'm not sure if the above are good approaches/designs for the site I described above. I'm fully aware that there isn't just one correct answer, but I know there are better solutions and not-so-ideal solutions. I'm hoping to get to the better solutions.

For the sake of this discussion, my RDBMS is mysql and my language is php.

Answer 1

The simplest way is to have a table with different ways of logging in that is not specific to the way of logging in. That will just create more work for you if they want to add another way.

Table Users
id, name, etc. - Not password

Table LoginValidations
id, user_id, type, validation_data

When they attempt to log in, match the type of login with the validation_data. For your site (type=YourSite), the validation_data would be a password (or hashed password). For type=Facebook the validation_data would be however you validate to your site using Facebook login, etc.

When someone wants to log in, you validate their id against the appropriate type of validation_data.