E-commerce is used by everyone every day, like some cat, some dog, etc. This chapter is suitable for junior engineers and intermediate engineers. Please feel free to read it carefully

preface

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 as fast as a child’s mood. Therefore, in the design, we should think well about the access and development of most functions and minimize the number of reconstruction. Cost saving for the boss, life saving for the programmer

The data table

In the early days when business was simple, we could design the data table as follows

The users table

Considering a variety of login methods, the data table should involve wechat OpenID, UnionID, Alipay, QQ user token, etc., which should be involved in the early stage, because it is a nightmare to add a field after the late user number is large, user status is also essential, comparing people is also good or bad, followed by the creation time, Login time, user table and user information table must be bound, this is not much to say.

CREATE TABLE 'member' (' id 'int(10) unsigned NOT NULL AUTO_INCREMENT,' tel 'bigint(20) DEFAULT NULL COMMENT' 主 键 ', 'password' varchar(555) COLLATE UTf8MB4_unicode_ci DEFAULT NULL COMMENT 'login password ', 'wx_token' varchar(125) COLLATE UTf8MB4_unicode_ci DEFAULT NULL COMMENT 'wechat TOKEN', 'im_token' varchar(255) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT 'untoken ', `open_id` varchar(125) COLLATE utf8mb4_unicode_ci DEFAULT NULL, 'STATUS' enum('1','-1') COLLATE UTF8MB4_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;Copy the code

Shipping address list

The receiving address is relative to the user one by one, so you can add the required fields in the design, such as consignee, consignee mobile phone number, city, detailed address, etc

CREATE TABLE `member_address` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 'member_id' int(11) NOT NULL COMMENT 'iD ', 'nick_name' varchar(255) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT '主 体 名', 'tel' varchar(255) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT '主 动 ',' prov 'int(11) DEFAULT NULL COMMENT' 主 动 ', 'city' int(11) NOT NULL COMMENT '表 ',' area 'int(11) DEFAULT NULL COMMENT' 表 ', Address 'varchar(255) COLLATE UTf8MB4_unicode_ci NOT NULL DEFAULT COMMENT ', 'number' int(11) NOT NULL COMMENT 'postcode ', 'default' enum('0','1') COLLATE UTF8MB4_unicoDE_CI NOT NULL default '0' COMMENT 'default 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;Copy the code

Bank card table

For the business of user withdrawal, the information required by bank card can be roughly recorded, such as cardholder, card number, belonging bank, etc

CREATE TABLE `member_card` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 'member_id' int(11) NOT NULL COMMENT 'userid ', 'card_name' varchar(25) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT 'card_name' varchar(25) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT 'card_name' varchar(25) COLLATE UTf8MB4_unicode_ci 'card_number' varchar(25) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT '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;Copy the code

The cart table

Normally, only member_CARt_item is needed. According to the actual offline business scenario, you need to take a shopping cart to the supermarket normally, but this shopping cart does not belong to you. After you use it, you need to return it, and others can continue to use it, and make the shopping cart public. It’s not about making cart items public. The business scenario is relatively narrow. For example, Jingdong Home is the same as Jingdong Mall (I’m just an example, and I don’t know how they do it). Shopping carts are not universal. I think you get the point.

CREATE TABLE `member_cart` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 'member_id' int(11) NOT NULL COMMENT 'userid ',' 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;Copy the code

Shopping cart list

The need to mention it is [not all of the design table is bound to each other, depend on each other’s], such as shopping cart goods table, not just to store commodity code, but also to commodity prices, commodity description and the specifications of the commodity (SKU) both store, not by the seller from the shelves of goods, and query the existence of the goods, is everything is given priority to with the user, The user is god’s principle, can’t let the goods quietly disappear. Therefore, do not use join or table associative query when doing shopping cart list query

CREATE TABLE `member_cart_item` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 'cart_id' int(11) NOT NULL COMMENT 'cart_id ', 'product_desc' varchar(255) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT 'product_desc' varchar(255) COLLATE UTf8MB4_unicode_ci 'product_img' varchar(255) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT 'product_img ', 'product_name' varchar(255) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT 'varchar ', 'price' decimal(8,2) NOT NULL DEFAULT '0.00' COMMENT, 'product_id' int(11) NOT NULL COMMENT '0.00', Supplier_id 'int(11) NOT NULL COMMENT' supplier_id 'int(11) NOT NULL COMMENT' supplier_id ', 'sku_id' int(11) NOT NULL COMMENT 'supplier_id ', 'number' int(11) NOT NULL DEFAULT '1' COMMENT '表 名 ',' 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;Copy the code

The user searches the history table

User search records are a must, in order to prepare for future data analysis, intelligent recommendation, after all, now 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 'iD ',' keyword 'varchar(125) COLLATE UTf8MB4_unicode_ci NOT NULL COMMENT' iD ', `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;Copy the code

Data records

There are many scenarios where the title and content are stored directly, similar to stores and goods collected. No matter what the seller does, the user’s shopping cart and order cannot be moved. This is the benchmark.

Thank you

Thank you for reading this. In the next post, I will talk about the product design of e-commerce system. Any questions can be asked in the comments section. thank you

communication

There is life, there is code.

Spread the positive energy of technology and continue to learn new knowledge.