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?
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With