OAuth (Facebook, Twitter) and basic login - users table

Question

I want to let users login to my website using their Facebook, or Twitter account, but if they don't have one then to register a new account, and use the basic login of my site.

But how should my users table looks like:

My idea:

• id (primary, auto increment)
• username (in case of oauth login => ouath_provider+oauth_id, ex:fb_100001557958700)
• password (password choosed by user or randomly generated in case of oauth)
• name (name to display)

What do you think? Or should I have 2 tables, 1 for basic login, and another 1 for oauth login? But then how users whill have unique ids?

Thanks in advance for comments.

Answer 1

In my experience, you are best storing your authorizations in one table, and your user data in the other.

authorizations:

network     - Varchar(255)  #Twitter/Facebook/Openid/whatever
network_id  - varchar(255)  #Users id for that social network.
user_id     - int

users:

id (primary, auto increment)
name
password
username

With this structure, if you want to allow the same user to login with both Twitter AND Facebook at some point in the future, that is also possible.

Answer 2

This is how my user table looks like.

• User_id (primary, auto increment)
• oauth_provider (enum(none,twitter,facebook))
• oauth_uid
• username
• password
• etc

Every time a user is registering through Facebook/Twitter, a new entry is inserted with the password record as null ofcourse.

I think this is a good way to do it, because you have 1 unique user_id you can use throughout your app/database.