User system designed for e-commerce system

  E-commerce, php

图片描述

E-commerce guys use it everyday, like a cat, a dog, etc.
The design of e-commerce system looks complicated and simple, and looks simple and complicated.
This chapter is suitable for junior engineers and mid-level engineers to look closely. Big brothers, please feel free to do so.

Preface

The design starts with the following tools

  • PHP is the development language.
  • Based on Laravel Framework
  • MySQL is the data store

The variability of e-commerce is the same as the children’s mood, which changes very fast. Therefore, most of the functions should be connected and developed well in the design, and the number of refactorings should be reduced as much as possible. Cost savings for bosses and “cherishing life” for programmers.

data sheet

When the preliminary business is simple, we can design the data table to look like the following

TableName Comments
member 用户表
member_address 收货地址表
member_card 银行卡表
member_cart 购物车表
member_cart_item 购物车商品表
member_collect_product 商品收藏表
member_collect_supplier 店铺收藏表
member_data 用户信息表
member_query_history 用户搜索历史表
member_wallet 用户账户表
member_withdrawal 用户提现表

User table

Considering various login methods, the data table should include openid,unionid, user token of Alipay and QQ of WeChat, etc. These should be involved in the early stage. Since adding a field after a large number of users in the late stage is a nightmare, the user status status is also essential, and the comparators are also good or bad. The second is the creation time, login time, etc. The user table and the user information table must be bound, which is not to mention.

CREATE TABLE `member` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `tel`bigint (20) default null comment' mobile phone number',
 `password`varchar (555) collatetf8mb4 _ unicode _ cidefault null comment' login password',
 `wx _ TOKEN`varchar (125) collatetf8mb4 _ unicode _ cidefault null comment 'wechat token',
 `im _ token`varchar (255) collatetf8mb4 _ unicode _ cinot null comment' user Rong Yun token',
 `open_id` varchar(125) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `status`enum ('1',' 1') collateutf8mb4 _ unicode _ ci not null default' 1' comment' account status',
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `member_tel_unique` (`tel`),
 UNIQUE KEY `member_wx_token_unique` (`wx_token`)
 ) ENGINE=InnoDB AUTO_INCREMENT=95 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

clipboard.png

Receiving address table

The receiving address is one-to-one relative to the user. Add the required fields in the design, such as the consignee, the number of the consignee’s personnel, the city, the detailed address, etc.

CREATE TABLE `member_address` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `member _ id`int (11) not null comment' user number',
 `nick _ name`varchar (255) collatetf8mb4 _ unicode _ cinot null comment' consignee name',
 `tel`varchar (255) collatetf8mb4 _ unicode _ ci not null comment' mobile phone number',
 `prov`int (11) defaultnullcomment' province',
 `city`int (11) not null comment',
 `area`int (11) defaultnullcomment' area',
 `address`varchar (255) collatetf8mb4 _ unicode _ cinotnull default'' comment' street address',
 `number`int (11) not null comment' zip code',
 `default`enum ('0',' 1') collateutf8mb4 _ unicode _ ci not null default' 0' comment' default shipping address 1= > default',
 `deleted_at` timestamp NULL DEFAULT NULL,
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

clipboard.png

Bank card form

It is used for the user’s cash withdrawal business, etc. It is generally enough to record the information required by the bank card, such as the cardholder, card number, home bank, etc.

CREATE TABLE `member_card` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `member _ id`int (11) not null comment' user code',
 `card _ name`varchar (25) collatetf8mb4 _ unicode _ ci not null comment' cardholder name',
 `card _ number`varchar (25) collatetf8mb4 _ unicode _ cinot null comment' bank card number',
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `member_card_card_number_unique` (`card_number`)
 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

clipboard.png

Shopping cart table

There is also a certain reason why this table is built separately. normally, only the member_cart_item table is required. according to the actual offline business scenario, you need to take a shopping cart to the supermarket for normal shopping, but this shopping cart does not belong to you. after you use it, you need to return it, others can continue to use it, and the shopping cart is not made public. The business scenario is relatively narrow. For example, Jingdong Home is the same as Jingdong Mall (I’m just giving an example, and I don’t know how they do it). Shopping carts are not common. How do you distinguish between shopping carts or shopping cart products? I think you already know.

CREATE TABLE `member_cart` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `member _ id`int (11) not null comment' user code',
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `member_cart_member_id_unique` (`member_id`),
 KEY `member_cart_member_id_index` (`member_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Shopping cart list

One point that needs to be mentioned here is that [not all tables are designed to be bound to each other and depend on each other], for example, shopping cart commodity tables not only store commodity codes, but also store commodity prices, commodity descriptions and commodity specifications (SKU). It is not possible to find the existence of commodities because sellers remove commodities from shelves. Compared with the principle that all commodities are based on users and users are God, commodities cannot disappear quietly. Therefore, when doing shopping cart commodity table query, remember not to use join or table association query

CREATE TABLE `member_cart_item` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `cart _ id`int (11) not null comment' cart code',
 `product _ desc`varchar (255) collatetf8mb4 _ unicode _ cinotnull comment' item sku information',
 `product _ img`varchar (255) collatetf8mb4 _ unicode _ cinotnull comment' merchandise snapshot',
 `product _ name`varchar (255) collatetf8mb4 _ unicode _ cinotnull comment' trade name',
 `price`decimal (8,2) not null default' 0.00' comment' price',
 `product _ id`int (11) not null comment' commodity code',
 `supplier _ id`int (11) not null comment' store code',
 `sku _ id`int (11) not null comment' commodity sku code',
 `number`int (11) not null default' 1' comment' quantity of goods',
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `member_cart_item_cart_id_product_id_supplier_id_index` (`cart_id`,`product_id`,`supplier_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

User search history table

The record of user search is a must, in order to prepare for future data analysis and intelligent recommendation, after all, this is the era of information sharing.

CREATE TABLE `member_query_history` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `member _ id`int (11) not null comment' user code',
 `keyword`varchar (125) collatetf8mb4 _ unicode _ cinotnull comment' keyword',
 `created_at` timestamp NULL DEFAULT NULL,
 `updated_at` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Data record

There are many scenes, the title and content should be stored directly, which is similar to the store and merchandise collected. No matter what the seller does, the user’s shopping cart and the order cannot be moved. This is the benchmark.

Thank you

Thank you for seeing here. In the next article, I will talk about the product design of e-commerce system. If you have any questions, please ask them in the comment section. Thank you