1

I want to know what are the conventions for columns like username and password when your application supports login with a third party, like login with facebook for instance.

Given the fact that when you Log with a 3rd party, you don't really need to specify a username, you'll just specify the 3rd party user id, in database design, you can expect either a blank field or a null value.

Since usernames are UNIQUE, I'm guessing going for nullable is the way to go, since it allows multiple null columns.

The real issue is that the user needs to specify either a username OR a 3rd party id. Which means both columns can be null, but not at the same time

So what's the convention for handling this design pattern?

2

I'd rather separate user and authentication mechanism/provider. Application may allow more than one authentication provider (say , logins with facebook, openid, etc). Adding columns/constraints every time you add a new authentication option doesn't sound like a very good idea to me (even though that doesn't happen often, such an action usually requires lots of changes on application side).
Something like

user(user_id);
authenticator (authenticator_id, name, description, authentication_type);
user_authentication(user_id, authenticator_id, authentication_identity, authenticator_token) ;

seems more general to me.

** In case of username/password authentication you can keep username in authentication_identity and password (surely not plain text) in authenticator_token. If you feel the pair authentication_identity/authenticator_token doesn't cover all the cases you can add couple more tables that store information specific to particular authentication_type

1
  • email password login would require the password to be hashed but social login would give you an access token, a refresh token and an expires column telling when the tokens expire, how will this get stored n your authenticator table, also what happens if the social login user decides to set a password
    – PirateApp
    Nov 10 '21 at 14:12
0

I'm not sur how you store username and password in your database. Do you store the data in your own tables or do you rely on the login and password that you need to connect to SQL Server?

Assuming it's the former then your assumption of a username being unique is only valid if there's a single 3rd party app (like facebook). But as soon as there are several 3rd party apps (e.g. facebook and twitter) you'll need to identify the username and the app it belongs to. Regarding the two columns being nullable: You could use a CHECK constraint to enforce data integrity (assuming both columns having the same data type...):

ALTER TABLE [dbo].YourTable  ADD  CONSTRAINT [CK_YourTable] CHECK  ((ISNULL(username,3rdpartyid) IS NOT NULL))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.