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

In the last article, we talked about the concept of e-commerce SPUS and SKUs, why custom properties and custom specifications are designed, and when they can be used. I have always said that e-commerce is both a simple and complex thing. In this chapter, we once again analyze more logic and implementation of e-commerce system product design.

associated

SPU corresponds to multiple SKUs. SPU is actually the main product list, similar to iphoneX, while SKU is the specification table bound to this product, similar to iphoneX red model, iphoneX black model, etc.

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

The album

In Taobao’s logic, merchants can add videos and pictures for goods, and pictures can be added for each SKU. We call them albums. Bind a set of pictures and videos to the merchandise list and SKU list, similar to an album by a singer or writer

product_album

CREATE TABLE `product_album` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL COMMENT 'Commodity no.',
  `name` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Trade name',
  `url` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Picture address',
  `size` int(11) DEFAULT NULL COMMENT 'Video size',
  `intro` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Picture introduction',
  `sort` int(11) NOT NULL DEFAULT '999' COMMENT 'order',
  `status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Picture Status',
  `state` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Resource type 0=> Image 1=> Video',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

brand

Each product belongs to a brand, such as the iphonex belongs to apple, and the mi 8 belongs to xiaomi. The brand does not need to be associated with the sku, the reason is very simple, the current sku is the iphonex belongs to apple, naturally, the specifications under the iphonex belong to apple.

product_brand

CREATE TABLE `product_brand` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_category_id` int(11) NOT NULL COMMENT 'Trade Class No.',
  `name` varchar(25) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Brand name',
  `image_url` varchar(125) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'image url',
  `sort` int(11) NOT NULL DEFAULT '999' COMMENT 'Permutation order',
  `status` tinyint(4) NOT NULL COMMENT 'state',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_brand_name_unique` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

category

Sometimes brands don’t just belong to one category. Take the iphonex for example. It’s a phone and it’s an apple product but it’s also a music player. Note that at this point do not bind the current brand to the three categories; if you do, future maintainability will be poor. Each category should be bound with the same brand name, so you have to ask, doesn’t that create data garbage? I don’t have the numbers to show you the benefits of that.

But starting from business, now I need to count the number of purchases of goods under each category to make user portraits. How do you distinguish the current category of goods? You can’t distinguish because you’re tying the brand to three categories and you don’t know which category the user clicked into to buy from.

In addition, many brands do not just make a product, such as SONY mp3 also do TV, mobile phone, game console and so on. Therefore, categories correspond to multiple brands, and brands should be related to multiple categories rather than multiple categories

product_category

CREATE TABLE `product_category` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'Classification Table',
  `pid` int(11) NOT NULL COMMENT 'Parent class Number',
  `cover` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Cover Image',
  `index_block_status` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'Home block level status 1=> Display',
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT 'Status 1=> Normal',
  `sort` int(11) NOT NULL DEFAULT '999' COMMENT 'order',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code

Thank you

In the next section, we will talk about the design of products and backend operations after users buy them. A good programmer should think about how their own people add products and manage them. Can’t happy users bitter operation? 😄

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