toproplus

记一次商品数据库设计

2021-06-12

根据本人多年的电商开发经验，归纳总结，自行设计了本人认为比较合理的商品数据结构，希望对大家有所帮助，不合理之处欢迎斧正！

商品表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
CREATE TABLE `m_goods` (
  `goods_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '商品ID ',
  `goods_name` varchar(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '商品名称',
  `market_price` decimal(10,2) DEFAULT '0.00' COMMENT '原价（市场价）',
  `cost_price` decimal(10,2) DEFAULT '0.00' COMMENT '成本价',
  `sale_price` decimal(10,2) DEFAULT '0.00' COMMENT '售价',
  `goods_img` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '商品主图',
  `stock` int(11) DEFAULT '0' COMMENT '总库存',
  `spec_type` tinyint(1) DEFAULT '0' COMMENT '规格类型：0单规格, 1多规格',
  `spu` varchar(100) CHARACTER SET utf8mb4 DEFAULT '' COMMENT 'spu码',
  `is_sale` tinyint(1) DEFAULT '0' COMMENT '1上架0下级',
  `unit_name` varchar(20) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '商品单位',
  `add_time` int(11) NOT NULL COMMENT '添加时间',
  `is_del` tinyint(1) DEFAULT '0' COMMENT '是否删除',
  PRIMARY KEY (`goods_id`),
  KEY `is_sale` (`is_sale`) USING BTREE,
  KEY `is_del` (`is_del`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='商品表';

说明：

  • sale_price 主表的售价是该商品所有规格中的最低售价，cost_price, market_price 对应最低售价的规格的对应值
  • stock 总库存，即所有规格库存之和

商品副表

1
2
3
4
5
6
CREATE TABLE `m_goods_extra` (
  `goods_id` int(11) NOT NULL DEFAULT '0' COMMENT '商品ID',
  `slider_imgs` text CHARACTER SET utf8mb4 COMMENT '轮播图,一维数组json格式',
  `contents` mediumtext CHARACTER SET utf8mb4 COMMENT '详情内容，富文本',
  PRIMARY KEY (`goods_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='商品附加表';

说明：

规格表

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `m_goods_spec` (
  `spec_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '规格自增ID',
  `spec_sort` tinyint(2) DEFAULT '0' COMMENT '排序值',
  `spec_name` varchar(150) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '规格名称',
  `spec_values` text CHARACTER SET utf8mb4 COMMENT '所有规格值,一维数组json格式',
  `goods_id` int(11) NOT NULL DEFAULT '0' COMMENT '商品ID',
  `add_time` int(11) NOT NULL COMMENT '添加时间',
  PRIMARY KEY (`spec_id`),
  KEY `goods_id` (`goods_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='商品规格表';

说明：

  • spec_sort 排序值，按用户添加规格的顺序正序排序，建议使用规格数组的索引值

  • spec_name 规格名，如：颜色

  • spec_values 所有规格值, 固定json格式：[“红色”,”紫色”,”白色”]

商品具体规格表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE `m_goods_spec_item` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `goods_id` int(11) NOT NULL DEFAULT '0' COMMENT '商品ID',
  `spec_img` varchar(255) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '规格图片',
  `spec_data` text CHARACTER SET utf8mb4 NOT NULL COMMENT '规格组合数据',
  `spec_show` tinytext CHARACTER SET utf8mb4 NOT NULL COMMENT '展示规格',
  `spec_key` char(8) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '规格key,按规律生成',
  `market_price` decimal(10,2) DEFAULT '0.00' COMMENT '原价（市场价）',
  `cost_price` decimal(10,2) DEFAULT '0.00' COMMENT '成本价',
  `sale_price` decimal(10,2) DEFAULT '0.00' COMMENT '售价',
  `is_show` tinyint(1) DEFAULT '0' COMMENT '是否展示',
  `stock` int(11) DEFAULT '0' COMMENT '库存 ',
  `sku` varchar(100) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '规格码',
  `bar` varchar(100) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '条形码',
  `add_time` int(11) NOT NULL COMMENT '添加时间',
  PRIMARY KEY (`item_id`),
  KEY `goods_id` (`goods_id`) USING BTREE,
  KEY `spec_key` (`spec_key`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='商品具体规格表';

说明：

  • spec_data 规格组合数据，固定json格式：[{“k”:”颜色”,”v”:”红色”},{“k”:”尺码”,”v”:”L”},{“k”:”布料”,”v”:”纯棉”}]
  • spec_show展示规格信息，展示作用，格式自定义。例一：颜色(红色), 尺码(L), 布料(纯棉)。例二：红色-L-纯棉；
  • spec_key规格识别码，生成算法:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?php  
   
   /**
     * 获取spec_key
     */
    public static function getSpecKey(array $spec_data) 
    {
        $values = [];
        foreach ($spec_data as $item) {
            $values[] = trim($item['k']).'&'.trim($item['v']);
        }
        sort($values,SORT_STRING);
        $values_str = implode(',', $values);
        $spec_key = substr(md5($values_str), 16, 8);
        return $spec_key;
    }

spec_key 规格识别码，可用于建立规格索引，spec_key 是按商品具体规格生成的，即商品规格不变，则spec_key也不变。

后台保存更新的商品具体规格的信息到数据库时，可用spec_key查询判断该规格是否存在，不存在则插入，反之则更新。由于不同商品可能存在相同规格，故查询时spec_key需配合goods_id一起并列作为查询条件；

购物车表，用goods_id和spec_key来保存某个商品规格的购物车数量。

用户下单时，前端用goods_id和spec_key传给后端生成商品订单。

购物车和下单切忌用规格表的item_id保存数据，因为item_id不可控。

规格模板表

1
2
3
4
5
6
CREATE TABLE `m_goods_template` (
  `tpl_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '模板ID',
  `tpl_name` varchar(150) CHARACTER SET utf8mb4 DEFAULT '' COMMENT '模板名称',
  `add_time` int(11) NOT NULL COMMENT '添加时间',
  PRIMARY KEY (`tpl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='规格模板表';

规格模板的规格表

1
2
3
4
5
6
7
8
9
CREATE TABLE `m_goods_template_spec` (
  `spec_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '规格自增ID',
  `spec_name` varchar(150) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '规格名称',
  `spec_values` text CHARACTER SET utf8mb4 COMMENT '所有规格值,一维数组json格式',
  `tpl_id` int(11) NOT NULL DEFAULT '0' COMMENT '模板ID',
  `add_time` int(11) NOT NULL COMMENT '添加时间',
  PRIMARY KEY (`spec_id`),
  KEY `tpl_id` (`tpl_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='规格模板的规格表';

数据示例

  • 商品多规格以 vivo Y70t 5G手机为例：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    INSERT INTO `m_goods` (`goods_id`, `goods_name`, `market_price`, `cost_price`, `sale_price`, `goods_img`, `stock`, `spec_type`, `spu`, `is_sale`, `unit_name`, `add_time`, `is_del`) VALUES ('1', 'vivo Y70t 5G手机 6G+128GB 碧海蓝 4500mAh大电池 双引擎闪充 4800万后置三摄 双模5G全网通手机', '1399.00', '1000.00', '1299.00', 'https://img14.360buyimg.com/n0/jfs/t1/188926/21/7835/87064/60c36b81Eb99e7a88/33fb086b04cc6a0a.jpg', '500', '1', 'Y70T001', '1', '台', '1623436229', '0');

    INSERT INTO `m_goods_extra` (`goods_id`, `slider_imgs`, `contents`) VALUES ('1', '[\"https://img14.360buyimg.com/n0/jfs/t1/175867/19/9371/72377/609ce1d2Eb8e0a5df/fdf4b931f02a11ed.jpg\",\"https://img14.360buyimg.com/n0/jfs/t1/186673/39/2996/39890/609ce1d2E0625e6ee/4539697f73f9a07d.jpg\"]', '');

    INSERT INTO `m_goods_spec` (`spec_id`, `spec_sort`, `spec_name`, `spec_values`, `goods_id`, `add_time`) VALUES ('1', '0', '颜色', '[\"碧海蓝\",\"雪羽白\"]', '1', '1623436229');
    INSERT INTO `m_goods_spec` (`spec_id`, `spec_sort`, `spec_name`, `spec_values`, `goods_id`, `add_time`) VALUES ('2', '1', '版本', '[\"8G+128G\"]', '1', '1623436229');

    INSERT INTO `m_goods_spec_item` (`item_id`, `goods_id`, `spec_img`, `spec_data`, `spec_show`, `spec_key`, `market_price`, `cost_price`, `sale_price`, `is_show`, `stock`, `sku`, `bar`, `add_time`) VALUES ('1', '1', 'https://img14.360buyimg.com/n0/jfs/t1/188926/21/7835/87064/60c36b81Eb99e7a88/33fb086b04cc6a0a.jpg', '[{\"k\":\"颜色\",\"v\":\"碧海蓝\"},{\"k\":\"版本\",\"v\":\"8G+128G\"}]', '碧海蓝,8G+128G', '29b1a5bc', '1399.00', '1000.00', '1299.00', '1', '200', 'v123456', '15486554522', '1623436229');

    INSERT INTO `m_goods_spec_item` (`item_id`, `goods_id`, `spec_img`, `spec_data`, `spec_show`, `spec_key`, `market_price`, `cost_price`, `sale_price`, `is_show`, `stock`, `sku`, `bar`, `add_time`) VALUES ('2', '1', 'https://img14.360buyimg.com/n0/jfs/t1/177724/16/3821/38901/609ce4b0E888331d0/6e6271c95e6f87a1.jpg', '[{\"k\":\"颜色\",\"v\":\"雪羽白\"},{\"k\":\"版本\",\"v\":\"8G+128G\"}]', '雪羽白,8G+128G', '288fdf69', '1399.00', '1000.00', '1399.00', '1', '300', 'v234567', '65455511228', '1623436229');

  • 商品单规格以耳机为例：

    1
    2
    3
    4
    5
    6
    7
    8
    INSERT INTO `m_goods` (`goods_id`, `goods_name`, `market_price`, `cost_price`, `sale_price`, `goods_img`, `stock`, `spec_type`, `spu`, `is_sale`, `unit_name`, `add_time`, `is_del`) VALUES ('2', '兰士顿 CPN5耳机入耳式有线调音带麦 vivo华为荣耀oppo小米通用手机K歌吃鸡游戏耳麦 白色', '19.99', '8.00', '9.99', 'http://img13.360buyimg.com/n1/s450x450_jfs/t1/195360/40/5836/48325/60b5cc72E7a50597a/8a2687f351efce46.jpg', '999', '0', 'L26484', '1', '副', '1623436229', '0');

    INSERT INTO `m_goods_extra` (`goods_id`, `slider_imgs`, `contents`) VALUES ('2', '[\"https://img13.360buyimg.com/n1/s450x450_jfs/t1/195360/40/5836/48325/60b5cc72E7a50597a/8a2687f351efce46.jpg\",\"http://img13.360buyimg.com/n1/s450x450_jfs/t1/178923/40/6959/327220/60b5cb9fEf1de1d15/5ebc073bbe6bfb3f.jpg\"]', NULL);

    INSERT INTO `m_goods_spec` (`spec_id`, `spec_sort`, `spec_name`, `spec_values`, `goods_id`, `add_time`) VALUES ('3', '0', '规格', '默认', '0', '1623436229');

    INSERT INTO `m_goods_spec_item` (`item_id`, `goods_id`, `spec_img`, `spec_data`, `spec_show`, `spec_key`, `market_price`, `cost_price`, `sale_price`, `is_show`, `stock`, `sku`, `bar`, `add_time`) VALUES ('3', '2', 'https://img13.360buyimg.com/n1/s450x450_jfs/t1/195360/40/5836/48325/60b5cc72E7a50597a/8a2687f351efce46.jpg', '[{\"k\":\"规格\",\"v\":\"默认\"}]', '默认', 'ca1d86d0', '19.99', '8.00', '9.99', '1', '999', 'l345678', '63645123658', '1623436229');

  • 规格模板以衣服模板为例:

    1
    2
    3
    4
    5
    INSERT INTO `m_goods_template` (`tpl_id`, `tpl_name`, `add_time`) VALUES ('1', '衣服模板', '1623436229');

    INSERT INTO `m_goods_template_spec` (`spec_id`, `spec_name`, `spec_values`, `tpl_id`, `add_time`) VALUES ('1', '尺码', '[\"S码\",\"M码\",\"L码\"]', '1', '1623436229');
    INSERT INTO `m_goods_template_spec` (`spec_id`, `spec_name`, `spec_values`, `tpl_id`, `add_time`) VALUES ('2', '颜色', '[\"红色\",\"黄色\",\"蓝色\",\"灰色\"]', '1', '1623436229');
    INSERT INTO `m_goods_template_spec` (`spec_id`, `spec_name`, `spec_values`, `tpl_id`, `add_time`) VALUES ('3', '材质', '[\"涤纶\",\"纯棉\"]', '1', '1623436229');

  • spec_key算法示例:
    spec_key

Tags: Mysql
浏览         访客  
← 小工具自动统计网站访问量
赏
使用支付宝打赏
使用微信打赏

若你觉得我的文章对你有帮助，欢迎点击上方按钮对我打赏

扫描二维码，分享此文章