Why create a new column
Unlike previous Grace Development and my blog posts, I set up this column as a technical column with attitude and high standards. In Grace Development, I will still talk about some technical problems we encounter in daily life and share my personal experience.
preface
This article is a summary of the three articles “E-commerce System Design Products (Top, Middle and Bottom)” I published in Sifu. It is an upgraded version combined with my experience in these years. The text description and flow chart are more standardized, and the links of the three articles are also attached here.
- E-commerce system design of goods (1) juejin.cn/post/684490…
- E-commerce system design of goods (middle) juejin.cn/post/684490…
- E-commerce system design of goods (below) juejin.cn/post/684490…
Goods has an important role in the design of the electricity system is, how to design the high extension, high-performance commodity system is not a simple thing, my design is a reference to the Internet each after the design of self study, is not entirely correct, but it’s not entirely wrong, now I design the electricity system is already in use, if logically have what problem, I will timely modify the design ideas of my articles on e-commerce system.
The basic idea
SPU
Standard Product Unit (SPU) Standardized Product Unit
What is a standardized product unit?
Discard the word standardization, product unit? It’s a product as a unit. For example, if you are a seller of notes, you say “I want iphoneX 100 models at will” when the manufacturer purchases, and the specifications are arbitrary. When you purchase, you do not consider the memory or screen size, so you treat iphoneX as a unit. That’s the product unit. Talking about standardization, it is just a standard made by some people or one person, so it is called standardized product unit. Don’t refute me with the explanation on Baidu Encyclopedia, I just explain SPU in a more accessible way.
For example, the price of iphoneX is also different, respectively, iphoneX 64G is 8888, iphoneX 256G is 18888. At this time we cannot set up two SPUs to manage these two goods. This is where the spU concept comes in.
SKU
SKU(Stock Keeping Unit
What is inventory unit?
Literally, inventory is how many units of a given item are available for a given specification, so it’s not just the item. Iphonex has two different specifications of goods, at this time, it is impossible to calculate the inventory of each specification (it is impractical to create two products, the future management will be very complicated, for example, anta running shoes have more than ten sizes, do we need to create more than ten products?) At this time, we can only create a sub-item for the current item, which is called specifications. For example, iphoneX has two specifications: storage and color
Did you notice anything or something? Then how to express the specific storage size and specific color? This is the time to create the specification’s child goods, which we call properties
This combination of each attribute is a new good, which we call an SKU, with N SKUs for each SPU
So we’re going to have N goods
Iphonex 64G White iphoneX 32G Black iphoneX 256G white etc…
System specifications/attributes
Why set up system specification attributes?
Embezzle the graph of treasure of a piece of treasure, above is set according to classification brand good specification and attribute
The main purpose is to facilitate merchants to add commodities and unified management of commodity specifications and attributes. Of course, an e-commerce system should minimize the use of system attributes and specifications in the case of early operation (convenient for merchants to check in).
Custom attributes are needless to say, how can we not let businesses add their own specifications and sizes?
Association between SPU and SKU
SPU corresponds to multiple SKUs. SPU is actually the main commodity table, similar to iphoneX, while SKU is the specification table bound to this commodity, similar to iphoneX red model, iphoneX black model, etc.
The master table and the specification table are also associated with other tables
Commodity album
In taobao’s logic, merchants can add videos and pictures for goods, and they can add pictures for each SKU. We call it an album. Bind a group of pictures and videos to the goods list and SKU list, just like a singer or writer produces an album
Related to the brand
Each product belongs to a brand, for example, iphonex belongs to apple company, and mi 8 belongs to xiaomi company. The brand does not need to be associated with the SKU, the reason is very simple, the current SKU belongs to Apple, naturally iphoneX below the specifications belong to Apple.
The binding of category
Sometimes the brand doesn’t belong to just one category, or iphonex, for example, which is a mobile phone and apple product but also a music player. Be careful not to tie your current brand to three categories at this time, if you do, future maintainability will be low. Should be bound to the same brand name in each category, you have to ask so that the data garbage is not generated. I don’t have data to show you the benefits of doing that.
But let’s start with business. Now I need to count the number of purchases under each category to make user portraits. How do you distinguish which category the current product belongs to? It’s impossible to tell, because you’ve tied the brand to three categories, and you don’t know which category the user clicked in to buy from.
Moreover, many brands are not only making a product, like SONY, which makes MP3 players, but also TVS, mobile phones, game consoles and so on. Therefore, categories correspond to multiple brands, and brands should be associated with multiple categories rather than multiple categories
Link analysis
The goods system and the order system are complementary, when the buyer purchases the goods will go through a process
Commodity system -> order system -> trading system -> order system -> logistics system -> after-sales system
Completing the process is completing a transaction, as any regular online shopper knows. Today we’ll look at stored procedures and their design pitfalls from commodity systems to trading systems and order systems.
Associated problems
Now here’s a scenario
Xiaoming bought an iPhone on Some Bao. The color is red and the storage is 32 GB. He chose to use Some Bao to pay.
| SKU | product | color | storage |
|---|---|---|---|
| 001 | iphone | red | 32G |
| 002 | iphone | red | 256G |
| 003 | iphone | black | 32G |
| 004 | iphone | black | 256G |
Xiao Ming chooses to purchase products with SKU=001. Normally, the order form should be associated with this SKU code to maintain data consistency. Like this,
| The order number | The user | SKU |
|---|---|---|
| SN110 | Xiao Ming | 001 |
This design has a disadvantage. The name and price of the product are not fixed. If the merchant modifies the title or other attributes of the product, the iPhone phone xiao Ming bought at that time is 8000 yuan. So this kind of association-only design is not desirable (at least not in e-commerce systems).
| The order number | The user | SKU | Commodity title | Commodity prices | Product cover | Other attributes of commodity |
|---|---|---|---|---|---|---|
| SN110 | Xiao Ming | 001 | iphone | 8000 | iphone.png | Other attributes |
As in the design above, one may ask, “What is the significance of the correlation?”
My answer is “maintain data correlation”. Although merchants may change the attributes of products, they should record all actions of users as much as possible.
Multi-merchant e-commerce
In fact, in the design of e-commerce system, I feel that multi-merchant e-commerce and single-user e-commerce should not be distinguished (at least developers should not distinguish 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? !
Involve much merchant to need to consider user unified order problem.
- Orders are placed from shopping carts, and multiple items come from multiple merchants
- If a single, single
- How to order notice and so on multi-merchant issues
The multi-merchant issue is not the focus of this chapter, so I will put forward these questions first. If you are interested, you can think about them in advance, and the subsequent article will explain them in detail
Orders are placed from shopping carts, and multiple items come from multiple merchants
If the order is for items from multiple merchants, the database interface for the order should be designed like this
The order sheet
| The order number | The user |
|---|---|
| SN110 | Xiao Ming |
Order Details
| The order number | SKU | The user | merchants |
|---|---|---|---|
| SN110 | 001 | Xiao Ming | The official |
| SN110 | 002 | Xiao Ming | The third party |
No matter how many goods are purchased and how many merchants the goods belong to, we should attribute the goods purchased by users at one time to one order, and then associate the details of multiple goods under the order. In the after-sales operation, it is also convenient for buyers to return a single commodity.
Background Function List
The following function names and urls are provided for reference
| The name of the menu | URL |
|---|---|
| Commodity management | /product |
| Release goods | /product/create |
| Commodity categories | /product/category |
| Brand management | /product/brand |
| Specification management | /product/attribute |
| The recycle bin | /product/recycle |
| The order list | /order |
I will introduce the backstage design and operation routines in a separate article. Here’s just a rough table.
Table of related data
Github.com/CrazyCodes/…
Thank you
Thank you for reading this and I hope my article can help you. If you have any questions, please leave a message in the comment section. I will reply as soon as I see it. Thank you very much!
communication
There is life, there is code.
Spread the positive energy of technology and continue to learn new knowledge.