Database table design: design of user table based on business scenarios

User login design of this site system

Usually used as the login of this site system, there are three design methods:

  • Username+password
  • Email + password
  • Phone number + password

So how to design the login method?

1. Are the functions of the three login methods duplicated? Is it possible to implement only one of them?

The function is not repeated. It depends on the situation.
The reasons are as follows:

  • The website still needs a username to distinguish internal users.
  • When mentioning a user, the email and mobile phone number, the user may not want to make it public. When mentioning nicknames, nicknames can be repeated and cannot be distinguished.
  • Once the user name is selected and not allowed to be changed, the mailbox and mobile phone number shall be allowed to be changed as a third-party system.
Insert picture description here

2. When can I log in using only my email or mobile phone?

The mailbox or mobile phone number is unique, but it also has the characteristics of "personal contact information", which has a different meaning from the UID generated by the system. Individuals should have the freedom to control whether their information is exposed in public space.

When the need for interaction with other users is not high, that is, there is no need for social interaction, the mailbox or mobile phone number can be used as an "account", such as banks, hospitals, and workstations.

As for systems with high social demands, such as forums and games, as a "person", you should retain your username to log in.

Unless it is stipulated that the nickname must be unique, at this time, you can refer to others through the nickname "@", then you can also log in without the user name.

3. Is the email or mobile phone number allowed to be changed?

The mailbox or mobile phone number can be changed. Although each mailbox is marked with a separate contact information and does not repeat each other, the mailbox, as a personal variable information, should be allowed to die or change, or even be replaced by an impersonation.

Because the controller of the mailbox asset does not belong to the current information system, it is a third party (of course, it can also be the mailbox service provided by this system, but from the current user system, it belongs to a third party).

The control of the user name belongs entirely to the current information system.

**A well-functioning information system obviously should not guarantee the long-term existence of third-party systems. **But it can determine the user name, and the UID corresponding to the user name is indeed completely controlled by itself, and determines the processing strategy.

For example, the QQ number, as the user name UID of the system, is randomly generated and is not linked to other third-party systems. However, some Tencent games only support QQ account or QQ mailbox login. At this time, because they belong to the same company, the system can be guaranteed to be reliable. You can log in without setting a user name + password.
In an event such as yahoo mailbox being closed, if you only set up your mailbox to log in in your system, you will lose your password if you retrieve your password through this mailbox.

Design of third-party login

Design case 1

Reference: Talking about the design of database user table structure, the design of third-party login is as follows:

users
|id|nickname|avatar|
|1|慕容雪村|http://…/avatar.jpg|
|2|魔力鸟|http://…/avatar2.jpg|
|3|科比|http://…/avatar3.jpg|
user_auths
|id|user_id|identity_type|identifier|credential|
|1|1|email|[email protected]|password_hash(密码)|
|2|1|phone|13888888888|password_hash(密码)|
|3|1|weibo|微博UID|微博access_token|
|4|2|username|moliniao|password_hash(密码)|
|5|3|weixin|微信UserName|微信token|

The idea is to separate user authorization and user information. User information is easy to understand, and all the required information is placed in this table.
In the user authorization table, the id primary key is self-incremented, user_id can be repeated, and identifier is the unique key.
The login verification process is as follows:
1. Judge the login type, such as mobile phone number, through regular verification or entrance third-party login verification.
2. Take out the entry through the sql query statement SELECT * FROM user_auths WHERE type='phone' and identifier='phone number', and verify the password
3. After the verification is passed, take out the user_id user information

Advantages of this design:

  • Unlimited expansion of login types in the site, no need to change the table structure
  • Mobile phone verification and email verification are changed from the original two verification fields phone_verified and email_verified to one verified field. The third-party login verification is authenticated by default.
  • Any number of the same type of verification methods can be bound, such as multiple mobile phone number login, multiple WeChat or email login to the same account. Or stipulate that only one can be registered.
  • The effect of "no need to register this site account" can be achieved on the front end, and there is no need to register this site account again.
  • Mobile phone or email as the contact method can still be displayed as a field in the user information table.

Disadvantages of this design:

  • From 1 SQL to 2 SQL
  • When changing the password, the passwords of the mailbox, user name, mobile phone number, etc. must be changed together, otherwise it will become the mailbox + new password, mobile phone number + old password to access, the solution is to increase the field, distinguish this site account and page Three-party login account.
  • The amount of code has increased, and the logical judgment has become more complicated.

According to the above analysis, the final design table structure is as follows:

|id|nickname|avatar|sex|birthday
|1|慕容雪村|http://…/avatar.jpg|男|19980101

user_auths
|id|user_id|identity_type|identifier|credential|verified|is_self
|1|1|email|[email protected]|password_hash(密码)|1|1
|2|1|phone|13888888888|password_hash(密码)|1|1
|3|1|weibo|微博UID|微博access_token|1|1

Design case 2

According to the above design case, I think this kind of database table structure design is not very satisfactory.
His idea of ​​separating user authorization from user information table is very much appreciated. However, the user authorization table is too complicated, the logic and code volume increase, and the meaning is ambiguous.
When the user logs in, the verification code must be considered, the number of times the user enters the wrong password, and whether the user is disabled, these are all related to the system of this site.
Therefore, consider a use case corresponding to a data table. Entering the account password from this site and obtaining authorization from a third-party login are different use cases. The data table for this site login and the third-party login should be separated.

Why use a third-party login?

Our main consideration is that when users register or log in, they are unwilling to set a password or fill in various information. However, the user already had a unique openid such as a QQ account and a WeChat account before that.

Based on the trust in these systems, the unique openid can be used as a credential to log in to the system on this site and the accompanying information (nickname, avatar, etc.) can be obtained.

So the third-party login has the following intentions:

  • After binding the account, if the account password of this site is forgotten, another means of logging in can be used.
  • For users who register for the first time, they can quickly register and log in without having to provide their account passwords, and do a good job of draining traffic.

What are the shortcomings of third-party login:

Since the information required by the system of this site is varied, and the third-party system may not provide it, such as mobile phones, e-mail addresses, this information is used for future marketing and password retrieval.

Sometimes the account on this site is strongly needed. In order to get rid of the dependence of external systems, or to obtain more user information, a third-party system needs to be bound to the account on this site.

However, after the first third-party registration, many websites need to register the account of this site immediately. The user originally thought that he could register and log in quickly, but it actually took longer, and may feel cheated and abandon the registration.

Solution:

  • No third-party login
  • Allow third parties to log in, after the first registration, in the future, through various methods to induce users to register this site account, complete the information
  • Allow third parties to log in. After the first registration, immediately register the account on this site, but make a clear reminder before then
Insert picture description here

Multi-account system

When logging in by a third-party, users generally do not want to have multiple accounts on this site, that is, one user_id is generated for each third-party login (QQ, WeChat, mobile phone, email), and one more account. When users log in with different third parties, they log in with different accounts.

Unless this site does not require interaction, similar to a bank "account", or Weibo, which wants more user accounts and a large number of followers.

When there are data information such as accounts with data information, similar to blogs, forums such as points, reading history, collections and other data information, users hope to share data information in a unified manner.

For the situation where the account number is desired to be unified, the design scheme adopted:

a. Ignore the needs of a small number of users who want to merge account information of different login terminals; (low development cost, affecting some user experience)

b. Provide account merger function; (high development cost, good user experience)

c. Mandatory registration after the first third-party login. (The development cost is moderate, which affects the experience of some users)

Registration and login verification methods in business

1. Account and Password Login
Account and password login methods are mature for users and have low external dependence. However, when the password is simple, it is easy to be cracked, and when the password is complex, it is difficult to remember.

  • Individual recognition: low
  • Convenience: low
  • Technical requirements: low
  • Risk level: high

2. Basic communication login (mobile phone or mailbox)
With the basic saturation of communication coverage and the development of IM, mobile phone numbers have gradually replaced mailboxes as the most common verification method. At this stage, due to the convenience of registration and login and no need to remember the password, the use of basic communication methods for identity verification is the most common.
But the mobile phone number may not be used for a long time, and then assigned to another user by the operator. Therefore, it is necessary to verify the supplementary process, which increases the use cost.

  • Individual recognition degree: medium
  • Convenience: Medium
  • Technical requirements: medium
  • Risk level: Medium

3. Credential information authentication (real name, passport, student ID, etc.)
Credentials are used as identity verification information accepted by the country and meet the basic requirements for verification information. However, there are two characteristics that limit his usage scenarios, one is sensitivity, and the other is complexity.

  • Individual recognition: high
  • Convenience: low
  • Technical requirements: medium
  • Risk level: Medium

4. Biometrics (fingerprints, iris, voice and audio, face recognition, etc.
** Theoretically perfect realization method. **Basically, the connection between real people and the network can be realized. The current problem lies in two points, one is technical means Bottleneck; Second, there is still room for identifiability to be developed. Find the most unique place on the human body, and then use it for convenient verification. If it can be achieved, it will be the ultimate verification method. At present, Apple’s fingerprint login, Tencent’s voice lock, and Alipay Face recognition and other functions are the direction towards the ultimate.

  • Individual recognition: high
  • Convenience: high
  • Technical requirements: high
  • Risk level: low

Design analysis

For different products, there should be different design schemes.

Nowadays, for marketing applications, users only need to provide WeChat login, and subsequently attract users to fill in their mobile phone numbers.

For applications with independent content, such as company websites, government websites, etc., users must register for an account on this site. If a third party logs in, they must be forced to register for an account on this site.

Insert picture description here

1. Industry

Different industries face different flow and risk control requirements. As shown in the figure above, different industries need to choose different registration and login processes due to different business scenarios. The closer to the traffic orientation, the shorter the registration and login process (even without registering and logging in to enjoy the service), the possibility of post-registration and login process increases; the closer to the risk control orientation, the longer the registration and login process, and the post-registration and login process The possibility becomes smaller. When the company's development direction is adjusted, the registration and login process also needs to be adjusted accordingly.

2. The company's development stage

The same goes for different stages of company development. In the early stage of development, due to the huge traffic pressure, I was more inclined to choose a short, flat and fast way to build an account system; in the later stage of development, whether it was for business expansion, preparation for transformation, precise marketing, and consideration of increasing the value of data in hand, etc. , Often consider building a self-built account system and use your own registration and login process.

3. Specific channel business positioning

Taking the e-commerce industry as an example, APP, PC and H5, foreign investment channels, these channels carry different missions, so if necessary, they should also design the registration and login process that meets the requirements according to their positioning to maximize the channel effect. APP and PC, relatively speaking, users are mainly mature users and old users, with more comprehensive functions, and also include modules with higher security requirements such as asset management. Therefore, for these channels, relatively long registration is required. And the login process; as for the H5 and foreign investment channels, because they are lighter and more focused on guiding fast transactions, they are more inclined to post-process, shorten the process, and reduce user loss.

Therefore, you need to look at whether your industry is flow-oriented or risk-control-oriented, what is the company's development stage, and the specific channel business form, before you can choose the most suitable method from the above different account registration and login forms, and then proceed. The supplement of the reverse process and the polishing of UI interaction form the most suitable registration and login process.

It should be noted that the registration and login process is not static, and may be adjusted appropriately at different stages of development. Even at the same stage, the needs of different channels need to be handled flexibly. How to plan well is the focus of user product managers.

Design and implementation

Based on the above considerations, I think the design of the database table structure is as follows:

个人信息表
person
|id|nickname|avatar|mobile|email|sex|birthday
|1|慕容雪村|http://…/avatar.jpg|mobile|email|男|19980101
本站用户表
user
|id|username|password|department|enabled|disabled_time|password_error_number|is_deleted
|1|zhangsan|password_hash(密码)|123|1|0|0
三方授权表
user_auths
|id|user_id|identity_type|identifier|credential|
|1|1|email|[email protected]|password_hash(密码)
|2|1|phone|13888888888|password_hash(密码)
|3|1|weibo|微博UID|微博access_token

First of all, the user information table is separated from the user authorization table, so there is no doubt.
The user user_id is the primary key of the database, which is associated with other table foreign keys through user_id. The account name username is guaranteed to be globally unique and used to identify users. Here does not generate a globally unique user_id, such as UUID generation, or a continuous number. The user_id is also not displayed to the front end, it is only used for the internal association of the system.

In a multi-enterprise system, the account name username and the organization department jointly constitute a globally unique one.

The username can be a user-defined pure letter, or it can be a mobile phone or an email address. As long as it is unique, it is not verified.
The mobile phone number and email address are changed by the user in the personal information table, and the authenticity is not verified. When the account needs to be bound, the user is not allowed to change at will, and the field is locked at this time. The same is true for real-name authentication of ID numbers.

The only purpose of the user table on this site is to log in, verify and identify identity, and obtain user permissions. Not responsible for other tasks.

The only purpose of the tripartite authorization form is to provide another way to log in, verify and identify the identity, and obtain user permissions. Not responsible for other tasks. After the first registration, the registration must be bound to the account on this site.

This is also in line with the life cycle of the project. From the beginning of the project development, there is only registration on this site. When the project develops to a certain extent, a third-party login will be provided.

At the beginning, only the account password is provided to log in. When the capital is available, a mobile phone verification code is also provided to log in.

Initially, there were only limited users such as back-end administrators who only needed username, account and password to log in. Later, when it developed, there were a large number of users at the beginning. Only mobile phones or mailboxes were required to register as account names at the front end, without the need to change the table structure.