MySQL user table structure design, third-party login

Speaking of the user table, it is probably the first thing to consider for each application/site project (Yard Farm). The design of the user table structure is the cornerstone of the entire backend architecture. If the cornerstone is unstable, until the back of the need to follow up the discovery can not cope, back to repeatedly modify the user table, to make changes in large and small places are also many. With this in mind, you might want to design a user table to consider scalability at the outset, and try to get the next step in place without too much extra cost.

Previous design

Id
Username
Password
Username plus password, address simple requirements, leave an ID as a foreign key to other tables. Of course, then the password may also be plaintext storage, better know MD5.

Later, with the expansion of business needs, to add a user state status to determine whether the user is banned, registration time and registered IP address, the last login time and IP address for reference (and derived from the login record table, used to determine whether remote login, etc.), user role/Permissions role (also derived from the user role permission relationship, or another discussion), the business also requires personal information such as real name, address and so on a brain to add, and now form a very complete user relationship table.

Id
Username
Password
Realname
Address
...
Status
Role
Register_time
Register_ip
Login_time
Login_ip
Now the problem, into the Web2.0 era, Weibo opened a third-party website Login, with the micro-Bo account can be logged into our website, the boss said, this we have to. Add a microblogging user login form, of course, to be associated with our own user table, this microblogging user information table is as follows:

ID self-Increment ID
USER_ID Associated site User ID
UID Micro blog Unique ID
Access_token
Access_expire
This is not finished, QQ and open user login, all of a sudden to access a number of third-party login, only on the "Microblogging User Information table" continue to add type plus judgment, if it is each third-party login to create a new table, will certainly be crazy.

Times have changed, into the era of mobile internet, how to support a mobile phone number login it? So now every standard is: username/email/mobile phone number login, plus a series of micro-blog, such as third-party login. The table structure is as follows:

User table
Id
Username
Email
Phone
...
User third-party login form
Id
user_id
App_type
app_user_id
Access_token
...
After the user enters the user name/email/phone number and password in the input box, the background determines whether it is a mailbox, a mobile phone number or a user name, and then the query is a specific user based on the criteria.

This table structure is capable of hosting business requirements for a certain period of time. If you say that a new sign-in method, such as the ID number login, what should I do? Continue to add fields to the user table? I think there's a better choice.

Improved version

Whether Username+password, or Phone+password, is a kind of user information + password authentication form; To understand the third-party login, in fact, it is also the user information + password form, User information is the ID in a third-party system (a third-party login must give a unique identifier in their system), and the password is Access_token, but is a password that is periodically modified with the use of the time limitation. So we abstracted it out of the form of the user base information table plus the user authorization information table.

User base Information table users
ID
Nickname
Avatar
User authorization information sheet user_auths
ID
user_id
Identity_type Login type (mobile phone number mailbox) User name) or third-party app name (Weibo, etc.)
Identifier identification (mobile phone number mailbox user name or unique identification of third party app)
Credential password voucher (save password in the station, do not save or save token outside the station)
The biggest feature of this system is that the user Information table does not save any password, do not save any login information (such as user name, mobile phone number, mailbox), only the nickname, Avatar and other basic information. All licensing-related (and basic front-end display unrelated), are placed in the User Information authorization form, User Information table and user authorization table is a one-to-many relationship. It's too abstract to say, show me the code.

Users
|id|nickname|avatar|
|1| mu Murong |http://.../avatar.jpg|
|2| Magic Bird |http://.../avatar2.jpg|
|3| Kobe Bryant |http://.../avatar3.jpg|
User_auths
|id|user_id|identity_type|identifier|credential|
|1|1|email| [email protected]|password_hash (password) |
|2|1|phone|13888888888|password_hash (password) |
|3|1|weibo| Micro Bo uid| Weibo access_token|
|4|2|username|moliniao|password_hash (password) |
|5|3|weixin| username|token|
Talk about the specific processing, the user sent to the mailbox/user name/mobile number and password request login, still is the first to determine the type, to a user using the mobile phone number login As an example, using the SELECT * from user_auths WHERE type= ' phone ' and identifier= ' Mobile phone number ' to find an entry, if any, to take out and determine if Password_hash (password) matches the credential of the entry, the match is verified, and then the user information is obtained through USER_ID.

If you are using a third-party login, just judge SELECT * from User_auths WHERE type= ' Weixin ' and identifier= ' UserName ', if there is a record, the direct login succeeds, and the original token is updated with the new token. Assuming that communication with the server is not hijacked, there is no need to determine the credential issue.

Through this table structure design, so that many of the original tangled problems instantly resolved, say the pros

One, the station login type unlimited expansion, small code changes. If you really want to support ID login, as long as a few changes, no need to modify the table structure.

Second, third-party login type can be expanded in batch mode, and the development cost of new third-party login type is minimized.

Third, under the original conditions, the application needs to verify whether the mobile phone number has been verified and the mailbox has been verified, need to be more than one field such as Phone_verified and email_verified, now as long as the User_auths table to add a unified verified field, Each login method can be visualized to see if the situation has been verified. The default third-party logins are verified based on the accuracy of the data that is trusted for third-party logons. If the user modifies the login phone number or log in to the mailbox, you can also clearly track the completion of each step.

Four, can be bound to any number of the same type of login method, that is, a user can bind multiple, can have multiple mailboxes, can have multiple mobile phone number, is not great? Of course you can also restrict a login method to only one record.

Five, in user_auths add the corresponding time and IP address, you can more complete tracking user habits, for example, has not used Weibo login for more than two years, has been bound for 300 days

Six, even if the full use of third-party account login, the front-end can do "no registration of the site account" effect. Many websites in the past, although support third-party account login, but for the sake of retention users, and so on, the first micro-blog login back, let you fill out a set of their website mailbox, password and other information, also lost the maximum significance of Weibo login. Technically, the original structure resulted in the creation of an entry in the user table, except in the case of the Weibo user table, and it is generally not possible to leave the user's mailbox or user name and password blank. The user experience is good, the mailbox automatically generates Weibo [email protected] , the password is randomly generated. As for the experience is not good, can only say that early know better than not to log on Weibo! Now, our user table structure is completely without such a problem, as long as the nickname and Avatar address provided by Weibo can generate this user, and then associated with his Weibo login record. And our table structure means that the user can remove all of his login methods, so the account becomes completely unable to log on to the zombie (the solution is to add a limit in the code, at least one user_auths record). If you have to get the user's mailbox, then every time you log in to see that he does not exist a identify_type for email records, the pop-up window to kill him, let him quickly fill the mailbox, or do not do anything.

Seven, improve the logical thinking ability. Abstract the essence of Things is the code of the agricultural necessary professional quality, through the user table structure of the study, improve my humble skills, from this writing code bon voyage ...

Eight, if you say that the mailbox and mobile phone number is part of the user information, they still need to be reflected in the users table as the front-end display? No problem, the users table is still email,phone in spite of the expansion, but they are not fundamentally different from the attributes of nicknames, avatars, or sex, just as "display purposes". In the User Information table and user authorization login split, the user information table can be added any field at any time, add constellation, plus birthday, no problem, just need to display at the front of more than a few input boxes, input more than a few lines of code, with the user login-related problems to achieve the maximum degree of decoupling.

A good will have a disadvantage, say a shortcoming.

First, the original user judgment was changed from 1 SQL to 2 SQL requests.

Second, the user at the same time there are mailboxes, user name, mobile phone number and other station login mode, change password must be changed together, or become a mailbox + new password, mobile phone number + old password access, it is certainly very strange situation. If you take this into account, add a new identification field in the User_auths table that represents the way the site is logged in or a third-party login.

Third, the amount of code increased, in some cases, logic judgment increased, the difficulty increased. For example, regardless of whether the user is logged in, regardless of whether the user has been registered, click on the same link to the Weibo third party authorized to return, there may be several situations: 1, the micro-blog in this site has not been registered, very good, directly to his registration association and login; 2, the Weibo has already existed in this site, Direct login success, 3, the Weibo is not registered in the site, but the current user is logged in and associated with another Weibo account, what to do depends on whether to allow the binding of multiple Weibo account number, 4, the Weibo is not registered in the site, the current user is logged in, try to bind the operation; 5, the Weibo has been registered, The user has also logged in with the account, why he repeatedly binds himself--. 6, the microblog already exists in this site, but the current user is logged in and associated with another Weibo account, what to do? Switch users or error? (Drawing a flowchart to better describe the problem) is not related to the data structure used, but only in the case of third-party account registration, the various situations encountered in this arrangement.

MySQL user table structure design, third-party login