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
| TableName | Comments |
|---|---|
| member | The users table |
| member_address | Shipping address list |
| member_card | Bank card table |
| member_cart | The cart table |
| member_cart_item | Shopping cart list |
| member_collect_product | Collection list |
| member_collect_supplier | Store collection list |
| member_data | User information table |
| member_query_history | The user searches the history table |
| member_wallet | User account table |
| member_withdrawal | Customer withdrawal form |
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 codeShopping 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 codeThe 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 codeData 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.