Authentication via social networks

Question

I am implementing authentication via Facebook and Twitter, and later other networks, but some doubts arose. What would be the logic for creating a new account when the user authenticates via social network?

I assume I get the id and the e-mail, but what would be the 'Foreign key', between my database and the social network?

---

I take as an example the code below taken from a tutorial:

# baseado no ID do Facebook
select * from Users where Fuid = '$fuid'

# (IF) novo usuario
INSERT INTO User (Fuid , Femail) VALUES ('$fuid' , '$femail')

# (ELSE) atualiza dados do usuario
UPDATE Users SET Femail = '$femail' where Fuid = '$fuid'

In the example he takes the id of Facebook as a single base, inserts or updates the e-mail, name and any other information available.

How would I integrate networks when the Facebook and Twitter user has the same email? Each authentication would return one id different, so it does not apply to the above code.

Answer 1

In your case I would use a master table for local user registration and create tables for each social network (Face, Twt, etc.) with the master table ID being used as key for all other tables. Thus, you could even capture extra user data (locations?!), record access dates made by each social network (individually), etc.

|  Tabela      |          Campos         |          
+--------------+-------------------------+
| User (master)| ID | name | email | etc |
| Facebook     | ID | SID  | email | etc |
| Twitter      | ID | SID  | email | etc |
| Outra        | ID | SID  | email | etc |
|______________|____|______|_______|_____|

The ID is the key; SID is the ID of each social network (or token); the other fields (etc.) will be in charge of the need of its application.

Just add in INSERT or UPDATE an extra sentence to the table of the social network in question.