E-commerce users use it every day, like some cat, some dog, etc. This chapter is suitable for junior engineers and intermediate engineers to read carefully. Please feel free to be a big guy
preface
Product design is dominant in the electricity system, how to design the high extension, high-performance commodity system is not a simple thing, my design is to inspect the Internet after the design of self study, is not entirely correct, but it’s not entirely wrong, now I design of the electricity system is already in use, if logically have what problem, I will timely modify the design thinking part of my article on e-commerce system.
The element
SPU
Standard Product Unit (SPU)
What is a standardized product unit?
Discard the word standardization, product unit? It’s one product per unit. For example, you are a notebook seller, you said I want iphoneX 100 models at random specifications at the time of manufacturer purchase, purchase did not consider the memory or screen size, this time you put iphoneX this product as a unit. That’s the product unit. Talking about standardization, it is just a standard developed by some people or one person, so it is called standardized product unit. Don’t refute me with the explanation on Baidu Baike. I just explain SPU in a more understandable way.
For example, the price of iphoneX is 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
Stock Keeping Unit (SKU)
What is a unit of inventory?
Literally, inventory refers to how many pieces of a given item of a given specification are available. In this case, you can’t just focus on the item. In the example above, the iphonex has 2 different sizes of goods. At this time, it is impossible to calculate the inventory of each size (creating 2 items is impractical, and the management will be complicated in the future, for example, anta’s running shoes have a dozen sizes, why should we create more than a dozen items?). At this time, we can only create sub-products based on the current product, we call it specifications. For example, iphonex has two specifications: storage and color
Did you find or have a problem? Then how to express the specific storage size and specific color? At this point, we need to create sub-items of the specification, which we call attributes
The combination of each of these attributes is a new product, which we call a SKU. One SPU corresponds to N SkUs
- Iphonex 64 g white
- Iphonex 32 gb black
- Iphonex 256G white etc…
System specifications/attributes
Why set system specification attributes?
It is mainly for the convenience of merchants to add commodities and unified management of commodity specification attributes. Of course, an e-commerce system should minimize the use of system attribute specifications in the early operation of the system (for the convenience of merchants to check in).
The custom properties go without saying. Do not let the business to add their own specifications and sizes of what can?
data
The specific data table design is as follows
product
List of Goods (SPU)
CREATE TABLE `product` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Title of goods',
`category_id` int(11) NOT NULL COMMENT 'Trade Classification Number',
`mer_id` int(11) NOT NULL COMMENT 'Merchant Number',
`freight_id` int(11) DEFAULT NULL,
`type_id` tinyint(4) NOT NULL COMMENT 'Type number',
`sketch` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'brief',
`intro` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Description',
`keywords` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Product Keywords',
`tags` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'tags',
`marque` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Product type',
`barcode` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Warehouse bar code',
`brand_id` int(11) NOT NULL COMMENT 'Brand number',
`virtual` int(11) NOT NULL DEFAULT '0' COMMENT 'Virtual Purchase',
`price` decimal(8,2) NOT NULL COMMENT 'Commodity price',
`market_price` decimal(8,2) NOT NULL COMMENT 'Market price',
`integral` int(11) NOT NULL DEFAULT '0' COMMENT You can use the integral to cancel.,
`stock` int(11) NOT NULL COMMENT 'Inventory',
`warning_stock` int(11) NOT NULL COMMENT 'Stock warning',
`picture_url` varchar(125) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Cover Image',
`posters` varchar(125) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`status` tinyint(4) NOT NULL COMMENT 'Status -1=> Off shelf,1=> on shelf,2=> pre-sale,0=> Off shelf',
`state` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Audit Status -1 Audit failed 0 Audit not 1 Audit successful',
`is_package` enum('0'.'1') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT 'Is it a package?',
`is_integral` enum('0'.'1') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '0' COMMENT 'Is it an integral product?',
`sort` int(11) NOT NULL DEFAULT '99' COMMENT 'order',
`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=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the codesystem_attribute
System specification table
CREATE TABLE `system_attribute` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL COMMENT 'Trade Class No.',
`name` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Attribute name',
`sort` int(11) NOT NULL DEFAULT '999' COMMENT 'Permutation order',
PRIMARY KEY (`id`),
KEY `product_attribute_category_id_name_index` (`category_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the codesystem_attribute_option
System property sheet
CREATE TABLE `product_attribute_option` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(125) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Option name',
`attr_id` int(11) NOT NULL COMMENT 'Attribute coding',
`sort` int(11) NOT NULL DEFAULT '999' COMMENT 'order',
PRIMARY KEY (`id`),
KEY `product_attribute_option_name_attr_id_index` (`name`,`attr_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the codeproduct_attribute_and_option
Specification property binding table
CREATE TABLE `product_attribute_and_option` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`sku_id` int(11) NOT NULL COMMENT 'sku code',
`option_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Attribute Option encoding',
`attribute_id` int(11) NOT NULL COMMENT 'Attribute coding',
`sort` int(11) NOT NULL DEFAULT '999' COMMENT 'order',
`supplier_option_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `product_attribute_and_option_sku_id_option_id_attribute_id_index` (`sku_id`,`option_id`,`attribute_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6335 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the codeproduct_sku
Sku table
CREATE TABLE `product_sku` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL COMMENT 'Trade code',
`name` varchar(125) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'sku name',
`img` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'the main figure',
`price` decimal(8,2) NOT NULL COMMENT 'price',
`stock` int(11) NOT NULL DEFAULT '0' COMMENT 'inventory',
`code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Trade code',
`barcode` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Bar code',
`data` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'sku list', PRIMARY KEY (`id`), KEY `product_sku_name_product_id_index` (`name`,`product_id`) ) ENGINE=InnoDB AUTO_INCREMENT=530 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;Copy the codeproduct_attribute
Customize the specification table
CREATE TABLE `product_attribute` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`product_id` int(11) NOT NULL COMMENT 'Trade code',
`name` varchar(125) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Specification name',
`sort` int(11) NOT NULL DEFAULT '999' COMMENT 'order',
PRIMARY KEY (`id`),
KEY `product_supplier_attribute_name_product_id_index` (`name`,`product_id`)
) ENGINE=InnoDB AUTO_INCREMENT=40 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the codeThank you
In fact, it is very simple to understand the product design level, and the following articles mainly explain how to store these data more reasonably. If the design is not right, for example adding goods is easy, but modifying goods is complicated. In the early design we try to avoid these “pits.”
Thank you for reading here. I hope my article can help you. If you have any questions, please leave them in the comment section. I will reply as soon as I see them. thank you