OAuth Provider persistance

[Guest 20221009]

I am adding FB and Twitter authentication to an app my team is building. The app has its own membership tables with a few non-null fields.

I would like to keep track of the users that use social accounts to login and somehow link them to my own membership tables. From a DB point of view is it fine for me to pass empty values for some account so that I can link a social account with a membership entity?

user
-id (int)
-username (notnull)
-password (notnull)


oath
-id (int)
-identifier (string: facebook and twitter ids)
-userid (int)

Does this make sense? I have no need to store the tokens, just need to match the user with the content they create on the site.

 

[guest2013-1]

I think it'll be better to store 2 columns in your user table instead, one would be when facebook is used, other when twitter is used to login. That way it's a simple select username from user where facebook is not null to determine which users are using facebook, twitter or just normal logins.

You have the right approach in terms of your tables structure, and under normal circumstances I'd say "yes that is perfect". The problem is identifying which social networks they've logged in from (which you can easily add a column for under oath) but since you have 100% control over your login process, I don't think the extra table is necessary, unless you think one user will login with multiple social accounts each time (not just facebook/twitter, but multiple facebook/twitter accounts)

 

[Guest 20221009]

Thanks Acid. I will be looking into this some more. At the moment we have an action method for each provider, so we can tell which network was used.

This might not be very clean design wise but I am not any best practices out there, but will improve the implementation later today (side project).