E-commerce platform SKU and SPU database design: How to take into account both user-defined attributes and attributeless products?

E-commerce platform SKU and SPU database design: Flexible response to custom attributes and attributeless products

This article discusses the design of SKU and SPU databases on e-commerce platforms, focusing on solving the compatibility issues of user-defined sales attributes and products without sales attributes. These two scenarios correspond to the product management model of traditional e-commerce platforms (such as Taobao and JD) and some group buying platforms (such as Meituan Maicai).

First, we review the concepts of SKU and SPU: SPU (Standard Product Unit) represents standard product units, such as "Apple iPhone 15"; SKU (Stock Keeping Unit) represents inventory units, such as "Apple iPhone 15, 256G, Space Gray". The SPU contains basic attributes (such as brand, model, weight) and sales attributes (such as color, memory). The sales attribute combination forms different SKUs, and each SKU corresponds to the specific price and inventory.

Challenge 1: Support user-defined sales attributes

The category-related sales attributes (such as the "color" and "memory" of the mobile phone) preset by the platform can meet most products, but users may need to customize attributes, such as "mobile case lettering style". How to deal with it?

Solution: Flexible table structure design.

• Product table: Stores basic product information (product ID, product name, etc.).
• Attribute table: Store sales attribute names (attribute ID, "color", "memory", "letter style", etc.).
• Attribute value table: Stores the specific values ​​of sales attributes (attribute value ID, attribute ID, attribute value "red", "128G", "personal customization A", etc.).
• SKU table: Stores SKU information (SKU ID, product ID, attribute value ID combination).

Through this design, users can customize the addition of attributes and attribute values. The SKU table stores SKU details through the associated attribute value table, avoiding maintenance problems.

Challenge 2: Handle products without sales attributes

Some products, such as "5 kilogram apples", may not have sales attributes. The traditional Cartesian product generates SKUs fails here.

Solution: Create a default SKU even if there are no sales attributes. When creating a product in the background, this single SKU is generated directly. The front-end judges whether to display sales attribute selection based on the number of SKUs. If there is only one SKU, it will be displayed and submitted directly.

Through the above design, e-commerce platforms can flexibly deal with custom attributes and attributeless products, and balance flexibility and maintainability in database design.

The above is the detailed content of E-commerce platform SKU and SPU database design: How to take into account both user-defined attributes and attributeless products?. For more information, please follow other related articles on the PHP Chinese website!