Commodity specification management of Leyou mall

Article directory

1. Product specifications

Leyou mall is a full category e-commerce website, so there are many kinds of goods, each of which has different attributes. In order to describe the product and segment differences more accurately, two concepts are abstracted: SPU and SKU

1.1 SPU and SKU

  • SPU: Standard Product Unit, a set of commodities with common properties

  • SKU: Stock Keeping Unit. Each product in the SPU product set is subdivided according to its specific characteristics

The above concept is somewhat abstract. For the convenience of understanding, there is a picture of "Xiaomi 10" commodity page of JD:

  • "Millet 10" on the page is a product set, namely SPU

  • Because different colors and versions are selected, different "millet 10", namely SKU, is subdivided.

    For example: titanium silver, 8GB+256GB is a SKU; ice blue, 8GB+128GB is a SKU

The role of both:

  • SPU is an abstract concept of commodity set, which is for the convenience of background management.
  • SKU is the specific commodity to be sold. The price and inventory of each SKU may be different. Users purchase SKU instead of SPU.

1.2 analysis of the relationship between product specifications

Let's take a look at the specifications page of Jingdong's "millet 10" products:

It is easy to analyze that there are two tables: Specification Group and specification parameter. And a specification group corresponds to multiple specification parameters, and a specification parameter corresponds to a specification group. There is a one to many relationship between specification groups and specification parameters.

And a classification corresponds to multiple specification groups, and a specification group corresponds to a classification. There is a one to many relationship between categories and specification groups.

Let's take a look at the filter conditions after JD searches for "mobile phones":

It can be analyzed that: we need to directly classify "mobile phones" and get "brand" specifications. And a classification corresponds to multiple specification parameters, and a specification parameter corresponds to a classification. There is a one to many relationship between classification and specification parameters.

The relationship among classification, specification group and specification parameters is shown in the following figure:

1.3 database design

1.3.1 commodity specification group table

TB spec group

CREATE TABLE `tb_spec_group` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `cid` bigint(20) NOT NULL COMMENT 'Commodity classification id´╝îMultiple specification groups under a category',
  `name` varchar(32) NOT NULL COMMENT 'Name of Specification Group',
  PRIMARY KEY (`id`),
  KEY `key_category` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COMMENT='Grouping table of specification parameters. There are multiple specification parameter groups under each commodity classification';

1.3.2 commodity specification parameter table

TB spec param

CREATE TABLE `tb_spec_param` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
  `cid` bigint(20) NOT NULL COMMENT 'Commodity classification id',
  `group_id` bigint(20) NOT NULL,
  `name` varchar(256) NOT NULL COMMENT 'Parameter name',
  `numeric` tinyint(1) NOT NULL COMMENT 'Is it a numeric type parameter, true or false',
  `unit` varchar(256) DEFAULT '' COMMENT 'The unit of the number type parameter. Non number type can be blank',
  `generic` tinyint(1) NOT NULL COMMENT 'Whether it is sku General properties, true or false',
  `searching` tinyint(1) NOT NULL COMMENT 'Is it used for search filtering, true or false',
  `segments` varchar(1024) DEFAULT '' COMMENT 'Numeric type parameter, if you need to search, add the value of segmentation interval, such as CPU Frequency interval: 0.5-1.0',
  PRIMARY KEY (`id`),
  KEY `key_group` (`group_id`),
  KEY `key_category` (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=33 DEFAULT CHARSET=utf8 COMMENT='Parameter name under specification parameter group';

There are several special fields here. Here is the specific explanation:

  • Numeric: used to determine whether the specification parameter is a numeric type parameter. If it is a numeric type parameter, you need to fill in the unit of the numeric type parameter.
  • generic: used to determine whether the specification parameter is a SKU general property. For example, the "color" and "version" of "Xiaomi 10" above are not SKU general attributes, but SKU specific attributes, so their values are false.
  • searching: used to determine whether the specification parameter is used for search filtering. As we can see above, some specification parameters will be used as search filtering criteria.
  • segments: segment interval value. If a field is both a numeric type parameter and can be used for search filtering, it can be divided into several interval values, such as battery capacity: 0-2000mAh, 2000mAh-3000mAh, 3000mAh-4000mAh.

2. Product specification group

2.1 front end of product specification group

We open the specification parameter page, and you can see the classification of goods on the left side

Click the final classification of a classification, and you can see the prompt "there is no specification group or classification has not been selected under this classification" on the right side, so you can know that the data on the right side is used to display the specification group data, but there is no data at present.

We found the code of the front end request specification group data:

From this we can know:

  • Request method: GET
  • Request path / spec/groups
  • Request parameter: classification id, here is a Rest style placeholder
  • Return parameter: collection of specification groups

2.2 query commodity Specification Group

2.2.1 entity class

Add two entity classes to your item interface project

SpecGroup

@Table(name = "tb_spec_group")
public class SpecGroup {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Long cid;
    private String name;
    @Transient
    private List<SpecParam> params;

    // getter, setter, toString methods omitted
}

Note: the params property here does not correspond to the database field, so @ Transient is added

SpecParam

@Table(name = "tb_spec_param")
public class SpecParam {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Long cid;
    private Long groupId;
    private String name;
    @Column(name = "`numeric`")
    private Boolean numeric;
    private String unit;
    private Boolean generic;
    private Boolean searching;
    private String segments;

    // getter, setter, toString methods omitted
}

Note: the numeric attribute here is a keyword in MySQL, so use @ Column to indicate that it is a field

2.2.2 Mapper

Add two mappers to the Leyou item service project

SpecGroupMapper

public interface SpecGroupMapper extends Mapper<SpecGroup> {
}

SpecParamMapper

public interface SpecParamMapper extends Mapper<SpecParam> {
}

2.2.3 Service

Add a Service to the Leyou item Service project

@Service
public class SpecificationService {
    @Autowired
    private SpecGroupMapper specGroupMapper;
    @Autowired
    private SpecParamMapper specParamMapper;

    /**
     * Query grouping according to classification id
     *
     * @param cid
     * @return
     */
    public List<SpecGroup> querySpecGroupsByCid(Long cid) {
        SpecGroup specGroup = new SpecGroup();
        specGroup.setCid(cid);
        List<SpecGroup> specGroups = specGroupMapper.select(specGroup);
        return specGroups;
    }
}

2.2.4 Controller

Add Controller to the Leyou item service project

@RestController
@RequestMapping("/spec")
public class SpecificationController {
    @Autowired
    private SpecificationService specificationService;

    /**
     * Query grouping according to classification id
     *
     * @param cid
     * @return
     */
    @GetMapping("/groups/{cid}")
    public ResponseEntity<List<SpecGroup>> querySpecGroupsByCid(@PathVariable("cid") Long cid) {
        List<SpecGroup> specGroups = specificationService.querySpecGroupsByCid(cid);
        if (CollectionUtils.isEmpty(specGroups)) {
            return ResponseEntity.notFound().build();
        }
        return ResponseEntity.ok(specGroups);
    }
}

2.2.5 test

Specification group data loaded successfully

3. Product specifications

3.1 front end of product specifications

Click a specification group "main body"

You can see that the table of specification group has been switched to the table of specification parameter, but there is no data yet

We find the code of front-end request specification parameter data:

From this we can know:

  • Request method: GET
  • Request path / spec/params
  • Request parameter: Specification Group id
  • Return parameters: collection of specification parameters

3.2 query commodity specification parameters

3.2.1 Controller

Add method in SpecificationController

/**
 * Query specification parameters based on criteria
 *
 * @param gid
 * @return
 */
@GetMapping("/params")
public ResponseEntity<List<SpecParam>> querySpecParams(@RequestParam("gid") Long gid) {
    List<SpecParam> params = specificationService.querySpecParams(gid);
    if (CollectionUtils.isEmpty(params)) {
        return ResponseEntity.notFound().build();
    }
    return ResponseEntity.ok(params);
}

3.2.2 Service

Add method in SpecificationService

/**
 * Query specification parameters based on criteria
 * @param gid
 * @return
 */
public List<SpecParam> querySpecParams(Long gid) {
    SpecParam specParam = new SpecParam();
    specParam.setGroupId(gid);
    List<SpecParam> params = specParamMapper.select(specParam);
    return params;
}

3.2.3 test

Specification parameter data loaded successfully

4. commodity

We have introduced the concepts of SPU and SKU, and we have learned that SPU is a product set, and SKU is the specific product to be sold. So the two necessary tables for commodities are SPU and SKU. Let's analyze the relationship between SPU, SKU and other tables.

4.1 analysis of the relationship between commodities

Still use the example above, "Xiaomi 10" is an SPU, which only corresponds to "Xiaomi" brand, but Xiaomi brand has multiple spus, such as: Xiaomi 9, Xiaomi 8, etc. There is a one to many relationship between brand and SPU.

"Xiaomi 10" is a mobile phone, which only corresponds to the mobile phone classification, while the mobile phone classification can correspond to multiple spus. There is a one to many relationship between classification and SPU.

As mentioned earlier, an SPU can have multiple SKUs, while a SKU can only have one SPU. There is a one to many relationship between SPU and SKU.

The relationship between commodities is shown in the figure below:

4.2 database design

4.2.1 SPU table

SPU table

CREATE TABLE `tb_spu` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'spu id',
  `title` varchar(128) NOT NULL DEFAULT '' COMMENT 'Title',
  `sub_title` varchar(256) DEFAULT '' COMMENT 'subtitle',
  `cid1` bigint(20) NOT NULL COMMENT '1 Class category id',
  `cid2` bigint(20) NOT NULL COMMENT '2 Class category id',
  `cid3` bigint(20) NOT NULL COMMENT '3 Class category id',
  `brand_id` bigint(20) NOT NULL COMMENT 'Brand of goods id',
  `saleable` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Whether to put it on the rack, 0 to put it off, 1 to put it on the rack',
  `valid` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Valid, 0 deleted, 1 valid',
  `create_time` datetime DEFAULT NULL COMMENT 'Adding time',
  `last_update_time` datetime DEFAULT NULL COMMENT 'Last modified',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=195 DEFAULT CHARSET=utf8 COMMENT='spu Table, which describes an abstract commodity, such as iphone8';

It seems that there are few fields in this table, such as product description, after-sales information, etc., but these data are relatively large. In order not to affect the query efficiency, we split the table vertically, and put the details of the SPU in another table, TB \

CREATE TABLE `tb_spu_detail` (
  `spu_id` bigint(20) NOT NULL,
  `description` text COMMENT 'Product description',
  `generic_spec` varchar(2048) NOT NULL DEFAULT '' COMMENT 'General specification parameter data',
  `special_spec` varchar(1024) NOT NULL COMMENT 'Specific specification parameters and optional value information, json format',
  `packing_list` varchar(1024) DEFAULT '' COMMENT 'Packing list',
  `after_service` varchar(1024) DEFAULT '' COMMENT 'After-sale service',
  PRIMARY KEY (`spu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

There are several special fields here. Here is the specific explanation:

generic_spec

It is used to save the values of general specification parameter information. In order to facilitate query, JSON format is used here.

All of them are key value pairs:

  • key: the id of spec_param of the corresponding specification parameter
  • Value: the value of the corresponding specification parameter

special_spec

It is used to save specific specification parameters and optional values, that is, special attributes of SKU.

All of them are key value pairs:

  • key: the id of spec_param of the corresponding specification parameter
  • value: the array corresponding to the specification parameter, because there may be more than one SKU specific attribute

4.2.2 SKU table

SKU table

CREATE TABLE `tb_sku` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'sku id',
  `spu_id` bigint(20) NOT NULL COMMENT 'spu id',
  `title` varchar(256) NOT NULL COMMENT 'Product title',
  `images` varchar(1024) DEFAULT '' COMMENT 'Pictures of products, multiple pictures with','Division',
  `price` bigint(15) NOT NULL DEFAULT '0' COMMENT 'Sales price in points',
  `indexes` varchar(32) DEFAULT '' COMMENT 'The specific specification attributes are spu Corresponding subscript combination in attribute template',
  `own_spec` varchar(1024) DEFAULT '' COMMENT 'sku Key value pair of specific specification parameters of, json Format, use when deserializing linkedHashMap´╝îGuarantee order',
  `enable` tinyint(1) NOT NULL DEFAULT '1' COMMENT 'Is it valid, 0 is invalid, 1 is valid',
  `create_time` datetime NOT NULL COMMENT 'Adding time',
  `last_update_time` datetime NOT NULL COMMENT 'Last modified',
  PRIMARY KEY (`id`),
  KEY `key_spu_id` (`spu_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=27359021729 DEFAULT CHARSET=utf8 COMMENT='sku surface,This table represents specific commodity entities,Like the black 64 g Of iphone 8';

There are several special fields here. Here is the specific explanation:

indexes

The special spec field of the TB SPU detail table is used to save the unique attributes of SKU, and the indexes field is the subscript combination of these unique attributes. This design is particularly useful on the product details page. When the user clicks and selects a specific property, you can quickly locate the SKU according to the corner mark.

For example, the special spec field is as follows:

indexes field:

  • 0? 0? 0: white, 3GB, 16GB
  • 1 × 0 × 0: gold, 3GB, 16GB
  • 2 × 0 × 0: rose gold, 3GB, 16GB

own_spec

The key value pair used to save SKU specific properties uses the JSON format, such as:

SKU should also have an inventory field, but other fields in the SKU table read more frequently, while the inventory fields write more frequently. Therefore, the table is vertically split so that the reading and writing do not interfere with each other.

TB stock

CREATE TABLE `tb_stock` (
  `sku_id` bigint(20) NOT NULL COMMENT 'Goods corresponding to inventory sku id',
  `seckill_stock` int(9) DEFAULT '0' COMMENT 'Second kill inventory',
  `seckill_total` int(9) DEFAULT '0' COMMENT 'Total number of seconds',
  `stock` int(9) NOT NULL COMMENT 'Inventory quantity',
  PRIMARY KEY (`sku_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Inventory table, representing inventory, seckill inventory and other information';

4.3 commodity front end

Click the product list, you can see that this is a form that can realize pagination and query, which is very similar to the previous brand query.

We found the code of the front-end request product data:


From this we can know:

  • Request method: GET
  • Request path: spu/page
  • Request parameters:
    • key: search criteria, String
    • saleable: upper and lower shelves, boolean (all null, upper shelves are true, lower shelves are false)
    • Page: current page, int
    • rows: page size, int
  • Return parameter: collection of specification groups
    • total: total
    • items: current page data

4.4 commodity query

4.4.1 entity

Add entity class in Leyou item interface:

SPU

@Table(name = "tb_spu")
public class Spu {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private Long brandId;
    private Long cid1;// Class 1
    private Long cid2;// Class 2
    private Long cid3;// Class 3
    private String title;// Title
    private String subTitle;// subtitle
    private Boolean saleable;// Is it on the shelf?
    private Boolean valid;// Valid or not, for logical deletion
    private Date createTime;// Creation time
    private Date lastUpdateTime;// Last modified
	
    // getter, setter, toString methods omitted
}

SPU details

@Table(name="tb_spu_detail")
public class SpuDetail {
    @Id
    private Long spuId;// id of the corresponding SPU
    private String description;// Commodity Description
    private String specialSpec;// Name and optional value template of special product specification
    private String genericSpec;// Global specification properties of goods
    private String packingList;// Packing list
    private String afterService;// After-sale service

    // getter, setter, toString methods omitted
}

At this point, we found a problem that the product classification and brand in the product page should be string

The product classification and brand of the Spu table are only IDS, so there are two attributes in the entity class to encapsulate the name of the product classification and the name of the brand classification. We can't directly modify the Spu entity class, but we can expand an entity class SpuBo, which is Business Object

public class SpuBo extends Spu{
    private String cname;
    private String bname;
    
    // getter, setter, toString methods omitted
}

4.4.2 Mapper

Add two mappers to the Leyou item service project

Spu

public interface SpuMapper extends Mapper<Spu> {
}

Spu details

public interface SpuDetail extends Mapper<SpuDetail> {
}

4.4.3 Controller

Add Controller to the Leyou item service project

@RestController
@RequestMapping("/spu")
public class SpuController {
    @Autowired
    private SpuService spuService;

    /**
     * Query commodity information by page according to query criteria
     * @param key search criteria
     * @param saleable Upper and lower frames
     * @param page Current page
     * @param rows Size per page
     * @return
     */
    @RequestMapping("/page")
    public ResponseEntity<PageResult<SpuBo>> querySpuByPage(
            @RequestParam(name = "key", required = false) String key,
            @RequestParam(name = "saleable", required = false) Boolean saleable,
            @RequestParam(name = "page", defaultValue = "1") Integer page,
            @RequestParam(name = "rows", defaultValue = "5") Integer rows
    ) {
        PageResult<SpuBo> pageResult = spuService.querySpuByPage(key, saleable, page, rows);
        if (CollectionUtils.isEmpty(pageResult.getItems())) {
            ResponseEntity.notFound().build();
        }
        return ResponseEntity.ok(pageResult);
    }
}

4.4.4 Service

Add a Service to the Leyou item Service project

@Service
public class SpuService {
    @Autowired
    private SpuMapper spuMapper;
    @Autowired
    private BrandMapper brandMapper;
    @Autowired
    private CategoryService categoryService;

    /**
     * Query commodity information by page according to query criteria
     *
     * @param key      search criteria
     * @param saleable Upper and lower frames
     * @param page     Current page
     * @param rows     Size per page
     * @return
     */
    public PageResult<SpuBo> querySpuByPage(String key, Boolean saleable, Integer page, Integer rows) {
        // Initialize example object
        Example example = new Example(Spu.class);
        Example.Criteria criteria = example.createCriteria();

        // Add search criteria
        if (StringUtils.isNotBlank(key)) {
            criteria.andLike("title", "%" + key + "%");
        }

        // Add upper and lower shelves
        if (saleable != null) {
            criteria.andEqualTo("saleable", saleable);
        }

        // Add Pagination
        PageHelper.startPage(page, rows);

        // Execute query to get Spu set
        List<Spu> spus = spuMapper.selectByExample(example);

        // Package Spu set as pageInfo
        PageInfo<Spu> spuPageInfo = new PageInfo<>(spus);

        // Convert Spu set to SpuBo set
        ArrayList<SpuBo> spuBos = new ArrayList<>();
        for (Spu spu : spus) {
            SpuBo spuBo = new SpuBo();
            // Copy common properties to SpuBo object
            BeanUtils.copyProperties(spu, spuBo);
            // Query classification name and add to SpuBo object
            List<String> names = categoryService.queryNamesByIds(Arrays.asList(spu.getCid1(), spu.getCid2(), spu.getCid3()));
            spuBo.setCname(StringUtils.join(names,"/"));
            // Query brand name and add to SpuBo object
            Brand brand = brandMapper.selectByPrimaryKey(spu.getBrandId());
            spuBo.setBname(brand.getName());
            // Add SpuBo to SpuBo collection
            spuBos.add(spuBo);
        }

        // Return to pageresult < spubo >
        return new PageResult<SpuBo>(spuPageInfo.getTotal(), spuBos);
    }
}

Add method in CategoryService

/**
 * Query classification name
 * @param ids
 * @return
 */
public List<String> queryNamesByIds(List<Long> ids) {
    ArrayList<String> names = new ArrayList<>();
    for (Long id : ids) {
        Category category = categoryMapper.selectByPrimaryKey(id);
        names.add(category.getName());
    }
    return names;
}

4.4.5 test

77 original articles published, praised 119, 10000 visitors+
Private letter follow

Tags: Database Mobile JSON Attribute

Posted on Sat, 07 Mar 2020 21:36:15 -0800 by Ash3r