preface

I have been engaged in the development of Internet e-commerce for more than three years, but I don’t understand the whole business process when I think about it. I feel ashamed to say it. But in the environment of Internet e-commerce, more or less contact with the various businesses, and secondly, there are many colleagues and friends engaged in e-commerce, which are all resources. So I decided to go through the whole process and share it with my colleagues, friends and even you, not to mention how good the results are, but at least in every place where we have the ability to do a good job.

In addition, in order to satisfy the technical satisfaction that we may not get in our work, we will use the technology stack that we most want to use in the whole system design process. Technology stack we use Docker to achieve this, so the end result: on the one hand, we master the business stuff, on the other hand, we get technical satisfaction, both.

Finally, for the sake of time, we came up with the idea of “Do Design No code”. In the end, we design the data model, the interface documents, even the interaction process, the environment deployment, etc., but in the end, we don’t write the code. Isn’t it? What’s the point of writing code in that case. Of course, it is not entirely so, for the sake of time, of course, it will be implemented in code, perhaps it is the opposite you to achieve.

Second, there are certainly areas of this content that are not fully considered or are more complex in a larger business. You are welcome to point them out and we would like to learn from and share your experiences.

Today, we begin the first part of the user system design. This paper is divided into the following four modules:

  • Architecture design
  • Data model design
  • Interaction design
  • Interface design

Architecture design

Let’s just look at the user system

When you first come into contact with a user-related Internet product, or was in my eyes. The user system is nothing more than “login” and “registration”, “modify user information” and so on. In simple terms, we need a table to record the user’s identity: at registration (insert), insert data into the table; During login (select&update operation), check whether the user password is correct based on the user ID (mobile phone number, email address, etc.). Modifying user information (select&Update operation) is to directly update the user information (profile picture, nickname, etc.) for this UID.

There’s nothing wrong with it. It’s simple. However, with the development of the business, on the one hand, we need to provide unified user management (high cohesion), and on the other hand, we need to improve the scalability of the system, so I want to present what I understand a basic user system should have.

What a basic user system should have

First of all, we abstracted the original user table again (removed the user registration, login dependent fields, third party login) -> account table, why do this? With the development of the business, we used to maintain only one product, but one day we may develop new products, so that we can maintain the registration and login logic of all products of our company in a unified manner. For different products, we can only maintain the information related to the product and the user (depending on the product form). As shown in the figure below:

In the figure above, the third party login/employee table/background authority management are also mentioned, these are some basic necessary structure of user system.

Third party login: A third party is also a login method, we also abstracted it to a part of the account, as shown in the figure above. Secondly, there is a problem in the interaction design of third-party login, which will be mentioned in the interaction design later.

Employee: Since we have removed the account table above, the internal management system can also use the login logic of the account table in a unified way, so that the whole company can achieve a really high cohesion in account matters.

When it comes to employees, the backstage of various internal systems must involve various rights management, so simple RBAC(role-based rights control) is mentioned here, and specific logical data model design will be mentioned.

Final architecture

As business product forms become more and more complex, we need to analyze the changes and invariances when designing the architecture:

  • Change: more and more products personalized user needs
  • Unchanged: Log-in logic for registration

In the end, we split the original users into accounts and users, but we also want to make clear the difference between the two concepts here:

  • Account: the unique place to produce UID in the whole system. It has cohesive registration and login logic and does not involve product business requirements
  • Users: different products personalized user demand information

The final architecture diagram is as follows:

  • Part I: Accounts (Service Layer)
  • Part II: Users (Application layer, unlimited horizontal scale)
  • Part THREE: Employees (application layer, employee authority system)

Data model design

It is easy to design our data model with the above architecture (assuming that we only have one application for the C side) :

Account -> 1. Account table Users -> 2. User table Employees -> 3. The employee tableCopy the code

In addition to the above three tables, we also need our R(role)B(base)A(Access)C(control) permission management,RBAC should be familiar with role-based permission management, I will not elaborate here, the simple RBAC first needs:

System menu table (menu is permission), uri path of the system 5. Permission table (menu is permission), specific permission is to access the system menu 6. Role table, which permissions a role has 7. Employee and role association table, which role an employee belongs toCopy the code

Ok, a simple list of RBAC involved in the basic list, but in my work experience, we often implement the authority management only for a certain system, so for many system background is messy, repeated wheels, low efficiency of authority management. Therefore, IN the architecture design above, I regard permission as a service to provide basic service capability for the whole system. And to do that, I just need to add one more list:

8. Background management system table, register all background management systems (so that through the system ID and system resource URI ID can constitute global uniqueness, simple URI has the possibility of duplication, the reason to use URI rather than URL is the possibility of domain name change)Copy the code

Finally, our user system should be based on the above 8 tables. Hey, seems to have missed the third party login, let’s add, very simple as:

9. Third-party user login form, recording different third-party user identifierCopy the code

The table structure and SQL are as follows:

Table SQL

Account model


-- Account model
CREATE TABLE `account_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'account id'.`email` varchar(30) NOT NULL DEFAULT ' ' COMMENT 'email'.`phone` varchar(15) NOT NULL DEFAULT ' ' COMMENT 'Phone number'.`username` varchar(30) NOT NULL DEFAULT ' ' COMMENT 'Username'.`password` varchar(32) NOT NULL DEFAULT ' ' COMMENT 'password'.`create_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Creation time'.`create_ip_at` varchar(12) NOT NULL DEFAULT ' ' COMMENT 'create IP'.`last_login_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Last landing time'.`last_login_ip_at` varchar(12) NOT NULL DEFAULT ' ' COMMENT 'Last login IP'.`login_times` int(11) NOT NULL DEFAULT '0' COMMENT 'Login Times'.`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Status 1:enable, 0:disable, -1:deleted',
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`),
  KEY `idx_phone` (`phone`),
  KEY `idx_username` (`username`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='account';
Copy the code
-- Third-party Account
CREATE TABLE `account_platform` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'on the id'.`uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'account id'.`platform_id` varchar(60) NOT NULL DEFAULT ' ' COMMENT 'platform id'.`platform_token` varchar(60) NOT NULL DEFAULT ' ' COMMENT 'platform access_token'.`type` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'platform type 0: unknown, 1: facebook, 2: Google, 3: wechat, 4: qq, 5: weibo, 6: twitter'.`nickname` varchar(60) NOT NULL DEFAULT ' ' COMMENT 'nickname'.`avatar` varchar(255) NOT NULL DEFAULT ' ' COMMENT 'avatar'.`create_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Creation time'.`update_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Update time',
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`),
  KEY `idx_platform_id` (`platform_id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Third-party user Information';
Copy the code

The user model


-- User model
CREATE TABLE `skr_member` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'user id'.`uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'account id'.`nickname` varchar(30) NOT NULL DEFAULT ' ' COMMENT 'nickname'.`avatar` varchar(255) NOT NULL DEFAULT ' ' COMMENT 'Head (relative path)'.`gender` enum('male'.'female'.'unknow') NOT NULL DEFAULT 'unknow' COMMENT 'gender'.`role` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'Role 0: Common user 1: VIP'.`create_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Creation time'.`update_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Update time',
  PRIMARY KEY (`id`),
  KEY `idx_uid` (`uid`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Account Information';
Copy the code

Employees model


- the employee table
CREATE TABLE `staff_info` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'employee id'.`uid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'account id'.`email` varchar(30) NOT NULL DEFAULT ' ' COMMENT 'Employee Email'.`phone` varchar(15) NOT NULL DEFAULT ' ' COMMENT 'Employee phone number'.`name` varchar(30) NOT NULL DEFAULT ' ' COMMENT 'Employee name'.`nickname` varchar(30) NOT NULL DEFAULT ' ' COMMENT 'Employee nickname'.`avatar` varchar(255) NOT NULL DEFAULT ' ' COMMENT 'Employee profile picture (relative path)'.`gender` enum('male'.'female'.'unknow') NOT NULL DEFAULT 'unknow' COMMENT 'Employee gender'.`create_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Creation time'.`update_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Update time',
    PRIMARY KEY (`id`),
    KEY `idx_uid` (`uid`),
    KEY `idx_email` (`email`),
    KEY `idx_phone` (`phone`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee information (here is the general information, more can be vertically split table)';

Copy the code

System rights management model


-- Permission management: system map
CREATE TABLE `auth_ms` (
    `id` smallint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'on the id'.`ms_name` varchar(255) NOT NULL DEFAULT '0' COMMENT 'System Name'.`ms_desc` varchar(255) NOT NULL DEFAULT '0' COMMENT 'System Description'.`ms_domain` varchar(255) NOT NULL DEFAULT '0' COMMENT 'System Domain name'.`create_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Creation time'.`create_by` int(11) NOT NULL DEFAULT '0' COMMENT 'founder staff_id'.`update_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Update time'.`update_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Modifier staff_id'.`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Status 1:enable, 0:disable, -1:deleted',
    PRIMARY KEY (`id`),
    KEY `idx_domain` (`domain`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='System Map (register existing background system information)';

-- Permission management: system menu
CREATE TABLE `auth_ms_menu` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'on the id'.`ms_id` smallint(11) unsigned NOT NULL DEFAULT '0' COMMENT 'system id'.`parent_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Parent menu ID'.`menu_name` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Menu Name'.`menu_desc` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Menu Description'.`menu_uri` varchar(255) NOT NULL DEFAULT '0' COMMENT 'menu uri'.`create_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Creation time'.`is_show` enum('yes'.'no') NOT NULL DEFAULT 'no' COMMENT 'Show menu or not'.`create_by` int(11) NOT NULL DEFAULT '0' COMMENT 'founder staff_id'.`update_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Update time'.`update_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Modifier staff_id'.`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Status 1:enable, 0:disable, -1:deleted',
    PRIMARY KEY (`id`),
    KEY `idx_ms_id` (`ms_id`),
    KEY `idx_parent_id` (`parent_id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='the system menu';

-- Rights management: system rights
CREATE TABLE `auth_item` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'on the id'.`ms_id` tinyint(11) unsigned NOT NULL DEFAULT '0' COMMENT 'system id'.`menu_id` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Page/interface URI'.`create_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Creation time'.`create_by` int(11) NOT NULL DEFAULT '0' COMMENT 'founder staff_id'.`update_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Update time'.`update_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Modifier staff_id'.`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Status 1:enable, 0:disable, -1:deleted',
    PRIMARY KEY (`id`),
    KEY `idx_ms_menu` (`ms_id`.`menu_id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='System Permissions';

-- Rights management: System rights (various sets of rights)
CREATE TABLE `auth_role` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'on the id'.`name` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Role name'.`desc` varchar(255) NOT NULL DEFAULT '0' COMMENT 'Role Description'.`auth_item_set` text COMMENT 'Multiple values of permission set, separated by numbers'.`create_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Creation time'.`create_by` int(11) NOT NULL DEFAULT '0' COMMENT 'founder staff_id'.`update_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Update time'.`update_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Modifier staff_id'.`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Status 1:enable, 0:disable, -1:deleted',
    PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Employee Roles';

Rights management: roles and employee relationships
CREATE TABLE `auth_role_staff` (
    `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'on the id'.`staff_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'employee id'.`role_set` text COMMENT 'Multiple values in the role set, separated by numbers'.`create_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Creation time'.`create_by` int(11) NOT NULL DEFAULT '0' COMMENT 'founder staff_id'.`update_at` int(11) NOT NULL DEFAULT '0' COMMENT 'Update time'.`update_by` int(11) NOT NULL DEFAULT '0' COMMENT 'Modifier staff_id'.`status` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Status 1:enable, 0:disable, -1:deleted',
    PRIMARY KEY (`id`),
    KEY `idx_staff_id` (`staff_id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Rights roles and Employee Relationships';

Copy the code

Interaction design

Friendly tip: a large number of pictures are coming. There are many pictures here. If you are not clear, you can click the big picture to view

registered

After a successful registration, there are at least two interaction modes:

  1. Registration successful -> Jump to the login page
  2. Successful registration -> Automatic login -> Jump to the home page of the application (or other pages)

The specific interaction process is as follows:

landing

Quick login

The shortcut login process is basically the same as above except that the authentication password is replaced by the verification code.

Third party login

The problem with third-party login interaction is:

  1. Do you still need to bind your mobile phone number /Email after logging in to the third-party account successfully?

Because I found that some PMS, in order to improve the simplicity and speediness of user use, often generate uid directly after third-party login without binding the account. In this way, the account binding will involve account merging, which is very troublesome (if there is a wallet, etc.). If we bind from the start, so that future account relationships are clear and easy to maintain, the third party login is essentially an alias for the normal account. The result of this is that the account table account_user is one-to-many or one-to-one with the third party user information table account_platform.

  1. If yes, can the registered mobile phone number /Email be bound?

This is fine, but in general, the binding selection is basically the right one. Finally, the specific flow chart is as follows:

The interactive interface diagram is as follows:

Background authority management

First of all, our backstage management System needs a loud title. After thinking about Apollo for a while, our company used Mars, but suddenly came out with Earth, the root of all things on earth. It just so happens that we are a full-business basic service management System

The rights management function of Earth System is divided into the following four parts:

  • The Manage System Page
    • Edit page
    • List page
  • The Menu Page
    • Edit page
    • List page
  • Role Management (The Role Page)
    • Edit page
    • List page
  • The Role Staff Map Page
    • Edit page
    • List page

The specific interaction is as follows:

Interface design

Application Layer Interface (external)

1. Register an interface

Request parameters:

field type Whether will pass describe
username string Will not pass The user account
email string Choose email or phone User mailbox
phone string Choose email or phone User phone Number
code int Will pass Verification code

Interactive mode 1 (skip to the login page)

{
    "code": "200"."msg": "OK"."result": []}Copy the code

Interaction Mode 2 (skip to the home page)

{
    "code": "200"."msg": "OK"."result": {
        "s_token": "String, user session identifier"."s_token_expire": "String, user session indicates expiration time, 0 does not expire"."username": "String, user name"."nickname": "String, user nickname"."avatar": "String, user profile picture"."gender": "String, user gender, male: male, female: female, other: unknown",}}Copy the code

2. Login interface

Request parameters:

field type Whether will pass describe
username string Choose one of the three options: username, email, or phone The user account
email string Choose one of the three options: username, email, or phone User mailbox
phone string Choose one of the three options: username, email, or phone User phone Number
password string Will pass password

Response content:

{
    "code": "200"."result": {
        "s_token": "String, user session identifier"."s_token_expire": "String, user session indicates expiration time, 0 does not expire"."nickname": "String, user nickname"."username": "String, user name"."avatar": "String, user profile picture"."gender": "String, user gender, male: male, female: female, other: unknown",}}Copy the code

3. Shortcut login interface

Request parameters:

field type Whether will pass describe
email string Choose email or phone User mailbox
phone string Choose email or phone User phone Number
code int Will pass Verification code

Response content:

{
    "code": "200"."result": {
        "s_token": "String, user session identifier"."s_token_expire": "String, user session indicates expiration time, 0 does not expire"."nickname": "String, user nickname"."username": "String, user name"."avatar": "String, user profile picture"."gender": "String, user gender, male: male, female: female, other: unknown",}}Copy the code

4. Third-party login interface

Request parameters:

field type Whether will pass describe
type string Will pass Platform type 1: facebook, 2: Google, 3:4: qq, wechat, 5: weibo, 6: twitter
platform_id string Will pass User ID of the third-party platform
platform_token string Will pass Third-party platform token

Response content:

{
    "code": "200"."result": {
        "s_token": "String, user session identifier"."s_token_expire": "String, user session indicates expiration time, 0 does not expire"."username": "String, user name"."nickname": "String, user nickname"."avatar": "String, user profile picture"."gender": "String, user gender, male: male, female: female, other: unknown",}}Copy the code

5. Interface for modifying user information

Request parameters:

field type Whether will pass describe
username string Will not pass The user account
nickname string Will not pass nickname
avatar string Will not pass Image url
gender string Will not pass User gender: male, female, other: unknown

Response content:

{
    "code": "200"."result": {
        "username": "String, user name"."nickname": "String, user nickname"."avatar": "String, user profile picture"."gender": "String, user gender, male: male, female: female, other: unknown",}}Copy the code

6. Verify user login status

Request parameters:

field type Whether will pass describe
s_token string Will pass User session identifier

Response content:

{
    "code": "200"."result": {
        "s_token_expire": "String, user session indicates expiration time, 0 does not expire, -1 login invalid",}}Copy the code

Service interface (base service, internal)

Account services:

  1. registered

Request parameters:

field type Whether will pass describe
username string Will not pass The user account
email string Choose email or phone User mailbox
phone string Choose email or phone User phone Number

Interactive mode 1 (skip to the login page)

{
    "code": "200"."msg": "OK"."result": {
        "uid": "String, account ID"}}Copy the code
  1. landing

Request parameters:

field type Whether will pass describe
username string Will not pass The user account
email string Choose email or phone User mailbox
phone string Choose email or phone User phone Number
password string Will pass password

Response content:

{
    "code": "200"."msg": "OK"."result": {
        "uid": "String, account ID"}}Copy the code
  1. Third party login

Request parameters:

field type Whether will pass describe
type string Will pass Platform type 1: facebook, 2: Google, 3:4: qq, wechat, 5: weibo, 6: twitter
platform_id string Will pass User ID of the third-party platform
platform_token string Will pass Third-party platform token

Response content:

{
    "code": "200"."result": {
        "uid": "String, account ID"."nickname": "String, user nickname"."avatar": "String, user profile picture",}}Copy the code

Access service

  1. Obtaining the System Menu

Request parameters:

field type Whether will pass describe
ms_id string Will pass System ID

Response content:

{
    "code": "200"."msg": "OK"."result": {
        "ms_name": "String, system name"."ms_desc": "String, system description"."ms_domain": "String, system domain name"."list": [{"parent_id": "String, parent menu ID"."menu_id": "String, menu ID"."menu_name": "String, menu ID"."menu_desc": "String, menu description"."menu_uri": "Menu uri string,"."child": [{"parent_id": "String, parent menu ID"."menu_id": "String, menu ID"."menu_name": "String, menu ID"."menu_desc": "String, menu description"."menu_uri": "Menu uri string,"."child"[]}]}}Copy the code
  1. Permission to check

Request parameters:

field type Whether will pass describe
menu_id string Will pass Menu ids

Response content:

{
    "code": "200"."msg": "OK"."result": []}Copy the code

conclusion

If there are wrong or imperfect places, I hope you can comment more, learn from each other and make progress

Project address: github.com/skr-shop/ma…

Skr-shop project member profile

In no particular order, in lexicographical order

nickname Introduction to the Personal blog
AStraw Graduate student entrepreneur, currently engaged in the back-end research and development of the mall in the overseas mall group of Xiaomi Technology ——–
Big fool Dayu PHP third-party payment aggregation project used by most people in ChinaPaymentThe author, a former entrepreneur, is currently engaged in the back-end research and development of the mall in the overseas mall group of Xiaomi Technology Big fool Talk
lwhcv He used to work in Baidu/Rong360, and now he is engaged in back-end research and development of mall in overseas mall group of Xiaomi Technology ——–
TIGERB PHP frameworkEasyPHPThe author, with A/B/C round of e-commerce start-up company working experience, is currently engaged in mall back-end research and development work in xiaomi Overseas Mall Group TIGERB.cn