自定义销售属性的展示和数据存储解决方案

之前一直在国内某服装电商公司.

我们的商品结构设计是:

款 -> 颜色 -> 尺码[SKU]. 比较简单.

现在,遇到一个新的需求. 我们需要支持用户自定义销售属性.如下截图:

SKU列表会随着属性的增加和删除会发生变化, 如下截图:

增加了一个型号短袖后,变成如下:

我的数据表设计如下

由 Product表, SellProperty表和SKU表三张表来支持该功能.

表结构如下:

CREATE TABLE `purchaser_product` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_code` varchar(45) DEFAULT NULL COMMENT '商品编码',
  `product_name` varchar(45) NOT NULL COMMENT '商品名称',
  `price` decimal(12,2) DEFAULT NULL COMMENT '价格',
  `unit` int(11) NOT NULL COMMENT '单位-个,件等',
  `category_id` bigint(20) NOT NULL,
  `top_category_id` bigint(20) NOT NULL,
  `description` text COMMENT '商品描述',
  `create_time` datetime NOT NULL,
  `creator_name` varchar(45) NOT NULL,
  `update_time` datetime DEFAULT NULL,
  `updator_name` varchar(45) DEFAULT NULL,
  `delete_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除',
  `on_shelf_status` tinyint(1) NOT NULL DEFAULT '1' COMMENT '上下架状态',
  `brand` varchar(45) DEFAULT NULL COMMENT '商品品牌',
  `model` varchar(45) DEFAULT NULL COMMENT '商品型号',
  `vendor_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

CREATE TABLE `purchaser_product_sell_property` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) DEFAULT NULL,
  `property_name` varchar(45) DEFAULT NULL,
  `order_num` int(11) DEFAULT NULL,
  `creator_name` varchar(45) DEFAULT NULL,
  `creator_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

CREATE TABLE `purchaser_sku` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `sell_property_name_struct` varchar(45) DEFAULT NULL COMMENT '销售属性名的结构,从第一级到最后,以逗号分隔',
  `price` decimal(12,2) NOT NULL,
  `product_code` varchar(45) NOT NULL,
  `sell_property_value_struct` varchar(45) DEFAULT NULL COMMENT '销售属性值的结构,从第一级到最后,以逗号分隔',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=utf8;

首先最复杂的就是增加属性,会变化表格.

我抽象了一个JS对象,有两个主要方法:

addProperty(property) // 添加属性

addPropertyValue(property,propertyValue) //添加属性值

var propertyMap = {
                propertyList:[],
                total:0,
                propertyObj:{},
                validProperties:[],
                deleteProperty : function(propertyName) {
                    delete this.propertyObj[propertyName];
                    removeByValue(this.propertyList,propertyName);
                    this.show();
                },
                hasValue : function() {
                    var hasValue = false;
                    $.each(this.propertyObj,function(i,n){
                        hasValue = n.length > 0;
                    });
                    return hasValue;
                },
                propertyHasValue : function(property) {
                    return this.propertyObj[property] == null ? true:(this.propertyObj[property].length>0);
                },
                addProperty : function(propertyName) {
                    this.propertyList.push(propertyName);
                    this.propertyObj[propertyName] = [];
                    var tpl = document.getElementById("property_row_tpl").innerHTML;
                    var html = juicer(tpl,{property:propertyName});
                    $("#property_area").append(html);

                },
                addValue : function(propertyName,value) {
                    if (contains(this.propertyObj[propertyName],value) ) {
                        layer.msg("属性值已经存在",{icon: 5,time: 1000});
                        return;
                    }
                    this.propertyObj[propertyName].push(value);
                    var tpl = document.getElementById("property_value_tpl").innerHTML;
                    var html = juicer(tpl,{propertyValue:value,propertyName:propertyName});
                    $("#" + propertyName + "_value_display_area").append(html);
                    this.show();
                },
                deleteValue : function(propertyName,value) {
                    removeByValue(this.propertyObj[propertyName],value);
                    this.show();
                },
                show : function() {
                    //没有属性,直接返回
                    if (this.propertyList == null || this.propertyList.length <= 0) {
                        $("#tablearea").html("");
                        return;
                    }

                    var propertySize = this.propertyList.length;//总共有多少个属性

                    var validProperty = null;
                    var last = null;
                    var validPropertyList = [];
                    var totalTR = 1;
                    for (var i = propertySize - 1; i >= 0; i--) {
                        if (this.propertyObj[this.propertyList[i]].length > 0) {
                            validProperty = {propertyValues:[],index:0,pre:null,next:null,nextLoop:1};
                            //如果属性对应的属性值列表有值,说明此属性有效
                            validProperty.index = i + 1;
                            validProperty.propertyValues = this.propertyObj[this.propertyList[i]];
                            if (last != null) {
                                last.pre = validProperty;
                            }
                            validProperty.next = last;
                            validProperty.name = this.propertyList[i];
                            last = validProperty;
                            validPropertyList.unshift(validProperty);
                            totalTR = totalTR * validProperty.propertyValues.length;
                        }
                    }
                    // 没有有效属性,也就是所有属性没有属性值,直接返回
                    if (validPropertyList == null || validPropertyList.length <= 0) {
                        $("#tablearea").html("");
//                        var defaultTable = document.getElementById("default_sku_tpl").innerHTML;
//                        $("#tablearea").html(defaultTable);
                        return;
                    }
                    this.validProperties = validPropertyList;
                    for (var i = 0; i < validPropertyList.length; i++) { //计算每一个属性的所有下级属性长度乘积的结果,用来表示,下级一共多少行.
                        var nextLoop = 1;
                        var nextNode = validPropertyList[i].next;
                        while (nextNode != null) {
                            nextLoop = nextLoop * nextNode.propertyValues.length;
                            nextNode = nextNode.next;
                        }
                        validPropertyList[i].nextLoop = nextLoop;
                    }

                    var totalTD = validPropertyList.length; // 每行有几个TD
                    var tableContent = '';
                    for (var i = 1; i <= totalTR; i++) { //需要有多少行
                        var tr = '<tr>';
                            for (var j = 1; j <= totalTD; j++) {
                                var currentProperty = validPropertyList[j - 1];
                                if (j == totalTD) {
                                    var index = parseInt((i - 1)%currentProperty.propertyValues.length);
                                    var propertyValueStruct = loadPropertyValues(currentProperty, i);
                                    var propertyStruct = loadProperty(currentProperty);
                                    console.log(propertyStruct);
                                    var id = i + "-" + j;
                                    tr +=  "<td>"+currentProperty.propertyValues[index]+"</td>";
                                    tr +=  "<td style='display:none'><input type='text' class='form-control product_code_class' placeholder='填写商品编码' id='productcode_"+id+"'  propertystruct='"+propertyValueStruct+"' property='"+propertyStruct+"'></td>";
                                    tr +=  "<td><input type='text' value='' class='touchspin' id='price_"+id+"'></td>";
                                    break;
                                }
                                var isFirst = false;
                                if (currentProperty.nextLoop == 1) {
                                    isFirst = ( i % currentProperty.nextLoop == 0);
                                } else {
                                    isFirst = (i % currentProperty.nextLoop == 1);
                                }
                                if (isFirst) {
                                    var index = parseInt(((i - 1)/currentProperty.nextLoop)%currentProperty.propertyValues.length);
                                    tr +=  "<td rowspan='"+ currentProperty.nextLoop +"'>"+currentProperty.propertyValues[index]+"</td>";
                                }
                            }
                        tr += "</tr>";
                        tableContent += tr;
                    }
                    var tpl = document.getElementById("table_tpl").innerHTML;
                    var html = juicer(tpl,{list:validPropertyList});
                    html = html.replace("######",tableContent);
                    $("#tablearea").html(html);
                    $(".touchspin").TouchSpin({
                        min: 0,
                        max: 100000,
                        step: 0.1,
                        decimals: 2,
                        prefix: '$'
                    });
                }

            };