Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to design the user table with supporting login by username/email/mobile?

Tags:

sql

mysql

I am designing a user registration table for my website, we need to support login by username/email/mobile using the same password.

The user can register by a username, email or mobile, once they get registered they can bind their email or phone.

Here is the table that I am thinking, but it has some problems:

user_id is AUTO_INCREMENT

user_id |   user_name   |   email   |   mobile  |   pwd
--------------------------------------------------------
   1        test            
   2                    [email protected]          
   3                                    123456          

The following registration should fail:

   4        123456              // illegal, 123456 is present in mobile                     
   5        [email protected]      // illegal, [email protected] is present in email                                  

The requirement is just to support login by username/email/mobile, and there is no limitation regarding the username format, so here username or email or mobile must be unique among the three columns.

It seems that it is impossible to add unique constraint cross multiple columns, any better solution?

like image 778
seaguest Avatar asked Oct 25 '25 07:10

seaguest


2 Answers

How about a table that holds the user_id, and their password:

user_account (user_id int, pwd binary(128))

And a separate table to hold user registration:

user_registration (user_id int, registration nvarchar(300), registration_type int)

You would then avoid having non-unique registrations belonging to different users, and your various applications can pass in the type or registration they are handling when authenticating a user.

You also avoid having a table with a bunch of null values when 95% of the user-base gravitates towards using mobile login exclusively (for example).

-- user_acount data

user_id pwd
------- ---------------
1       0x234524305762
2       0x403958634589
3       0x345656753546

-- user_registration data   --Types username=1, email=2, mobile=3

user_id registration        registration_type   
1       '[email protected]'   2                   
1       '4443332233'        3
2       'userTWO'           1
3       'User-Three'        1
3       '[email protected]'   2
3       '5554443322'        3

You are basically allowing the user to create a "user name alias" for each of your different registration modes, without making them have a value for each mode. This could be viewed as both good, flexible login options for user; and bad as hacking into an account would become exponentially easier with the first half of the credential possibly/likely being the phone number of the user.

like image 128
Max Sorin Avatar answered Oct 27 '25 22:10

Max Sorin


It seems to me (without knowing further details about your requirements) that the best approach would be to have each user have a single row in the table:

user_id    user_name    email             mobile    password
1          test         [email protected]    123456    mypassword

You just need to always know which type of login you're dealing with (user name, mobile, or email address) at each point in your application so that you know which column to look at.

like image 37
Tom H Avatar answered Oct 27 '25 20:10

Tom H



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!