SpringBoot actual e-business project mall (30K + STAR) address: github.com/macrozheng/…

Abstract

There are two problems in the original SKU design. One is that the SKU table design is relatively fixed and cannot be expanded. Another is that after modifying the commodity information, the COMMODITY SKU ID will change, because the shopping cart table and the order commodity table are associated with the COMMODITY SKU ID, so it will not match. Recently, we have done some optimization for these two problems. Let’s talk about the idea of optimization.

Spus and SKUs for goods

First of all, let’s understand the concept of SPU and SKU, which may be unknown to many friends who have not been in contact with e-commerce.

  • Standard Product Unit (SPU) : refers to the Standard Product Unit. It is the smallest Unit of Product information aggregation. It is a set of standardized information that can be reused and easily retrieved and describes the characteristics of a Product.
  • SKU (Stock Keeping Unit) : a minimum physical Unit of inventory that cannot be separated.

For example, now there is a mobile phone product called Mi 8, mi 8 has different properties, for example, it comes in black and blue, 32GB and 64GB versions. The Mi 8 is an SPU and the MI 8 Black 64G is an SKU.

SKU design of goods

Previous design

SKU information of a product is stored in the PMS_SKu_STOCK table. The sales attribute of a product is stored with the attributes of SP1, SP2 and SP3, which is inflexible and difficult to expand.

This leads to further problems, such as our shopping cart and order will need to store sales attributes, which will require sp1, SP2, and SP3 attributes.

Improved design

Since the sales attribute of the goods is dynamic, there is no way to determine how many there are. At this time, we can use JSON format to store the goods and add sp_data field to the pMS_SKu_STOCK table.

Sp_data stores a JSON array. For example, a black cell phone with a capacity of 32 GB stores the following information.

[{"key": "Color"."value": "Black"
    },
    {
        "key": "Capacity"."value": "32G"}]Copy the code

After this modification, both the original shopping cart table OMs_CARt_item and the order item table OMs_order_item can store the sales attribute in JSON format, using the product_attr field.

Modify the SKU associated with goods

Previous practice

The SKU information of a product is associated with the product and will be modified when the product information is modified. The previous approach was to simply delete all SKU information for the item and add it again. This will cause the ID in the item SKU to be changed, and the original ID will be invalid because the item SKU ID is associated with the shopping cart and the order item. Below is the original code to modify the SKU information in the product.

/** * Created by macro on 2018/4/26. */
@Service
public class PmsProductServiceImpl implements PmsProductService {
    
        @Override
        public int update(Long id, PmsProductParam productParam) {
            // omit some code...
            // Delete the SKU associated with the commodity
            PmsSkuStockExample skuStockExample = new PmsSkuStockExample();
            skuStockExample.createCriteria().andProductIdEqualTo(id);
            skuStockMapper.deleteByExample(skuStockExample);
            handleSkuStockCode(productParam.getSkuStockList(),id);
            // Insert all skUs passed inrelateAndInsertList(skuStockDao, productParam.getSkuStockList(), id); }}Copy the code

Improved practice

First of all, we need to agree with the front end that the newly added SKU information will not transmit ID, the SKU information to be modified will transmit ID, and the deleted SKU information will not transmit SKU information directly. Then we can determine the SKU information to be added, modified, and deleted according to the incoming SKU information. In this way, the ID of the original SKU can not be changed when updating the product SKU information. The specific process is as follows.

The specific code is as follows:

/** * Created by macro on 2018/4/26. */
@Service
public class PmsProductServiceImpl implements PmsProductService {
    private void handleUpdateSkuStockList(Long id, PmsProductParam productParam) {
        // Current SKU information
        List<PmsSkuStock> currSkuList = productParam.getSkuStockList();
        // There is no skU to delete directly
        if(CollUtil.isEmpty(currSkuList)){
            PmsSkuStockExample skuStockExample = new PmsSkuStockExample();
            skuStockExample.createCriteria().andProductIdEqualTo(id);
            skuStockMapper.deleteByExample(skuStockExample);
            return;
        }
        // Get the initial SKU information
        PmsSkuStockExample skuStockExample = new PmsSkuStockExample();
        skuStockExample.createCriteria().andProductIdEqualTo(id);
        List<PmsSkuStock> oriStuList = skuStockMapper.selectByExample(skuStockExample);
        // Obtain information about the newly added SKU
        List<PmsSkuStock> insertSkuList = currSkuList.stream().filter(item->item.getId()==null).collect(Collectors.toList());
        // Get the SKU information to be updatedList<PmsSkuStock> updateSkuList = currSkuList.stream().filter(item->item.getId()! =null).collect(Collectors.toList());
        List<Long> updateSkuIds = updateSkuList.stream().map(PmsSkuStock::getId).collect(Collectors.toList());
        // Obtain information about the SKU to be deletedList<PmsSkuStock> removeSkuList = oriStuList.stream().filter(item-> ! updateSkuIds.contains(item.getId())).collect(Collectors.toList()); handleSkuStockCode(insertSkuList,id); handleSkuStockCode(updateSkuList,id);/ / new sku
        if(CollUtil.isNotEmpty(insertSkuList)){
            relateAndInsertList(skuStockDao, insertSkuList, id);
        }
        / / delete the sku
        if(CollUtil.isNotEmpty(removeSkuList)){
            List<Long> removeSkuIds = removeSkuList.stream().map(PmsSkuStock::getId).collect(Collectors.toList());
            PmsSkuStockExample removeExample = new PmsSkuStockExample();
            removeExample.createCriteria().andIdIn(removeSkuIds);
            skuStockMapper.deleteByExample(removeExample);
        }
        / / modify sku
        if(CollUtil.isNotEmpty(updateSkuList)){
            for(PmsSkuStock pmsSkuStock : updateSkuList) { skuStockMapper.updateByPrimaryKeySelective(pmsSkuStock); }}}}Copy the code

conclusion

If you want to store some properties in a database in a variable format, you can use JSON to store them. For the modification of the associated attribute, there are some logical operations to achieve the modification without changing the original ID.

Project source code address

Github.com/macrozheng/…

The public,

Mall project full set of learning tutorials serialized, attention to the public number the first time access.