[Understanding in a single minute] Analysis and design of the initial construction of the commodity module of the e-commerce system

Why create a new column

The creation of the column "A Practical Understanding" is different from the previous personal sharing articles of "Grace development" and my blog. My positioning of the column of "A Practical Understanding" is an attitude, high-standard technical column, and "Grace development" I will still talk about some of the technical problems we encounter in our daily life and my personal experience sharing.

Preface

This article is a summary of the three articles of "E-commerce System Design Commodities (Upper, Middle, Lower)" that I published in the past. It is an upgraded version of my experience over the years. It is more standardized in the text description and flowchart, here Also paste links to the previous three articles.

• Products of e-commerce system design ( 1) 16193ba20cb559 https://segmentfault.com/a/1190000015565599
• Products of e-commerce system design (middle) https://segmentfault.com/a/1190000015600143
• Products of e-commerce system design (Part 2) https://segmentfault.com/a/1190000015650942

The design of commodities occupies an important position in the e-commerce system. How to design a highly scalable and high-performance commodity system is not a simple matter. My design is based on the design of the Internet’s major players, and it is not completely correct. But it's not completely wrong. Now the e-commerce system I designed is already in use. If there is any problem in logic, I will promptly revise the design thinking part of my article on the e-commerce system.

Basic idea

See the picture above, let's first explain the relationship between system specifications and custom specifications, system attributes and custom attributes, and their meaning.

SPU

SPU (Standard Product Unit) standardized product unit

What is a standardized product unit?

Abandoning the term standardization, product unit? It is to take a product as a unit. For example, if you are a memo seller, when you buy from the manufacturer, you say I want iphonex 100 models with random specifications. When you buy, you do not consider the memory or screen size. At this time, you treat the iphonex product as a unit. This is the product unit. Let’s talk about standardization. It’s just a standard made by some people or one person, so it’s called a standardized product unit. Don’t refute me with the explanation on Baidu Encyclopedia. I’m just explaining SPU in a more easy-to-understand way.

For example, the price of iphonex is also different. They are 8888 for iphonex 64g and 18888 for iphonex 256g. At this time, we cannot create 2 spus to manage these 2 commodities. At this time, you need to use the concept of spu.

SKU

SKU (Stock Keeping Unit) inventory unit

What is an inventory unit?

Literally, inventory refers to how many pieces of a certain product are left in a certain specification. At this time, you can't just focus on the product. The above example iphonex has 2 products of different specifications. At this time, it is impossible to calculate the inventory of each specification ( is impractical to create 2 products. Future management will be very complicated. For example, Anta’s running shoes have more than a dozen sizes. Do you want to create more than a dozen products? ). At this time, you can only create sub-products for the current product. We call it specifications. For example, iphonex has two specifications for storage and color.

Is there something wrong with it? How to express the specific storage size and specific color? At this time, you need to create a sub-product of the specification, we call it an attribute

The combination of each attribute is a new product, we call it SKU, one SPU corresponds to N SKUs

In this way, N products are generated

iphonex 64G white
iphonex 32G black
iphonex 256G white etc...

System specifications/attributes

Why set up system specification attributes?

Misappropriating a Taobao image, the above are all specifications and attributes set according to the category brand

The main purpose is to facilitate merchants to add products and to uniformly manage the specifications and attributes of the products. Of course, an e-commerce system minimizes the use of system attributes and specifications in the case of early operation (it is convenient for merchants to move in).

Needless to say, custom attributes, how can it not allow merchants to add their own specifications and sizes?

SPU and SKU association

SPU corresponds to multiple SKUs. SPU is actually the main product list, similar to the iphonex mobile phone, while SKU is the specification list bound to this product, similar to iphonex red, iphonex black, etc.

The main table and the specification table are also associated with other tables

Merchandise album

In Taobao's logic, businesses can add videos and pictures to products, and can add pictures to each sku. We call it an album. Bind a set of pictures and videos to the merchandise table and sku table like an album by a singer or writer

Related brands

Each product belongs to a brand, for example, iphonex belongs to Apple, and Xiaomi 8 belongs to the same brand as Xiaomi. The brand does not need to be associated with the sku. The reason is very simple. The current sku belongs to the iphonex company and Apple, so naturally the specifications below the iphonex belong to Apple.

Binding category

Sometimes the brand not only belongs to a category, but also takes iphonex as an example. It is a mobile phone and an Apple product, but it is also a music player. Note that at this time, do not bind the current brand to the three categories. If you do, the maintainability in the future will be very low. The same brand name should be bound to each category. You must ask, isn't this data garbage generated? I have no specific data to show you the benefits of doing so.

But from the business point of view, now I need to count the number of purchases of products under each category to make user portraits. How do you distinguish which category the current product belongs to? It is impossible to distinguish, because you have bound the brand to 3 categories, and I don’t know which category the user clicked through to make the purchase.

In addition, many brand companies are not only making a product, similar to Sony's mp3, but also making TVs, mobile phones, game consoles and so on. Therefore, the category corresponds to multiple brands, and the brand should correspond to multiple categories instead of being associated with multiple categories

Link analysis

The commodity system and the order system are interlinked, and the buyer will go through a process after purchasing the commodity

Commodity System->Order System->Trading System->Order System->Logistic System->After-sale System

Completing the above process is the completion of a transaction, and children's shoes who often shop online understand this. Today we talk about the storage process from the commodity system to the trading system and the order system and the "pits" that should be paid attention to in its design.

Related issues

Now there is such a scene

Xiao Ming bought a mad mobile phone in a certain treasure, the color is red, the storage is 32G, he chooses to use a certain treasure to pay.

SKU	product	colour	storage
001	Crazy phone	Red	32G
002	Crazy phone	Red	256G
003	Crazy phone	black	32G
004	Crazy phone	black	256G

Xiao Ming chooses to purchase products with SKU=001. Under normal circumstances, the order form should be associated with this SKU code to maintain data consistency. like this

order number	user	SKU
SN110	Xiao Ming	001

There is a drawback to this design. The name and price of the product are not fixed. If the merchant changes the title or other attributes of the product, then Xiao Ming bought the Aimad phone at 8000 yuan, but after a few years, the price was reduced by the merchant. The price, as a result, Xiao Ming’s purchase list has also become the revised price, so this mere association design is not desirable (at least not in the e-commerce system).

order number	user	SKU	Product title	Commodity price	Product cover image	Other attributes of the product
SN110	Xiao Ming	001	Crazy phone	8000	iphone.png	Other attributes

Like the design in the above table, someone will ask "What is the meaning of the association?"

My answer is "keep data association". Although merchants may change product attributes, they should record all user actions as much as possible.

Multi-merchant e-commerce

In fact, in the design of the e-commerce system, I feel that we should not distinguish between multi-merchant e-commerce and single-user e-commerce (at least developers should not distinguish between them), but the concept of multi-merchant should be brought into the system in the early design. Even if there is only one official store? !

When it comes to multiple merchants, it is necessary to consider the issue of unified ordering by users.

• The order is placed by the shopping cart, and multiple products come from multiple merchants
• If you split the order, split the order
• How to place an order notification and other multi-merchant issues

The issue of multi-merchants is not the focus of this chapter. This time I will raise these questions first. Interested students can think about it in advance, and follow-up articles will explain them in detail.

The order is placed by the shopping cart, and multiple products come from multiple merchants

If the order is from multiple merchants, then the database interface of the order should be designed like this

Order Form

order number	user
SN110	Xiao Ming

Order Details Table

order number	SKU	user	Merchant
SN110	001	Xiao Ming	official
SN110	002	Xiao Ming	Third party

No matter how many products are purchased and how many merchants the products belong to, we should assign the products purchased by users at one time to one order, and then associate the details of multiple products under the order. In the after-sales operation, it is also convenient for buyers to return and exchange individual products.

Backstage function list

The function name and URL are provided here for reference

Menu name	URL
Commodity management	/product
Publish goods	/product/create
Commodity category	/product/category
Brand management	/product/brand
Specification management	/product/attribute
Recycle bin	/product/recycle
Order List	/order

I will introduce the design and operation routines of the background in a separate article. Here is only a rough table.

Related data sheet

https://github.com/CrazyCodes/E-Commerce-SQL

Thanks

Thank you for seeing here, and I hope my article can help you. If you have any questions, you can leave a message in the comment area, and I will reply as soon as I see it. Thanks!